Importing Excel into Access 97

G

Guest

I'm still using a Access 97 version for many reasons I won't go into, but
need help importing a later Excel version into it using VBA.
I can currently use the following code to import all but later versions of
Excel.
Can anyone point me in the right direction?
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "InputData",
strInputDir, False, ""
 
J

John Nurick

Hi Jennie,

Try replacing
acSpreadsheetTypeExcel5
with
acSpreadsheetTypeExcel8
 
G

Guest

When I use acSpreadsheetTypeExcel8 I get the error variable not defined.
I've tried re-regisering dll files (regsvr32
msexcl35.dll,msxbse35.dll,mspdox35.dll,and mstext35.dll).
Any other suggestions?
 
J

John Nurick

Hi Jennie,

My fault. In Access 97 the constant is called acSpreadsheetTypeExcel97
not acSpreadsheetTypeExcel8 (that's used in later versions).
 
J

John Nurick

I've lost track of just what you're trying to do. But either
acSpreadsheetTypeExcel97
or
acSpreadsheetTypeExcel8
should work in Access 2002 and later.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Import Excel named range into Access 2
how to import multiple Excel files into multiple Access tables? 9
VBA for Access, import Excell files into Access tables 7
Access Subscript out of range when importing into access 2010 from excel 0
Exporting Excel workbook to Access table 1
Importing Multiple Excel Worksheets into An Access Table 4
Import Several excel files into Several Access Tables 6
Import xls data into table 3

Top