将数据从Excel插入Access [英] Insert Data from Excel into Access

问题描述

我已经编写了一些代码以将excel表中的数据插入到Access数据库中-我的代码如下:

I've made some code to insert data from an excel table in to an access database - my code is as follow:

    Sub AddData()

Dim Cn As ADODB.Connection

Set Cn = New ADODB.Connection

'lets connect to the workbook first, I tested this, it works for me
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sample.xls;Extended Properties=Excel 8.0;" _
& "Persist Security Info=False"

' Append data from Sheet1 of workbook to Table1 of mydb.mdb:
Cn.Execute "INSERT INTO tblSales IN 'C:\Users\User\Documents\access.mdb' SELECT * FROM [datasheet]"

Cn.Close
Set Cn = Nothing

End Sub

我的问题是执行此操作时出现错误"Microsoft Jet Engine找不到对象数据表"的路径.数据表只是数据在工作簿中位于工作表的名称.非常感谢您的帮助

My problem is when executing this I get the error "Microsoft Jet Engine could not find the path to object "datasheet" . Datasheet is just the name of the sheet where the data is located in my workbook. Any help is much appreciated.

推荐答案

如果在工作表名称后加$符号会发生什么情况 像这样[datasheet $] ?

What happens if you put a $ sign after the sheet name like this [datasheet$] ?

这篇关于将数据从Excel插入Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！