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

Posted by Ivan F Moala on June 18, 2001 9:51 PM

Something like this may help;
Change as required.

Sub ImportFile()
Dim sDir As String
Dim I As Integer
Dim FileSearch

sDir = "C:\Raw Data"
Set FileSearch = Application.FileSearch

Application.ScreenUpdating = False

With FileSearch
.LookIn = sDir
.Filename = "*.xls"
If .Execute > 0 Then
For I = 1 To .FoundFiles.Count

Workbooks.Open Filename:=.FoundFiles(I)
Range("a1").Select
Selection.Copy
ActiveWorkbook.Close (True)
'ThisWorkbook.Activate
Windows("summation.xls").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("a1").Select
ActiveSheet.Paste
Range("a1").Select
Next I
Else
MsgBox "There were no files found. " & sDir
End If
End With

Application.ScreenUpdating = False

End Sub

Ivan

Posted by Lukas on June 20, 2001 3:43 AM

Thanks, Ivan. Your solution works great. Many, many thanks.



Posted by Lukas on June 20, 2001 3:45 AM

Thanks, Ivan. Your solution works great. Many, many thanks.