Example of Programmatic Import of an Excel Worksheet to an Access Table

[Don]

What built-in functions should I be looking for to programmatically
imporrting an Excel worksheet into an Access (2003) table? There does not
seem to be an "import" function per se (or I missed it!). There was a
reference to an SQL string in a posting in
microsoft.public.access.externaldata. However, it was not clear how the
connection to the spreadsheet itself was made.

In short, my goal is to allow the user to pick the desired Excel file (.xls)
and then the program import the data into a table. The spreadsheet is
relatively simple having one worksheet and is fixed format (i.e. the columns
do not vary from instance to instance).

Any suggestions will be greatly appreciated!

Thanks!

Don

 

[Rob Oldfield]

Take a look at

http://www.mvps.org/access/api/api0001.htm

for detail on how to allow a user to pick a file and the TransferSpreadsheet
method in VBA for how to run the import.

 

[Don]

Rob,

Exactly the hints I needed!! Especially the TransferSpreadsheet function!
Don't know how I missed that!

Is there any advantage to using the APIs as described at
http://www.mvps.org/access/api/api0001.htm as opposed to Common Dialog
control? I have used the Common Dialog control in the past.

Thanks!!

Don

 

[Rob Oldfield]

Yes. You're better off using the api method. The common dialog control is
buggy and quite frequently fails due to references going wrong.

 

[Don]

Rob,

Thanks for the advice!

Don

Yes. You're better off using the api method. The common dialog control is
buggy and quite frequently fails due to references going wrong.