Calling excel-macros from script?

  • Thread starter Thread starter grz01
  • Start date Start date
G

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
 
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
 
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
 
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
 
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
 
Thanks Bob,

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