Using TransferSpreadsheet to import multiple Excel Files

[hois]

I have multiple excel spreadsheets I am trying to import utilizing a Macro
with TransferSpreadsheet as a step. Problem is that I must change the path
of the excel file in the Macro each time I import a different excel file. Is
there any way I can have the Macro read from a list of the excel file names
and automatically run through each file name.

 

[Steve Schapel]

Hois,

See this article by Ken Snell:
Import Data from Specific Worksheets in All Excel Files in a single Folder
via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpFldWrkFiles

 

[Gina Whipp]

Hois,

You could add multiple TransferSpreadsheet lines. Just keep adding with the
names of the seperate paths until you gotten them all then Run.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[hois]

Steve, this was very helpful. However, I must run all additional steps in
the Macro prior to importing the next spreadsheet. (I have some query's etc
that clean up the data) Any way to have the code below run through the
entire macro prior to selecting the next excel spreadsheet to import.

 

[aaron.kempf]

you should move to a real ETL tool like SSIS or DTS.

Access shouldn't be used to move data around, Jet is dead and it has
been for a decade.

-Aaron

 

[Tony Toews [MVP]]

I have multiple excel spreadsheets I am trying to import utilizing a Macro
with TransferSpreadsheet as a step. Problem is that I must change the path
of the excel file in the Macro each time I import a different excel file. Is
there any way I can have the Macro read from a list of the excel file names
and automatically run through each file name.

Please ignore Aaron's posting as Aaron's answer to just about every question is SQL
Server and ADPs. No matter how appropriate his response.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/