Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.activex,microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.scripting.vbscript,microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.activex,microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.scripting.vbscript
|
|||
|
|||
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 |
#3
Posted to microsoft.public.activex,microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.scripting.vbscript
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.activex,microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.scripting.vbscript,microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.activex,microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.scripting.vbscript
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calling a macro from ActiveX Script. | Excel Programming | |||
Calling Excel 4 Macros From VBA | Excel Programming | |||
Calling Macros From VB6 | Excel Programming | |||
Calling macros from a VB app | Excel Programming | |||
Calling Excel FUNCTION MACROS Programmatically from VB | Excel Programming |