我想弄清楚如何从 Access 表中获取我要导入 Excel 的数据以导入到特定的工作表(工作表只是称为工作表 2 或访问数据)。我有以下代码来获取数据并在导入后按照我想要的方式对其进行格式化,但我无法将其导入到特定工作表中。我可以获得帮助吗?这是我所拥有的:
使用分辨率更新代码:
Sub getAccessData()
Dim DBFullName As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Dim lngLastColumn As Long
Dim lngLastRow As Long
Dim OXLSheet As Worksheet
Set OXLSheet = Worksheets("WorksheetName")
Worksheets("WorksheetName").Cells.Clear
'Datebase path info
DBFullName = "C:\Users\myname\Desktop\Database Backups\database.accdb"
'Open the connection for the database
Set Connection = New ADODB.Connection
Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
Connect = Connect & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Connect
'Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
'Data Filter
Source = "SELECT * FROM tblRetirements WHERE [AllowEnteredInPayroll] Is Null AND ApplicationCancelled = 'No'"
.Open Source:=Source, ActiveConnection:=Connection
'Write field Names
For Col = 0 To Recordset.Fields.Count - 1
Worksheets("WorksheetName").Range("A5").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next
'Write Recordset
Worksheets("WorksheetName").Range("A5").Offset(1, 0).CopyFromRecordset Recordset
End With
ActiveSheet.Columns.AutoFit
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
With OXLSheet
lngLastColumn = .Cells(5, .Columns.Count).End(xlToLeft).Column
lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.ListObjects.Add(xlSrcRange, .Range(.Cells(5, 1), .Cells(lngLastRow, lngLastColumn)), , xlYes).Name = "Table1"
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium16"
End With
End Sub
谢谢你。
有一个错字SELECT*FROM
,应该是SELECT * FROM
。
如果您想导入到特定的工作表 name output
,请尝试替换:
Range("A5").Offset(0, Col).Value = Recordset.Fields(Col).Name
和 Worksheets("output").Range("A5").Offset(0, Col).Value = Recordset.Fields(Col).Name
Range("A5").Offset(1, 0).CopyFromRecordset Recordset
和 Worksheets("output").Range("A5").Offset(1, 0).CopyFromRecordset Recordset
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句