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

Question

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).

I'm trying to get the following code to work in 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:

Runtime error 424:

Object required

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...

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?

Thanks - your help is much appreciated!

Answer 1

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

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.

In other words, you need to use acApp.DoCmd instead of just DoCmd. Otherwise, your code has no way of knowing what DoCmd means because it is not part of 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 IF you 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.