Multiple spreadsheets

[Mrich]

Hello,
I need to import data from multiple excel file spreadsheets into one Access
table.
All the data in all spreadsheets are in Sheet 1 and in the same format.
I know how to get external data but I was wondering if it was a faster way
being there are
about 20-30 different Excel files but all in the same format.
I'm trying to correct a situation to make things simpler. Right now I am
copying from one sheet at a time and pasting into a "master" spreadsheet
where I can then import into
MS Access.
Thanks in advance.

 

[John Vinson]

Hello,
I need to import data from multiple excel file spreadsheets into one Access
table.
All the data in all spreadsheets are in Sheet 1 and in the same format.
I know how to get external data but I was wondering if it was a faster way
being there are
about 20-30 different Excel files but all in the same format.
I'm trying to correct a situation to make things simpler. Right now I am
copying from one sheet at a time and pasting into a "master" spreadsheet
where I can then import into
MS Access.
Thanks in advance.

This should be fairly easy to automate using the TransferSpreadsheet()
method. Could you put the filenames of the Excel workbooks into a
table? If so you could loop through the records in this table and use
TransferSpreadsheet to migrate the data. Check out the Help topic for
the method in the VBA help.

John W. Vinson[MVP]

 

[pietlinden]

alternatively, if they're all in the same directory, you can use the
Dir function to loop through the contents of the directory and then use
TransferSpreadsheet to import the files. You could still write the
filenames to a table if you wanted...

 

[Guest]

When I use the TransferSpreadsheet command I always get the first sheet.
Is there a possibility to get for instance sheed2. I want to import
different sheets from one excel document into different tables in one
database.
Thank you..........

 

[Mrich]

Thank you much.....its a start..

This should be fairly easy to automate using the TransferSpreadsheet()
method. Could you put the filenames of the Excel workbooks into a
table? If so you could loop through the records in this table and use
TransferSpreadsheet to migrate the data. Check out the Help topic for
the method in the VBA help.

John W. Vinson[MVP]