我正在开展一个项目,我已经遇到了障碍 . 我在excel中有一个表,我想通过在访问excel中的工作表时链接表来运行查询,然后将报表存储到Excel工作表中 . 我靠近我可以运行访问查询并从excel中存在的表中存储excel,但如果其中一个表在我的excel表中,我无法弄清楚如何去做 . 有没有人知道将访问表链接到excel工作表的vba代码?
我正在引用以下链接,这让我非常接近,但并非一直如此 .
http://www.myengineeringworld.net/2013/10/running-access-queries-from-excel-vba.html http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=173:import-export-data-from-access-to-excel-using-ado&catid=79&Itemid=475
Private Sub CommandButton1_Click()
'--------------
'DIM STATEMENTS
Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String
Dim i As Long, n As Long, lastRow As Long, lFieldCount As Long
'instantiate an ADO object using Dim with the New keyword:
Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection
'--------------
'THE CONNECTION OBJECT
strDBName = "REPORT.MDB"
strMyPath = "C:\Program Files\SETROUTE 9.2.0\DATA"
strDB = strMyPath & "\" & strDBName
'Connect to a data source:
'For pre - MS Access 2007, .mdb files (viz. MS Access 97 up to MS Access 2003), use the Jet provider: "Microsoft.Jet.OLEDB.4.0". For Access 2007 (.accdb database) use the ACE Provider: "Microsoft.ACE.OLEDB.12.0". The ACE Provider can be used for both the Access .mdb & .accdb files.
connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDB
'--------------
'OPEN RECORDSET, ACCESS RECORDS AND FIELDS
Dim ws As Worksheet
'set the worksheet:
Set ws = ActiveWorkbook.Sheets("Cables721")
'Set the ADO Recordset object:
Set adoRecSet = New ADODB.Recordset
'Opening the table named SalesManager:
strTable = "Cables with Incomplete Vias"
adoRecSet.Open "", Source:=strTable, ActiveConnection:=connDB, CursorType:=adOpenStatic, LockType:=adLockOptimistic
'--------------
'COPY RECORDS FROM THE EXCEL WORKSHEET:
'Note: Columns and their order should be the same in both Excel worksheet and in Access database table
lFieldCount = adoRecSet.Fields.Count
'determine last data row in the worksheet:
lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
'start copying from second row of worksheet, first row contains field names:
For i = 2 To lastRow
adoRecSet.AddNew
For n = 0 To lFieldCount - 1
adoRecSet.Fields(n).Value = ws.Cells(i, n + 1)
Next n
adoRecSet.Update
Next i
'--------------
'close the objects
adoRecSet.Close
connDB.Close
'destroy the variables
Set adoRecSet = Nothing
Set connDB = Nothing
End Sub
1 回答
尝试使用连接字符串:
如果目标数据库是ACCDB格式,则提供者部分必须是Provider = Microsoft.ACE.OLEDB.12.0 . Provider = Microsoft.Jet.OLEDB.4.0仅适用于较旧的MDB格式 .
另请参阅:How to query a MS-Access Table from MS-Excel (2010) using VBA
您也可以手动将表格作为连接从Excel中添加到“数据”选项卡下