Excel running Access VBA

[SiGill]

I have the following code in Excel VBA. The OpenQuery part of the code is working but the Transfer Spreadsheet part isn't giving warning

runtime error 3170 Could not find installable ISAM

Any ideas?

Set A = CreateObject("Access.Application")
A.Visible = False
A.OpenCurrentDatabase (strDatabase)
A.DoCmd.OpenQuery "myQuery"
A.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "myTable", strTempFile, True
A.CloseCurrentDatabase
A.Quit
Set A = Nothing

The bit of code in Red is what it points to when I Debug

Any help would be appreciated.

 

[Cliff67]

Hi SiGill

it could be that reference to "Microsoft Access 12.0 Object Library" is not set on your database. I had a similar problem a while back on my current database took me ages to find it.

Open your database, open any VBA module and select Tools at the top, then select References and tick the object library required

hope it helps

Cliff

 

[Ranman256]

theres another object needed:

With a
     with .Application
        .DoCmd.OpenForm "myForm 
        .DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "myTable", strTempFile, True
     end with
end with

 

[SiGill]

Thanks Cliff I stumbled upon that too and was about to update this thread with that when I saw your reply. I added in that reference and now it works perfectly.