Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a list of Excel files and run a macro in each
Is there a generic code that will take a list of Excel files (workbooks) in column A, open each one, and run a macro that is in that file then close it.
I have code that opens a workbook and does some stuff then saves it but the name is hard coded in the open file code statement and it is a single workbook. In my search I find little help and what I did find this is about all I understood of the code, which runs the sub named MyMacro for that workbook. Run("MyMacro") I assume the code would assign each file name to a variable and then that variable would be properly placed in the open file code and I would have to change MyMacro to the actual name of the sub I wanted to run. Thanks. Howard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a list of Excel files and run a macro in each
Hi Howard,
Am Thu, 13 Feb 2014 21:53:42 -0800 (PST) schrieb L. Howard: Is there a generic code that will take a list of Excel files (workbooks) in column A, open each one, and run a macro that is in that file then close it. if you have the full names in column A then try: Sub Test() Dim LRow As Long Dim myRng As Range Dim rngC As Range Application.ScreenUpdating = False LRow = Cells(Rows.Count, 1).End(xlUp).Row Set myRng = ThisWorkbook.Sheets("Sheet1").Range("A1:A" & LRow) For Each rngC In myRng Workbooks.Open (rngC) With ActiveWorkbook MyMacro .Save .Close End With Next Application.ScreenUpdating = True End Sub Change MyMacro to your macro name Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a list of Excel files and run a macro in each
if you have the full names in column A then try: Sub Test() Dim LRow As Long Dim myRng As Range Dim rngC As Range Application.ScreenUpdating = False LRow = Cells(Rows.Count, 1).End(xlUp).Row Set myRng = ThisWorkbook.Sheets("Sheet1").Range("A1:A" & LRow) For Each rngC In myRng Workbooks.Open (rngC) With ActiveWorkbook MyMacro .Save .Close End With Next Application.ScreenUpdating = True End Sub Change MyMacro to your macro name Regards Claus B. -- Hmmm, not a daunting as I thought the code would be. I'll give it a go. Thanks, Claus. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a list of Excel files and run a macro in each
I'm getting an error on MyMacro as - sub not defined. (That is the name of the macro I am trying to run, see below.) The code opens this and two other workbooks with similar test names. C:\Users\Howard Kittle\Documents\aaaaBooger.xlsm I comment out MyMacro because it fails and also .Save & .Close to test if the open part works, which it does. 'MyMacro '.Save '.Close This is the macro I have in each workbook, in a standard module. (Tried it in the sheet module also) Option Explicit Sub MyMacro() MsgBox "Test book aaaaBooger" Range("F1") = "aaaaBooger" End Sub This does not work either Run("MyMacro") Howard |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a list of Excel files and run a macro in each
Hi Howard,
Am Fri, 14 Feb 2014 00:51:16 -0800 (PST) schrieb L. Howard: The code opens this and two other workbooks with similar test names. Option Explicit Sub MyMacro() MsgBox "Test book aaaaBooger" Range("F1") = "aaaaBooger" End Sub I thought MyMacro is in ThisWorkbook the workbook from which you open the other workbooks. Try workbooks with different names. The macro fails if you have 3 workbooks with same names. Try: Sub Test() Dim LRow As Long Dim myRng As Range Dim rngC As Range Application.ScreenUpdating = False LRow = Cells(Rows.Count, 1).End(xlUp).Row Set myRng = ThisWorkbook.Sheets("Sheet1").Range("A1:A" & LRow) For Each rngC In myRng Workbooks.Open (rngC) With ActiveWorkbook Run ("'" & .Name & "'!MyMacro") .Save .Close End With Next Application.ScreenUpdating = True End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a list of Excel files and run a macro in each
Hi Howard,
Am Fri, 14 Feb 2014 12:19:50 +0100 schrieb Claus Busch: I thought MyMacro is in ThisWorkbook the workbook from which you open the other workbooks. Try workbooks with different names. The macro fails if you have 3 workbooks with same names. why don't you put the code into the Workbook_Open Event. The code fires if the workbook is opened. You have to refer to the expected sheet or you have to activate the expected sheet after opening. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a list of Excel files and run a macro in each
On Friday, February 14, 2014 3:19:50 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Fri, 14 Feb 2014 00:51:16 -0800 (PST) schrieb L. Howard: The code opens this and two other workbooks with similar test names. Option Explicit Sub MyMacro() MsgBox "Test book aaaaBooger" Range("F1") = "aaaaBooger" End Sub I thought MyMacro is in ThisWorkbook the workbook from which you open the other workbooks. Try workbooks with different names. The macro fails if you have 3 workbooks with same names. Try: Sub Test() Dim LRow As Long Dim myRng As Range Dim rngC As Range Application.ScreenUpdating = False LRow = Cells(Rows.Count, 1).End(xlUp).Row Set myRng = ThisWorkbook.Sheets("Sheet1").Range("A1:A" & LRow) For Each rngC In myRng Workbooks.Open (rngC) With ActiveWorkbook Run ("'" & .Name & "'!MyMacro") .Save .Close End With Next Application.ScreenUpdating = True End Sub Regards Claus B. -- This works quite fine! I do have three different named workbooks, probably was not clear on that. Sorry. Thank you very much. Regards, Howard |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a list of Excel files and run a macro in each
You might want to think about adding code in case your files don't
exist in the hard path on your sheet. Optionally, you might want to use a FileDialog to multi-select the files if they're in the same folder. I'm not in favor of Claus' suggestion to use Workbook_Open in case you need to work on the file and test macros. Otherwise, you'd have to open with macros disabled. Suggestion... Sub OpenAndRun() Dim vFilesToOpen, n&, sFilename, wkbTmp As Workbook vFilesToOpen = ActiveSheet.Range("FilesToOpen") '//named dynamic range If IsArray(vFilesToOpen) Then For n = LBound(vFilesToOpen) To UBound(vFilesToOpen) If Dir(vFilesToOpen(n)) < "" Then Set wkbTmp = Workbooks.Open(vFilesToOpen(n)) Call RunMacro_CloseFile(wkbTmp) End If End If Next 'n Else '//single file or none listed If Dir(vFilesToOpen) < "" Then Set wkbTmp = Workbooks.Open(vFilesToOpen) Call RunMacro_CloseFile(wkbTmp) End If End If End Sub Sub RunMacro_CloseFile(Wkb As Workbook) With Wkb Application.Run ("'" & .Name & "'!MyMacro") .Close SaveChanges:=True End With End Sub ...where the actual running of the macro is done in a separate sub so the file will close before opening the next file. (Otherwise, the file doesn't close until the sub ends) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a list of Excel files and run a macro in each
On Friday, February 14, 2014 5:50:18 AM UTC-8, GS wrote:
You might want to think about adding code in case your files don't exist in the hard path on your sheet. Optionally, you might want to use a FileDialog to multi-select the files if they're in the same folder. I'm not in favor of Claus' suggestion to use Workbook_Open in case you need to work on the file and test macros. Otherwise, you'd have to open with macros disabled. Suggestion... Sub OpenAndRun() Dim vFilesToOpen, n&, sFilename, wkbTmp As Workbook vFilesToOpen = ActiveSheet.Range("FilesToOpen") '//named dynamic range If IsArray(vFilesToOpen) Then For n = LBound(vFilesToOpen) To UBound(vFilesToOpen) If Dir(vFilesToOpen(n)) < "" Then Set wkbTmp = Workbooks.Open(vFilesToOpen(n)) Call RunMacro_CloseFile(wkbTmp) End If End If Next 'n Else '//single file or none listed If Dir(vFilesToOpen) < "" Then Set wkbTmp = Workbooks.Open(vFilesToOpen) Call RunMacro_CloseFile(wkbTmp) End If End If End Sub Sub RunMacro_CloseFile(Wkb As Workbook) With Wkb Application.Run ("'" & .Name & "'!MyMacro") .Close SaveChanges:=True End With End Sub ..where the actual running of the macro is done in a separate sub so the file will close before opening the next file. (Otherwise, the file doesn't close until the sub ends) -- Garry Hi Garry, I created a dynamic named range TO Wit: Name: FilesToOpen Refers To: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) I get an error on this line: If Dir(vFilesToOpen(n)) < "" Then If I hover the cursor around a bit I fet the little alert boxes "subscript out of range" Howard |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a list of Excel files and run a macro in each
Sorry about that. It's a 2D array and so...
If Dir(vFilesToOpen(n, 1)) < "" Then -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a list of Excel files and run a macro in each
On Friday, February 14, 2014 6:53:24 AM UTC-8, GS wrote:
Sorry about that. It's a 2D array and so... If Dir(vFilesToOpen(n, 1)) < "" Then -- Garry Hi again. Had to add the 1 in this line also Set wkbTmp = Workbooks.Open(vFilesToOpen(n, 1)) And it seems to do it's stuff, flies right. Can't follow the code very much but it does open the test workbooks and run the macro in each. Thanks for the help. Howard |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a list of Excel files and run a macro in each
Hi Garry,
Am Fri, 14 Feb 2014 08:50:18 -0500 schrieb GS: ..where the actual running of the macro is done in a separate sub so the file will close before opening the next file. (Otherwise, the file doesn't close until the sub ends) if you step with F8 through my macro you will see that the activeworkbook is closed with ..close and is not still open till sub ends Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a list of Excel files and run a macro in each
Hi Garry,
Am Fri, 14 Feb 2014 08:50:18 -0500 schrieb GS: ..where the actual running of the macro is done in a separate sub so the file will close before opening the next file. (Otherwise, the file doesn't close until the sub ends) if you step with F8 through my macro you will see that the activeworkbook is closed with .close and is not still open till sub ends Regards Claus B. Not the case if one file because the wkbTmp object isn't destroyed until the macro ends. In the array loop, new ref replaces old ref and so wkbTmp closes when ref is reset to new wkbTmp. Last one doesn't close until sub ends. (What I mean is still visible in Project Explorer. Makes good arg for *wkbTmp=Nothing*!) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a list of Excel files and run a macro in each
On Friday, February 14, 2014 6:53:24 AM UTC-8, GS wrote:
Sorry about that. It's a 2D array and so... If Dir(vFilesToOpen(n, 1)) < "" Then -- Garry Hi again. Had to add the 1 in this line also Set wkbTmp = Workbooks.Open(vFilesToOpen(n, 1)) And it seems to do it's stuff, flies right. Can't follow the code very much but it does open the test workbooks and run the macro in each. Thanks for the help. Howard Yes, thanks for making the correction. (I wrote the code in TextPad and so no testing was done<g) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a list of Excel files and run a macro in each
This revision releases (and removes) wkbTmp each iteration *before* the
ref is reset. It also does same for single file. Sub OpenAndRun() Dim vFilesToOpen, n&, sFilename, wkbTmp As Workbook vFilesToOpen = ActiveSheet.Range("FilesToOpen") '//named dynamic range If IsArray(vFilesToOpen) Then For n = LBound(vFilesToOpen) To UBound(vFilesToOpen) If Dir(vFilesToOpen(n, 1)) < "" Then Set wkbTmp = Workbooks.Open(vFilesToOpen(n, 1)) Call RunMacro_CloseFile(wkbTmp) Set wkbTmp = Nothing End If Next 'n Else '//single file or none listed If Dir(vFilesToOpen) < "" Then Set wkbTmp = Workbooks.Open(vFilesToOpen) Call RunMacro_CloseFile(wkbTmp) Set wkbTmp = Nothing End If End If End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a list of Excel files and run a macro in each
Another way...
Sub OpenAndRun() Dim vFilesToOpen, n&, sFilename, wkbTmp As Workbook vFilesToOpen = ActiveSheet.Range("FilesToOpen") '//named dynamic range If IsArray(vFilesToOpen) Then For n = LBound(vFilesToOpen) To UBound(vFilesToOpen) If Dir(vFilesToOpen(n, 1)) < "" Then Set wkbTmp = Workbooks.Open(vFilesToOpen(n, 1)) Call RunMacro_CloseFile(wkbTmp) End If Next 'n Else '//single file or none listed If Dir(vFilesToOpen) < "" Then Set wkbTmp = Workbooks.Open(vFilesToOpen) Call RunMacro_CloseFile(wkbTmp) End If End If End Sub Sub RunMacro_CloseFile(Wkb As Workbook) With Wkb Application.Run ("'" & .Name & "'!MyMacro") .Close SaveChanges:=True End With Set Wkb = Nothing End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a list of Excel files and run a macro in each
Hi Garry,
Am Fri, 14 Feb 2014 08:50:18 -0500 schrieb GS: ..where the actual running of the macro is done in a separate sub so the file will close before opening the next file. (Otherwise, the file doesn't close until the sub ends) if you step with F8 through my macro you will see that the activeworkbook is closed with .close and is not still open till sub ends Regards Claus B. Ah geez! I mistated about the workbook not closing. I meant to clarify that the resources were not released as the file was still listed in Project Manager. Yes, the file[s] did close as per the .Close statement; no arg about that. I was trying to convey the idea of *deliberately releasing* the resources held by the object ref. Clearly I need some decent sleep!<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a list of Excel files and run a macro in each
On Friday, February 14, 2014 1:04:41 PM UTC-8, GS wrote:
Hi Garry, Am Fri, 14 Feb 2014 08:50:18 -0500 schrieb GS: ..where the actual running of the macro is done in a separate sub so the file will close before opening the next file. (Otherwise, the file doesn't close until the sub ends) if you step with F8 through my macro you will see that the activeworkbook is closed with .close and is not still open till sub ends Regards Claus B. Ah geez! I mistated about the workbook not closing. I meant to clarify that the resources were not released as the file was still listed in Project Manager. Yes, the file[s] did close as per the .Close statement; no arg about that. I was trying to convey the idea of *deliberately releasing* the resources held by the object ref. Clearly I need some decent sleep!<g -- Garry I stopped back by the thread and read your exchanges, pretty much above my pay grade. But nonetheless I did read it to see if I could glean a nugget of info. That's pretty high hanging fruit to me to try to pick, but thanks. Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening / Closing files using Excel Macro? | Excel Programming | |||
Excel files with list function not opening in Excel 2000 | Excel Discussion (Misc queries) | |||
macro for opening all excel files and copying contents | Excel Programming | |||
Error opening files from a list in a spreadsheet | Excel Programming | |||
Opening Files based on list | Excel Programming |