Excel VBA 将数据导入现有 Access 数据库 [英] Excel VBA import data to existing Access database

问题描述

我之前发现过类似的问题，但我似乎无法使用这些示例来弄清楚.

I've found similar questions asked before, but I can't seem to figure it out using those examples.

我正在 Excel 中编写宏，其中最后一步需要将 Sheet1!A:Q(第 1 行中的标题)中的所有数据导入 Access 数据库中的 Table1.我需要确保 Excel 中的每一列都对应于 Access 中的正确字段.

I'm writing a macro in Excel where the last step needs to import all the data in Sheet1!A:Q (headers in row 1) to Table1 in an Access database. I need to make sure each column in Excel corresponds to the correct field in Access.

我使用的是 Excel 2010/2013 和 Access 2010/2013.我目前使用的代码是:

I am using Excel 2010/2013 and Access 2010/2013. Code I am currently using is:

Sub AccImport()

Dim acc As New Access.Application
acc.OpenCurrentDatabase "C:\Users\Benjamin\Documents\Database1.accdb"
acc.DoCmd.TransferSpreadsheet _
        acImport, _
        acSpreadsheetTypeExcel12Xml, _
        "Sheet1", _
        Application.ActiveWorkbook.FullName, _
        True, _
        "A1:Q1000"         'Need to change to reflect the last row in the sheet
acc.CloseCurrentDatabase
acc.Quit
Set acc = Nothing

End Sub

代码导致错误消息，突出显示 acc As New Access.Application 并显示编译错误 - 未定义用户定义的类型"

The code results in error message, highlighting acc As New Access.Application and displaying "Compile error - User-defined type not defined"

任何帮助将不胜感激.谢谢！

Any help would be greatly appreciated. Thanks!

推荐答案

试试这个:在 VBA 编辑器中，转到工具 -> 参考.向下滚动到Microsoft Access 11.0 对象库"(您的可能不是 11.0，但您正在寻找显示 Microsoft Access 的那个)并确保选中该框.如果没有，请检查它并单击确定".这解决了我测试时的编译错误.

Try this: In the VBA Editor, go to Tools -> References. Scroll down to "Microsoft Access 11.0 Object Library" (yours might not be 11.0, but you're looking for the one that says Microsoft Access) and ensure the box is checked. If not, check it and click OK. This solved the compile error when I tested it.

这篇关于Excel VBA 将数据导入现有 Access 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！