I need a Macro that will open workbooks and run a different Macro.

[Art MacNeil]

Hello,

Is there a way for a macro to open all workbooks in a folder and then run
another macro?

I have the 'other macro' written already, I just need a macro that will open
200 spreadsheets - (they are not in the same folder but I can move them if
needed, they are in the same directory) - , one at a time, then run my
macro, then save each spreadsheet.

I've asked a similar question of this forum (specifically dealing with txt
files - thank you Jim Cone.) and I've tried to modify that Macro so it will
open *.xls files but I can get it to work.


Any help with this will be greatly appreciated,


Art.

 

[Nikos Yannacopoulos]

See if this works for you:

Sub Process_All_Workbooks()
Dim fs, f, f1
Dim strFile As String
fldr = "C:\SomeFolder" 'Put your folder path here
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(fldr)
Set f1 = f.Files
i = 0
Application.ScreenUpdating = False
For Each f2 In f1
strFile = fldr & f2.Name
If Right(strFile, 4) = ".xls" Then
Workbooks.Open strFile
'Code to do what you want here
'Possibly calling the other macro?
ActiveWorkbook.Close , True
Next
Application.ScreenUpdating = True
End Sub

HTH,
Nikos

 

[Art MacNeil]

Thank you Nikos,

I tried the code below but I now get a "Next without For" error message.
Any idea what I need to do to fix it?


Thank you for your help,

Art.

 

[Nikos Yannacopoulos]

Art,

Yes, I do have an idea! My mistake (always a problem with untested
code), and a typical misleading error message: the nessage should
actually be "If without End If". I omitted the End If, the code should be:

Sub Process_All_Workbooks()
Dim fs, f, f1
Dim strFile As String
fldr = "C:\SomeFolder" 'Put your folder path here
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(fldr)
Set f1 = f.Files
i = 0
Application.ScreenUpdating = False
For Each f2 In f1
strFile = fldr & f2.Name
If Right(strFile, 4) = ".xls" Then
Workbooks.Open strFile
'Code to do what you want here
'Possibly calling the other macro?
ActiveWorkbook.Close , True
End If
Next
Application.ScreenUpdating = True
End Sub

Hope it works this time!
Nikos

 

[Art MacNeil]

Brilliant!!


It works like a charm. I do have one other request.

Is there a way to automatically save the spreadsheets/workbooks? There are
times when I update 200 spreadsheets/workbooks. Also I'd like to have a way
to not save the changes because there are times when I'll run a Macro on a
bunch of spreadsheets/workbooks but don't want them saved.

Is this possible?

Thanks again,

Art.

 

[Art MacNeil]

I added a ActiveWorkbook.Save line to my Macro so now I don't get the "Do
you want to save the changes you made..." error message. However, I'd like
to find a way to select "No" to the "Do you want to save the changes you
made..." error message.

Thanks,

Art.

 

[Dave Peterson]

Activeworkbook.close savechanges:=false

If you've already saved your changes.

 

[Art MacNeil]

That did the job.

Thank you Nikos and Dave.

Art.

 

[Art MacNeil]

If anyone else is looking for a way to open multiple workbooks and run
another macro on each of them, you can use the following:
Credit to Nikos Yannacopoulos and Dave Peterson for making this Macro as
good as it is.
If you want to save your changes use "True" at the "ActiveWorkbook.Close
savechanges" line.
If you don't want those changes saved, use "False" at the
"ActiveWorkbook.Close savechanges" line.


Here's the code:

Dim fs, f, f1
Dim strFile As String
fldr = "C:\Test Area\" 'Put your folder path here
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(fldr)
Set f1 = f.Files
i = 0
Application.ScreenUpdating = False
For Each f2 In f1
strFile = fldr & f2.Name
If Right(strFile, 4) = ".xls" Then
Workbooks.Open strFile



Call Macro1 'Call another macro or write code to do what you want here.

ActiveWorkbook.Close savechanges:=True 'Change accordingly.
True will save the spreadsheet and False will not.
End If
Next
Application.ScreenUpdating = True
End Sub



Thank you guys,

Art.