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

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

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.

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

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

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

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

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