Excel running Access VBA (1 Viewer)

SiGill

Registered User.
Local time
Today, 00:14
Joined
Dec 22, 2008
Messages
72
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.
 
Last edited:

Cliff67

Registered User.
Local time
Today, 00:14
Joined
Oct 16, 2018
Messages
175
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

Well-known member
Local time
Today, 03:14
Joined
Apr 9, 2015
Messages
4,339
theres another object needed:

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

SiGill

Registered User.
Local time
Today, 00:14
Joined
Dec 22, 2008
Messages
72
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.
 

Users who are viewing this thread

Top Bottom