Automation from Excel

[agibsonsw]

Hello. Excel 2003/Access 2003
I have this short procedure in Excel which should use DoCmd to import a spreadsheet to a new table.

Code: Select all

'Tools- References Microsoft Access 11.0 Object Library
Sub UsingAccess()

    Dim ac As Access.Application
    
    Application.ScreenUpdating = False
    
    Set ac = New Access.Application
    ac.OpenCurrentDatabase "C:\Documents and Settings\A3train\My Documents\AndyDB.mdb"
    ac.DoCmd.TransferSpreadsheet acImport, 8, "tblEmployees", _
        "C:\Documents and Settings\A3train\My Documents\Employees", True, ""
    ac.Quit
    Set ac = Nothing
    Application.ScreenUpdating = True
End Sub

but I get an error saying it can't open the database because it's missing or opened exclusively.

I think I'm missing something straight-forward? Any idea?

[HansV]

You also get this error - misleadingly - if there is an error in the path/filename of the database. Check carefully.

[agibsonsw]

Hello.
When I open Access normally there is a confirmation box 'Enable unsafe expressions..' and then a Security Warning 'Open..'. I think perhaps these might be the issue.
Andy.

[HansV]

I don't know whether those are relevant, but take a look at Enable or disable sandbox mode and Hide the Unsafe Expressions security warning message in Access 2003.

BTW, I tested your code, substituting the path+filename of a database and a workbook on my PC. The code ran succesfully, without warnings or error messages.

[agibsonsw]

Hi. Yes it was due to the 'unsafe expressions' etc. warnings. We also got it working on another PC.

Thanks for your assistance. Andy.