Automate how to open several files in excel

[JAZPpl]

Hi,

I am trying to automate the opening of 1000 of excel spreadsheet. Each spreadsheet say is named as 120500,120501..120599
The aim is to open each of these file in a loop and pick some of the values in each of the spreadsheet.
Then after the file is open and the data extracted the file as to be renamed as 1.xls, 2.xls...

So if I summarize:

a) Open the files 120500 to 120599 (1 by 1)
after each file is open extract say (the data of A1 in sheet1 and copy it to an output spreadsheet (called say output.xls). Each o fhte data should be paste one under the previous one

b) Close each file one after the other and rename it to 1.xls, 2.xls...and so on

Cheers

Francois

 

[SkipVought]

Hi,

What's the point in renaming the workbook? Nothing has changed in the original.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[michaelbr55]

Try this,

Sub recourse_open()
For x = 1 To 100
myfile = "c:\temp\" & 120499 + x & ".xls"
Workbooks.Open (myfile)
myvalue = ActiveWorkbook.ActiveSheet.Range("A1&quot.Value
myOutput.myData.Cells(x, 1).Value = myvalue
ActiveWorkbook.SaveAs ("c:\temp\" & x & ".xls&quot
ActiveWorkbook.Close
Next
End Sub

you can substitute workbook("output.xls&quot.worksheet("sheet1&quot for myOutput.myData if you don't want to rename the workbook and sheet.

see Faq's on ref sheet more effectly
thread707-642389

If you want to then delete the source file just "kill" but I wouldn't if I were you, you may end up with nothing, run the above then if everything's OK select the old files in explorer and delete them.



Mike