 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2006I need a Macro that will open workbooks and run a different Macro.
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. 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 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. > See if this works for you: > [quoted text clipped - 20 lines] > HTH, > Nikos >>Hello, >> [quoted text clipped - 13 lines] >> >>Art. 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 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, > [quoted text clipped - 25 lines] > Hope it works this time! > Nikos 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. > Brilliant!! > [quoted text clipped - 40 lines] >> Hope it works this time! >> Nikos Activeworkbook.close savechanges:=false If you've already saved your changes. > 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 [quoted text clipped - 49 lines] > >> Hope it works this time! > >> Nikos  SignatureDave Peterson That did the job. Thank you Nikos and Dave. Art. > Activeworkbook.close savechanges:=false > [quoted text clipped - 56 lines] >> >> Hope it works this time! >> >> Nikos 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. > Activeworkbook.close savechanges:=false > [quoted text clipped - 56 lines] >> >> Hope it works this time! >> >> Nikos
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.