Excel VBA linking Excel table to Access database

Posted on 2012-08-16
Last Modified: 2012-08-17
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?
Question by:pmpatane
    LVL 22

    Expert Comment

    by: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.
    LVL 18

    Expert Comment

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

    Assisted Solution

    This could be caused by your use of the constants acLink, acSpreadsheetTypeExcel5

    Try replacing them with the numeric literal equivalent values.
    LVL 22

    Accepted Solution

    Good one aikimark, points are yours. I assumed there was a reference to the Access library. But if there isn't and the 'Option Explicit' clause is omitted I can reproduce the error. So I'm pretty sure this is the cause.

    Author Closing Comment

    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.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now