How to loop through a specific folder of workbooks

Posted by Richard O'Brien on February 29, 2000 8:49 AM

I want to construct a macro that will loop through a specific directory of similar workbooks, extract about 30 cells of information from a specific worksheet within each workbook, place the values in these cells (which are calculated) sequentially in a new worksheet (in a new workbook) and save it as a txt file with an IIF extension for import into Quickbooks. Many tries, no success. Ugh!
Thanks in advance for any support

Posted by Chris on March 01, 2000 11:36 AM

Richard,

I'm not going to do all your work for you, but here's some stuff to get started.

This procedure will loop through files and for each will call a procedure called DoSomething. I set the sub folder search to false.

Sub FindXLSFiles()
Dim lFile As Long
With Application.FileSearch
.NewSearch
.FileType = msoFileTypeExcelWorkbooks
.LookIn = "C:\Data"
.SearchSubFolders = False
.Execute SortBy:=msoSortByFileType
If .FoundFiles.Count Then
For lFile = 1 To .FoundFiles.Count
On Error GoTo myerrhand
Workbooks.Open FileName:=.FoundFiles(lFile), updatelinks:=0
xbook = ActiveWorkbook.Name
Application.StatusBar = "Now working on " & ActiveWorkbook.FullName
'Here is the line that calls the macro below, passing the workbook to it
DoSomething ActiveWorkbook
'ActiveWorkbook.Save
Application.DisplayAlerts = False
Windows(xbook).Close
myerrhand:
Windows(ActiveWorkbook.Name).Activate
Next lFile
End If
End With
Application.StatusBar = False
End Sub

Here's the shell of the DoSomething procedure

Sub DoSomething(inBook As Workbook) 'Do whatever you want to each workbook

End Sub

You can record a macro to save the file as text.

HTH,
Chris