Opening a list of Excel files and run a macro in each

[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.

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

 

[Claus Busch]

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.

 

[L. Howard]

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.

 

[L. Howard]

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

 

[Claus Busch]

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.

 

[Claus Busch]

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.

 

[L. Howard]

Hi Howard,



Am Fri, 14 Feb 2014 00:51:16 -0800 (PST) schrieb L. Howard:









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

 

[GS]

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

 

[L. Howard]

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)

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

 

[GS]

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

 

[L. Howard]

Sorry about that. It's a 2D array and so...



If Dir(vFilesToOpen(n, 1)) <> "" Then

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

 

[Claus Busch]

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.

 

[GS]

Hi Garry,

Am Fri, 14 Feb 2014 08:50:18 -0500 schrieb GS:


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

 

[GS]

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

 

[GS]

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

 

[GS]

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

 

[GS]

Hi Garry,

Am Fri, 14 Feb 2014 08:50:18 -0500 schrieb GS:


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 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

 

[L. Howard]

Hi Garry,

Am Fri, 14 Feb 2014 08:50:18 -0500 schrieb GS:


if you step with F8 through my macro you will see that the

activeworkbook is closed with

and is not still open till sub ends



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 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>

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