如何使用 VBA 和后期绑定（无 ADO，无 DAO）将 Excel 数据移动到 Access - DoCmd.TransferSpreadsheet 运行时错误 424？

How to move Excel data to Access using VBA and late binding (no ADO, no DAO) - DoCmd.TransferSpreadsheet runtime error 424?

提问by yehoshuk

I am trying to create a macro that will regularly move 25,000 - 35,000 lines in Excel, to an Access database. As I understand it, if I'm working in an environment where people using the macro have different versions of Excel and Access (I happen to be using 2007), I'm best off using late binding, because it avoids problems with different references and versions,etc. - once it works, it works (even though it's slower).

我正在尝试创建一个宏，它会定期将 Excel 中的 25,000 - 35,000 行移动到 Access 数据库。据我了解，如果我在使用宏的人具有不同版本的 Excel 和 Access（我碰巧使用 2007）的环境中工作，我最好使用后期绑定，因为它避免了不同引用的问题和版本等。- 一旦它起作用，它就会起作用（即使它更慢）。

I'm trying to get the following code to work in Excel VBA:

我试图让以下代码在 Excel VBA 中工作：

Sub TransferToDB()
Dim acApp As Object

ssheet = "C:\Documents and Settings\yk\Desktop\Open Invoice Summary 322 - 2012-07-17.xlsx"
ssrange = "AllData!A1:M28323"

Set acApp = CreateObject("Access.Application")
    acApp.OpenCurrentDatabase ("C:\Documents and Settings\yk\Desktop\Open Invoice Summary.accdb")
    acApp.Visible = True
    acApp.UserControl = True
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, OpenInvoices, ssheet, True, ssrange
    acApp.CloseCurrentDatabase
    acApp.Quit
Set acApp = Nothing


End Sub

When the macro gets to the DoCmd.TransferSpreadsheet line, it stops with an error:

当宏到达 DoCmd.TransferSpreadsheet 行时，它会因错误而停止：

Runtime error 424:

Object required

运行时错误 424：

所需对象

This routine in Excel gets called from another routine that gathers information from many worksheets in a large workbook, and dumps it all in a sheet called "AllData" in that workbook. I keep seeing references online to this DoCmd.TransferSpreadsheet method, but I can't seem to find any cut-and-paste code that works...

Excel 中的此例程从另一个例程中调用，该例程从大型工作簿中的许多工作表中收集信息，并将其全部转储到该工作簿中名为“AllData”的工作表中。我一直在网上看到对这个 DoCmd.TransferSpreadsheet 方法的引用，但我似乎找不到任何有效的剪切和粘贴代码......

1. Why does the routine keep crashing at that point?
2. Is it even possible to transfer date from Excel to Access without using ADO or DAO?
3. Is it true that late binding prevents problems, or is it better to use ADO?

1. 为什么例程在这一点上不断崩溃？
2. 甚至可以在不使用 ADO 或 DAO 的情况下将日期从 Excel 传输到 Access 吗？
3. 后期绑定确实可以防止问题，还是使用 ADO 更好？

Thanks - your help is much appreciated!

谢谢 - 非常感谢您的帮助！

回答by Daniel

This feels a little obvious, but I believe the actual cause of your error message is this:

这感觉有点明显，但我相信您的错误消息的实际原因是：

DoCmd is an object. Here's an exert from the help file Application.DoCmd Property

DoCmd 是一个对象。这是帮助文件中的一项内容Application.DoCmd Property

You can use the DoCmd property to access the read-only DoCmd object and its related methods. Read-only DoCmd. Syntax

expression.DoCmd

expression A variable that represents an Application object.

您可以使用 DoCmd 属性访问只读 DoCmd 对象及其相关方法。只读 DoCmd。句法

表达式.DoCmd

表达式 表示应用程序对象的变量。

In other words, you need to use acApp.DoCmdinstead of just DoCmd. Otherwise, your code has no way of knowing what DoCmdmeans because it is not part of Excel.

换句话说，您需要使用acApp.DoCmd而不仅仅是DoCmd. 否则，您的代码无法知道什么DoCmd意思，因为它不是 Excel 的一部分。

I'll chime in on the other questions now as well.

我现在也将插话其他问题。

1. I'm sure it is, though I don't know how Docmd.TransferSpreadSheet is implemented and it may use DAO or ADO without user involvement.

2. Late binding is often necessary if you are implementing any solution with VBA that uses different Office applications than the one being used IFyou have a newer version of Office than any of your users. In my experience, early binding works fine for upgrading from 2003 to 2010, but once you've made the change you can't go back. Any updates to a legacy VBA will automatically update the references to the current references and destroy version compatibility. So if you use early binding and update before your users, you'll need to switch to late-binding or stop supporting your system.

1. 我确定是这样，虽然我不知道 Docmd.TransferSpreadSheet 是如何实现的，它可能会在没有用户参与的情况下使用 DAO 或 ADO。

2. 如果您正在使用 VBA 实施任何解决方案，而该解决方案使用的 Office 应用程序与使用的 Office 应用程序不同，如果您的 Office 版本比任何用户都新，则通常需要后期绑定。根据我的经验，早期绑定适用于从 2003 年升级到 2010 年的升级，但一旦进行了更改，就无法返回。对旧版 VBA 的任何更新都会自动更新对当前引用的引用并破坏版本兼容性。因此，如果您在用户之前使用早期绑定和更新，则需要切换到后期绑定或停止支持您的系统。

回答by HansUp

Without a reference set for the Access type library, Excel doesn't know about the Access named constants, acImportand acSpreadsheetTypeExcel9. Use those constants' values instead of their names for the TransferSpreadsheet method.

如果没有为 Access 类型库设置引用，Excel 不知道 Access 命名常量，acImport而acSpreadsheetTypeExcel9. 对于 TransferSpreadsheet 方法，使用这些常量的值而不是它们的名称。

If OpenInvoices is the literal Access table name, add quotes. But if OpenInvoices is the name of a variable which holds the name of the Access table, you don't need the quotes.

如果 OpenInvoices 是文字 Access 表名称，请添加引号。但是，如果 OpenInvoices 是保存 Access 表名称的变量的名称，则不需要引号。

DoCmd.TransferSpreadsheet 0, 8, "OpenInvoices", ssheet, True, ssrange

So to answer the 3 parts of your question:

所以要回答你问题的三个部分：

1. I suspect it crashed without quotes around OpenInvoices because Excel found no variable named OpenInvoices, so tried to give TransferSpreadsheetnothing as the table name.
2. Yes, sort of. I think your TransferSpreadsheetapproach should work with the revisions suggested in these answers. However, I also think TransferSpreadsheetuses DAO.
3. Late binding can prevent certain types of problems. But you must then use values for the respective constants instead of their names, or redefine them as constants in your procedure. Late binding vs. ADO is a confusing question to me. It's not an either/or thing ... when I use ADO, I almost always use late binding with it to avoid deployment problems due to early binding with a specific ADO version.

1. 我怀疑它在 OpenInvoices 周围没有引号的情况下崩溃了，因为 Excel 找不到名为 OpenInvoices 的变量，因此尝试不提供TransferSpreadsheet任何表名。
2. 是的，有点。我认为您的TransferSpreadsheet方法应该适用于这些答案中建议的修订。但是，我也认为TransferSpreadsheet使用 DAO。
3. 后期绑定可以防止某些类型的问题。但是您必须随后使用相应常量的值而不是它们的名称，或者在您的过程中将它们重新定义为常量。后期绑定与 ADO 对我来说是一个令人困惑的问题。这不是一个非此即彼的事情......当我使用 ADO 时，我几乎总是使用后期绑定来避免由于早期绑定特定 ADO 版本而导致的部署问题。

回答by Fionnuala

Table name should be a string.

表名应该是一个字符串。

acSpreadsheetTypeExcel9 = 8
acImport = 0
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "OpenInvoices", ssheet, True, ssrange