当我按下按钮时,我尝试使用 Excel 文件上的 VBA 代码将数据从 Excel 2010 导出到 Access 2010。我只想将“水质”表中的数据导出到数据库中的“水质”表(Excel 文件和访问文件中还有其他表和表格)。
我的实际代码是:
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
.
我也检查了名称和路径名,但找不到问题出在哪里。
任何解决该问题的帮助将不胜感激。
以下是一次插入所有数据的几个示例:
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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)