asked on

Excel VBA linking Excel table to Access database

I am trying to link Excel tables to Access, run append queries to get the data from them and then unlink the tables. The last two are simple, the linking of the tables though is frustrating. My code is:

Sub OpenAcc()
    Set oapp = CreateObject("Access.Application")
    oapp.Visible = True
    oapp.OpenCurrentDatabase "O:\RCD\RCD db.accdb"

    DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel5, "13 mo Report", "O:\RCD\Report\Billable Expenses.XLS", True, "TestingRange"
End Sub

Open in new window

If I run the code as is, I get an error 424 (Object Required). If I precede the DoCmd with oapp., I get eror 3170 (Could not find installable ISAM). What am I doing wrong?

Nico Bontenbal

The code is OK. It worked on my machine with Office 2007. I used this from Excel:

Sub OpenAcc()
    Dim oapp As Object
    Set oapp = CreateObject("Access.Application")
    oapp.Visible = True
    oapp.OpenCurrentDatabase "G:\Data\EE\Codes.accdb"
    oapp.DoCmd.TransferSpreadsheet 2, 5, "13 mo Report", "G:\Data\EE\crib-11-test.xls", True, "TestingRange"
End Sub

Open in new window

It's the 3170 error you need to resolve. Start by trying to link the Excel table from MS Access manually. See if that works. I think you need to change acSpreadsheetTypeExcel5 to acSpreadsheetTypeExcel12 (or higher if you use Office 2010).

Alternatively you might be able to transfer the data using ADO without even opening the access database, but that would require completely different code.

p912s

What version of Office? Excel? Access? are you running?

SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Nico Bontenbal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pmpatane

ASKER

aikimark, I started doing what you had suggested as I was sure that was probably the issue, but had trouble finding the numeric value of aclink, but then nicobo, I read your assumption about the Access library reference and I thought I would check that out, and d'oh, it was not checked. It worked after that. One funny thing though, after I run my macro that links, pauses and unlinks, it works the first time, then if I run it again I get run-time error -2147417851 which when I Google I get nothing related to it. If I then run the macro a third time, all is well again...not a show-stopper issue as everything else is humming, but weird! Thanks again to both of you!

P.S. p912s, my apologies as you are quite correct I should have included the information you asked for.