ExcelBanter - Calling excel-macros from script?

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Calling excel-macros from script? (https://www.excelbanter.com/excel-programming/328635-calling-excel-macros-script.html)

grz01

Calling excel-macros from script?

Hi, need a little help he

We have a directory with a number of excel-documents in it.
Each excel-document has one or more macros in them, that should be run
in sequence.
(the macros will fetch info from a data-source and save output to
another excel-doc)
So far, this has been done manually by a person each morning,
and I would like to automate this by a script, but I dont have much
experience with Windows-scripting.

Thus, in "meta-code" what we want to put in the script is:

Open Exceldoc1
- Run MacroA
- Run MacroB
Close Exceldoc1
Open Exceldoc2
- Run MacroC
- Run MacroD
Close Exceldoc2
.... etc ...

What is the simplest/easiest way to accomplish this? VBS-script?
And what is the exact syntax to do this please? Any help appreciated.
---------------------------------------------------------- grz01

Andy Wiggins

Calling excel-macros from script?

You could do this by creating another Excel workbook that acts as the
"manager" of the process. it could run a VBA procedure that opens each
workbook in turn and runs the appropriate macro(s).

Yes, you could do this from a VBS Script. There is an example script at
http://www.bygsoftware.com/Excel/VBA/vbs_script.htm. It doesn't do precisely
what you require but it could point you in the right direction.

--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy

"grz01" wrote in message
ups.com...
Hi, need a little help he

We have a directory with a number of excel-documents in it.
Each excel-document has one or more macros in them, that should be run
in sequence.
(the macros will fetch info from a data-source and save output to
another excel-doc)
So far, this has been done manually by a person each morning,
and I would like to automate this by a script, but I dont have much
experience with Windows-scripting.

Thus, in "meta-code" what we want to put in the script is:

Open Exceldoc1
- Run MacroA
- Run MacroB
Close Exceldoc1
Open Exceldoc2
- Run MacroC
- Run MacroD
Close Exceldoc2
... etc ...

What is the simplest/easiest way to accomplish this? VBS-script?
And what is the exact syntax to do this please? Any help appreciated.
---------------------------------------------------------- grz01

Bob Phillips[_7_]

Calling excel-macros from script?

Here is some example code, untested, but should work.

Unfortunately, running VBScript does not have the debugging facilities VBA
in Excel has.

Dim xlApp

Set xlApp = CreateObject("Excel.application")

xlApp.Workbooks.Open "C:\MyFiles\myDoc1.xls"
xlApp.Run "macroA"
xlApp.Run "macroB"
xlApp.ActiveWindow.Close SaveChanges:=False

xlApp.Workbooks.Open "C:\MyFiles\myDoc2.xls"
xlApp.Run "macroC"
xlApp.Run "macroD"
xlApp.ActiveWindow.Close SaveChanges:=False

'repeat for each workbook/macro

xlApp.Quit
Set xlApp = Nothing

--
HTH

Bob Phillips

"grz01" wrote in message
ups.com...
Hi, need a little help he

We have a directory with a number of excel-documents in it.
Each excel-document has one or more macros in them, that should be run
in sequence.
(the macros will fetch info from a data-source and save output to
another excel-doc)
So far, this has been done manually by a person each morning,
and I would like to automate this by a script, but I dont have much
experience with Windows-scripting.

Thus, in "meta-code" what we want to put in the script is:

Open Exceldoc1
- Run MacroA
- Run MacroB
Close Exceldoc1
Open Exceldoc2
- Run MacroC
- Run MacroD
Close Exceldoc2
... etc ...

What is the simplest/easiest way to accomplish this? VBS-script?
And what is the exact syntax to do this please? Any help appreciated.
---------------------------------------------------------- grz01

Hal[_3_]

Calling excel-macros from script?

VBA Script

Workbooks.Open FileName = "c:\exceldoc1"
Application.Run Range("exceldoc1!macroa")
Application.Run Range("exceldoc1!macrob")
ActiveWorkbook.Close
Workbooks.Open FileName = "c:\exceldoc2"
Application.Run Range("exceldoc1!macroc")
Application.Run Range("exceldoc1!macrod")
ActiveWorkbook.Close
etc

"grz01" wrote in message
ups.com...
Hi, need a little help he

We have a directory with a number of excel-documents in it.
Each excel-document has one or more macros in them, that should be run
in sequence.
(the macros will fetch info from a data-source and save output to
another excel-doc)
So far, this has been done manually by a person each morning,
and I would like to automate this by a script, but I dont have much
experience with Windows-scripting.

Thus, in "meta-code" what we want to put in the script is:

Open Exceldoc1
- Run MacroA
- Run MacroB
Close Exceldoc1
Open Exceldoc2
- Run MacroC
- Run MacroD
Close Exceldoc2
... etc ...

What is the simplest/easiest way to accomplish this? VBS-script?
And what is the exact syntax to do this please? Any help appreciated.
---------------------------------------------------------- grz01

Hal[_3_]

Calling excel-macros from script?

grz01

Calling excel-macros from script?

Thanks Bob,

Seems like I had to use
xlApp.Workbooks.Close
rather than
xlApp.ActiveWindow.Close
Works perfect now :)
Thanks!

Bob Phillips wrote:
Here is some example code, untested, but should work.

Unfortunately, running VBScript does not have the debugging
facilities VBA
in Excel has.

Dim xlApp

Set xlApp = CreateObject("Excel.application")

xlApp.Workbooks.Open "C:\MyFiles\myDoc1.xls"
xlApp.Run "macroA"
xlApp.Run "macroB"
xlApp.ActiveWindow.Close SaveChanges:=False

xlApp.Workbooks.Open "C:\MyFiles\myDoc2.xls"
xlApp.Run "macroC"
xlApp.Run "macroD"
xlApp.ActiveWindow.Close SaveChanges:=False

'repeat for each workbook/macro

xlApp.Quit
Set xlApp = Nothing

All times are GMT +1. The time now is 05:17 PM.