Calling excel-macros from script?

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
Tue May 03 06:56:06 CDT 2005

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" <grz01@spray.se> wrote in message
news:1115120379.158349.38900@z14g2000cwz.googlegroups.com...
> 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
>

Bob
Tue May 03 08:52:52 CDT 2005

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" <grz01@spray.se> wrote in message
news:1115120379.158349.38900@z14g2000cwz.googlegroups.com...
> 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
>

Hal
Tue May 03 12:27:37 CDT 2005

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" <grz01@spray.se> wrote in message
news:1115120379.158349.38900@z14g2000cwz.googlegroups.com...
> 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
>

grz01
Wed May 04 17:11:32 CDT 2005

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