Automation

[Tom]

I have a series of Excel spreadsheets (probably 135 - 150 ) that I need to
retrieve data and put the data into an access database. I have the VBA code
so that it retrieves the data from an individual spreadsheet. I think I can
set up VB code to loop thru a folder and open each excel object get the
information I need and update the database.

What is the best way to accomplish this goal?
1. Is it better to write VB code to go to a folder, loop thru the objects
(ie: open the spreadsheet, update the database and then move to the next
spreadsheet).
2. Is it better to copy the VBA code into a module for each spreadsheet,
open the spreadsheet, update the database, close the spreadsheet and repeat.

Looking for some opinions on what should be the "best" way to accomplish the
task. I think looping the Excel objects would be much of the same as going
thru the Outlook object for example.

Something like this: For Each objExcel In objExcelColl

Thanks for any comments, ideas, insight, code etc!

Tom

 

[Tim Barlow]

Tom,

If all the spreadsheets are in the same folder, then you could use Dir() to
loop through them all e.g.

Dim aFilename as String
aFilename = Dir("C:\My Documents\Data\*.xls")

Do While aFilename <> ""
' open the file and process it here
'.
'.

' get next filename for the next loop
aFilename = Dir()
Loop


Tim

 

[Tom]

Thanks Tim
I think I can get thru them all in the loop, I am not sure that its the
best method to do this type of job. I am concerned about bombing out while
opening so many files in loop and some of the other automation errors that
you seem to read about. I am leaning towards the code to loop thru the
directory but would wonder if this is something that will end up taking alot
longer to debug and get working then just running code in each file.

I am posting this message but I still cant see my thread in the server.
Weird I cant see my original message or your responce but if I do a search
then I can at least access them.

Thanks for your input!
Tom



Tom,

If all the spreadsheets are in the same folder, then you could use Dir() to
loop through them all e.g.

Dim aFilename as String
aFilename = Dir("C:\My Documents\Data\*.xls")

Do While aFilename <> ""
' open the file and process it here
'.
'.

' get next filename for the next loop
aFilename = Dir()
Loop


Tim

 

[Patrick Molloy]

IMHO I'd recommend one code in a separate module & use
this to process each workbook in your folder.
In your Access database, create a table to save the names
of processed workbooks. Use Scripting Runtime as well to
open a textstream to a log file. Write a note to the
textstream for each part of the process. If there's a
crash or issue, (1) your log will indicate where and (2)
your table will tell you which files have already been
processed.

HTH
Patrick Molloy
Microsoft Excel MVP