将数据从Excel导出到Access - 错误：参数不可选 [英] Export data from Excel to Access - Error: Argument not Optional

问题描述

当我按下按钮时，我试图将数据从Excel 2010导出到Access 2010，并在Excel文件上使用VBA代码。我只想将数据从水质表导出到我的数据库的水质表（在Excel文件和访问文件是其他表和表）。

I'm trying to export data from Excel 2010 to Access 2010 with a VBA code on my excel file when I press a button. I just want to export the data from "Water Quality" sheet to the "Water Quality" table on my database (In the excel file and access file are other sheets and tables).

我的实际代码是：

Sub Button14_Click()

' Exports data from the active worksheet to a table in an Access database

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
Dim LastRow As Long

' Set cn = New ADODB.Connection

'cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
    "Data Source=C:\Documents and Settings\Administrador\Mis documentos\MonEAU\modelEAU Database V.2.accdb; " & _
    "Persist Security Info=False;"

strCon = "Provider=Microsoft.ACE.OLEDB.12.0; " & _
    "Data Source=C:\Documents and Settings\Administrador\Mis documentos\MonEAU\modelEAU Database V.2.accdb"

' Late binding, so no reference is needed
Set cn = CreateObject("ADODB.Connection")

cn.Open strCon

' Find LastRow in Col A into the Sheet1
LastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

' Insert unto a table called Water_Quality
scn = "[Excel 8.0;HDR=YES;DATABASE=" & ActiveWorkbook.FullName & "]"
strSQL = "INSERT INTO Water_Quality " _
       & "SELECT * FROM " & scn & ".[Sheet1$A5:L" & LastRow & "]"

' Execute the statement
cn.Execute strSQL

rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

End Sub

任何问题的代码，但当我运行它，运行错误出现：Microsoft Office Access数据库引擎找不到对象'Sheet1 $ A5：L10'。确保对象存在，并拼写其名称和路径名正确。它似乎有一个问题， cn.Execute strSQL 。

I can debug without any problem the code but when I run it, a run error appears: "The Microsoft Office Access database engine could not find the object 'Sheet1$A5:L10'. Make sure the object exists and that you spell its name and the path name correctly." It seems that there's a problem with the line cn.Execute strSQL.

我已经检查了名称和路径名，以及我找不到问题所在。

I've checked the names and the path name as well, and I can't find where the problem is.

任何帮助解决它将非常感激。

Any help to solve it would be greatly appreciated.

推荐答案

以下是一次性插入所有数据的示例：

Here are a few examples for inserting all the data at once:

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=z:\docs\test.accdb"

''Late binding, so no reference is needed
Set cn = CreateObject("ADODB.Connection")

cn.Open strCon

''Create a table called ATable
scn = "[Excel 8.0;HDR=YES;DATABASE=" & ActiveWorkbook.FullName & "]"
strSQL = "SELECT * INTO ATable " _
       & "FROM " & scn & ".[Sheet7$A1:C4]"

''Execute the statement
cn.Execute strSQL

''Insert into a table called ATable
scn = "[Excel 8.0;HDR=YES;DATABASE=" & ActiveWorkbook.FullName & "]"
strSQL = "INSERT INTO ATable " _
       & "SELECT * FROM " & scn & ".[Sheet7$A1:C4]"

''Execute the statement
cn.Execute strSQL

''Insert into a table with no column header in Excel, 
''the fields are [afield],[atext],[another]
scn = "[Excel 8.0;HDR=NO;DATABASE=" & ActiveWorkbook.FullName & "]"
strSQL = "INSERT INTO ATable ([afield],[atext],[another]) " _
       & "SELECT F1 As afield, F2 As AText, F3 As another FROM " _
       & scn & ".[Sheet7$A1:C4]"

''Execute the statement
cn.Execute strSQL

这篇关于将数据从Excel导出到Access - 错误：参数不可选的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！