Multiple spreadsheets

[Guest]

I receive multiple excel spreadsheets each week as part of a survey. I need
to consolidate several of the fields into a databse. How can I use
VBA/macro/function to consolidate the information from several excel
spreadsheets to one table without having to open all the spreadsheets?
Thank you.

 

[Ken Snell [MVP]]

You could import the spreadsheets into temporary tables (see
TransferSpreadsheet in Help file), and then run append and update queries as
needed to copy and modify the data into permanent tables.

 

[Guest]

I have done this for excels with one sheets. Now I want to do the same thing
but this time I want to import only the fifth sheet with name INCOME and to
import the first 4 columns (A to D). Using TransferSpreadsheet I know can
put the range. Can you tell me how to write the range.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp",
strFile, True, "........."

Kostas

 

[Ken Snell [MVP]]

For your example, the "range" argument would be (I believe)
INCOME!A