Multiple workbooks - NEED HELP

[JaneCranberry]

I need to run a macro on hundreds of workbooks. I have the macro, but I can
not figure out how to access all these workbooks (in a folder) in one step.
In other words, I do not want to assign each workbook as a variable ... I
might as well open each manually and run the macro, as I have been.
Do I have to open all the workbooks first?
Can I write a macro that looks for a folder. selects the first workbook,
applies the macro, closes the workbook, selects the next workbook, applies
the macro ... etc.?
PLEASE I need to organize this data ASAP
Thank you
Jane Mika
Cranberry Station
UMASS

 

[stephen.h.dow]

try this:
http://spreadsheetpage.com/index.php/tip/getting_a_list_of_file_names_using_vba/

 

[Per Jessen]

Hi Jane

I found this macro at http://www.ozgrid.com/News/aug-2005.htm:

Sub RunCodeOnAllXLSFiles()

Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = FalseApplication.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "C:\MyDocuments\TestResults"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(.FoundFiles(lCount))
'DO YOUR CODE HERE
wbResults.Close SaveChanges:=True
Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Hopes it helps

Regards,
Per

 

[Mike H]

Hi,

Try this. Change the folder to suit and call your macro where it say 'Do
things. It will open every .xls file in turn in the folder

Sub OpenFiles()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\") ' Change to suit
For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path & "\" & objFile.Name
'Do things
ActiveWorkbook.Close True 'or false
End If
Next
End Sub


Mike