Open all workbooks inside a folder, run macro and save them?

[Guest]

Hi,

I have a folder that has more than 400 excel files, and all of them contain
unformatted data. I have a macro for formatting this data that runs as
required. The problem is I need to open each file individually and run the
macro to format the data in each file.

So is there a way I can open all the workbooks inside the folder, run the
macro that I have already for all of them, and then save the workbook? the
data is contained in Sheet 1 in all the workbooks.

Thanks

 

[Bob Phillips]

Sub LoopFolders()
Dim oFSODim Folder As Object
Dim Files As Object
Dim file As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder("c:\MyTest")

For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then
Workbooks.Open Filename:=file.Path
'<<<<< run macro here on Activeworkbook
Activeworkbook.Close SaveChanges:=False
End If
Next file
Set oFSO = Nothing

End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks for the quick reply Bob, but when I run the macro I get an error
message, and the text "Dim oFSODim Folder As Object" is highlighted when I go
into De-bug mode.

When I run the LoopFolders macro, do I have to open my 1st workbook and run
the macro in it?

Thanks,

Peter.

 

[Dave Peterson]

That should be two lines:
Dim oFSO
Dim Folder As Object

 

[Guest]

Thanks Bob

Sub LoopFolders()
Dim oFSODim Folder As Object
Dim Files As Object
Dim file As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder("c:\MyTest")

For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then
Workbooks.Open Filename:=file.Path
'<<<<< run macro here on Activeworkbook
Activeworkbook.Close SaveChanges:=False
End If
Next file
Set oFSO = Nothing

End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Dave

That should be two lines:
Dim oFSO
Dim Folder As Object

 

[Bill Renaud]

You could also use the Application.GetOpenFilename to prompt for the folder
and the files to process. Then the macro is more general-purpose and will
not require changing some day if you decide to use it on a subset of files
in another folder. This macro will display a file open dialog box, allow
you to navigate to the desired folder and then select which files you want
to process.

'----------------------------------------------------------------------
Public Sub ProcessAllFiles()
Dim varFileList As Variant
Dim lngFileCount As Long
Dim ilngFileNumber As Long
Dim strFileName As String

varFileList = Application _
.GetOpenFilename(FileFilter:="Excel Files (*.xls),*.xls",
_
Title:="Open Excel File(s)", _
MultiSelect:=True)

lngFileCount = FileCount(varFileList)

If lngFileCount = 0 Then GoTo ExitSub
'User canceled out of dialog box.

For ilngFileNumber = 1 To lngFileCount
Workbooks.Open Filename:=CurrentFileName(varFileList, ilngFileNumber)

'Call your macro here.

'Set SaveChanges according to whether your macro already saved or not.
ActiveWorkbook.Close SaveChanges:=False
Next ilngFileNumber

ExitSub:
End Sub

'----------------------------------------------------------------------
Private Function FileCount(varFileList) As Long
Select Case VarType(varFileList)
Case vbBoolean
'User canceled out of the File Open dialog box.
FileCount = 0
Case vbString
'Dialog box is in single file mode.
'Single file selected for opening only.
FileCount = 1
Case vbArray + vbVariant
'Multiple files selected for processing.
FileCount = UBound(varFileList) - LBound(varFileList) + 1
End Select
End Function

'----------------------------------------------------------------------
Private Function CurrentFileName(varFileList As Variant, _
ilngFileNumber As Long) As String

Select Case VarType(varFileList)
Case vbBoolean
'User canceled out of the File Open dialog box.
CurrentFileName = ""
Case vbString
'Dialog box is in single file mode.
'Single file selected for opening only.
CurrentFileName = varFileList
Case vbArray + vbVariant
'Multiple files selected for processing.
'Return the filename currently pointed to.
CurrentFileName = CStr(varFileList(ilngFileNumber))
End Select
End Function

 

[Bill Renaud]

Note that the GetOpenFilename method (Excel 2000) returns an array for
varFileList where the index starts at 1, even without an "Option Base 1"
statement at the top of the module. This behavior cannot be assumed in
other situations.