to loop or not to loop? That is the question.
Posted by Lukas on June 18, 2001 7:37 PM
I have written the following macro to extract data on a daily basis from up to 150 individual workbooks.All the workbooks reside in a directory called "raw data"
Sub ImportFile()
' ImportFile Macro'
myFile = Application.GetOpenFilename("Microsoft excel,*.xls")
Application.ScreenUpdating = False
Workbooks.Open FileName:=myFile
Range("a1").Select
Selection.Copy
ActiveWorkbook.Close (True)
Windows("summation.xls").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("a1").Select
ActiveSheet.Paste
Range("a1").Select
In all instances, the data resides in cell A1, the workbooks are numbered ( e.g. 456.xls, 488.xls, 501.xls, etc.) and only sheet 1 of the workbook is used.
The macro works fine, except that I have to open each workbook individually by double-clicking on the filename. Is there a simple way to expand the macro through a loop that would fetch the information automatically and cycle through the workbooks in the directory. I am new to Excel VBA and any help would be greatly appreciated.
Thanks in advance Lukas