excel - VBA将Excel电子表格逐行导入Access

Question

我正在调试一些代码，需要找出一个DoCmd.TransferSpreadsheet acImport, , ".....失败了，所以我决定逐行“手动”导入它，看看它在哪里掉下来。

我想这样的东西是我正在寻找的:

mySpreadSheet = ConnectTo(Spreadsheet.xlsx)
while(!mySpreadSheet.EOF)
   get(mySpreadSheet.nextLine)
   SQL("UPDATE MyTable with mySpreadSheet.nextLine")

我试过谷歌搜索无济于事。任何帮助深表感谢!

附加信息:

电子表格和 Access 表的列名相同。

每种数据类型都是 nvarchar(MAX)(或 Access 称之为“备忘录”)

该表是与 SQL Server 2008

的链接表

Answer 1

如果您有明确定义的数据表布局，ADO 会很好地工作(HansUp 回答)。如果您需要在加载对象之前添加控制，您可以连接到 Excel 工作簿，然后提取您喜欢的任何数据。这实际上取决于您需要的控制水平。

Public Sub LoadExcelToAccess(xlPath As String)
'uses late binding to open excel workbook and open it line by line

'make reference to Microsoft Excel xx.x Object Model to use native functions, requires early binding however

    Dim xlApp As Object 'Excel.Application
    Dim xlWrk As Object 'Excel.Workbook
    Dim xlSheet As Object 'Excel.Worksheet
    Dim i As Long
    Dim sql As String

    Set xlApp = VBA.CreateObject("Excel.Application")

    'toggle visibility for debugging
    xlApp.Visible = False

    Set xlWrk = xlApp.Workbooks.Open(xlPath)
    Set xlSheet = xlWrk.Sheets("Sheet1") 'modify to your perticular sheet

    'depends on what your trying to do with the sheet
    For i = 1 To 10

        'quick and dirty:  best to load items into custom class collection, then do processing there
        sql = "Insert Into [Temp] (Col1) VALUES (" & xlSheet.Cells(i, 1).Value & ")"
        DoCmd.RunSQL sql
    Next i

    'make sure to dispose of objects
    xlWrk.Close
    xlApp.Quit

    Set xlSheet = Nothing
    Set xlWrk = Nothing
    Set xlApp = Nothing
End Sub