使用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称之为“Memo”）。
• 该表是连接到SQL Server 2008的链接表。

- MSpeed

---

你是否考虑过将xlsx文件导出为csv，然后逐行读取它作为一个可能的解决方案？ - MPękalski

将您的电子表格限制为仅一个记录，然后查看它是否有效。 - JeffO

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

- Fink

---

非常感谢您，Fink。您的程序对我非常有效。真是一个很棒的程序... - Ashok kumar

Answer 2

您可以创建一个ADO连接到您的电子表格（请参阅Excel 2007连接字符串），然后使用该连接打开一个ADO记录集（例如，请参阅StackOverflow：VBA中的ADODB记录集说Excel字段为空，但实际上不是）。

然后遍历记录集行，并使用该行的值创建SQL INSERT语句。

strInsert = "INSERT INTO MyTable (first_field, second_field) VALUES ('" & -
    rs2.Field(0).Value & "', '" & rs2.Field(1).Value & "');"
Debug.Print strInsert
CurrentDb.Execute strInsert, dbFailonerror

这段代码假设first_field和second_field是文本数据类型。如果它们是数字，则不需要单引号。

我认为这大致符合您的要求。但是，在使用代码之前，我建议先检查电子表格是否可以干净地导入到新的本机Access表中。（还要检查该新表的数据类型和约束是否与链接的SQL Server表相兼容。）如果可以，请尝试直接从管理工具或适当的工具将电子表格直接导入到SQL Server中。

- HansUp

Answer 3

两个额外的选项：

1. 像表格一样在Access中链接电子表格。在Access 2007中，转到“外部数据”窗格并选择“导入Excel电子表格”。您应该导入到现有数据表、新数据表或仅链接到Excel文件。然后，您将使用这个新的“Excel”表格像一个Access表格一样工作（考虑到性能问题，在最后一种情况下）。

2. 尝试修复Docmd.TransferSpreadsheet问题。我已经使用这种方法几年了，它运行良好，尽管在某些情况下可能有点棘手（我相信这是你的情况）。请提供更多关于此方法的问题的信息，包括您的Access和Excel版本，这将非常值得。

希望我能帮到您。祝您好运。

- Alex

---

罕见的负评，是因为提供了明显错误的信息。ADO与Jet/ACE驱动程序一起使用时，将Excel电子表格视为数据库表格没有问题。请参阅HansUp的答案，包括链接。 - RolandTumble

好的...我错了，我从未使用ADO连接Excel。但是，@RolandTumble先生，其他选项不是完成任务的方法吗？ - Alex

好的，我无法取消踩一下，但如果您编辑您的答案，那么我就可以取消踩了。 - RolandTumble

我点赞了。仅仅是因为让我意识到了 docmd.transferspreadsheet 选项。谢谢。 - rohrl77

Answer 4

为了故障排除，请尝试链接到电子表格并查看遇到的问题，包括在数据表视图中查看时显示错误的数据。

- David-W-Fenton

Answer 5

您也可以尝试将Excel数据复制到剪贴板中，然后粘贴到Access表中。如果有失败的部分，它们会被写入Access中的“粘贴错误”表中。

- Beth