将 Excel 中的数据插入 Access

Question

我编写了一些代码来将 Excel 表中的数据插入到 Access 数据库中 - 我的代码如下：

    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 找不到对象“数据表”的路径”。数据表只是我的工作簿中数据所在的工作表的名称，非常感谢任何帮助。

Answer 1

xlFilepath = Application.ThisWorkbook.FullName
Sql = "INSERT INTO tblSales " & _

"SELECT * FROM [Excel 12.0 Macro;HDR=YES;DATABASE=" & xlFilepath & "].[datasheet$A1:AK10011] "

cnn.Execute Sql

数据表表前 8 行中出现警告。假设有一个标题（HDR=YES）
接下来的 6 行应包含虚拟数据来定义与访问表列定义等效的列。

Answer 2

如果在工作表名称后添加 $ 符号会发生什么
像这样[数据表$]
？

Answer 3

我认为您不能对任何打开的工作簿执行查询。它必须针对文件运行，这意味着您必须提供工作表的完整路径，包括文件名。如果您的工作簿是“脏”的，您需要先保存它。
我宁愿

• 循环工作表行并一一添加记录
• ，或者使用您刚刚从 Access 编写的代码（如果合适）

Answer 4

据我所知，所缺少的只是数据源的路径和数据表上的字符串：

Data Source=sample.xls;

应该读取，例如：

Data Source=c:\docs\sample.xls;

并且：

SELECT * FROM [datasheet$]

Answer 5

SELECT 语句在数据库本身上运行，但您希望从 EXCEL 发送值。因此，您必须

cn.Execute "INSERT .... VALUES (" & excelcell_or_variable & ");"

在循环中最终使用来处理所有行/列等。

希望这有助于祝您

好运

编辑...
不要忘记 CHAR 和标点符号周围的引号；我使用

' ....
' .... "...VALUES (" & T(Q(MyStringCell)) & T(MyNumCell) & Q(MyLastTextCell) & ");"
' ....

' surrounds a string by single quotes
Private Function Q(Arg as String) As String
    Q = "'" & Arg & "'"
Return

' appens a comma to a string
Private Function T(Arg as String) As String
    T = Arg & ","
Return

编辑2
我假设在 EXCEL 中要插入到数据库中的值都在 1 行中...

假设您有一个包含多于 1 行的 source_range，则必须为该范围中的每一行触发 INSERT 语句。您可以使用 .Rows 属性从范围中返回单行。您可以使用 .Cells(1, 1)、.Cells(1,2) .... 等将多列发送到同一行中的 INSERT 语句举例

：

Sub Test()
Dim MyRange As Range, MyRow As Range

    Set MyRange = Range([B4], [C8])   ' source range

    For Each MyRow In MyRange.Rows    ' get one row at the time from source range 
        Debug.Print MyRow.Cells(1, 1), MyRow.Cells(1, 2)
        ' replace the above by your INSERT statement
    Next MyRow

End Sub