Calling excel-macros from script?

[grz01]

Hi, need a little help here:

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]

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

 

[Bob Phillips]

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

 

[Hal]

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

 

[Hal]

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]

Thanks Bob,

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