How do I call my VBA Macros outside of Excel ? 2

[ljsmith91]

I am a beginner and have created some Excel VBA macros that input some raw data and chart it etc etc. I know I can invoke the macros inside Excel a number of different ways. However, I was wondering if there is a way to invoke the macro through a VBScript outside of Excel.

I am trying to completely automate the conversion of the raw data without the need to access Excel and interact with the macro which recquires no user input.

This may be simple but I have no idea how to go about it. I hope its doable. Thanks for any suggestions.

LJS

 

[SkipVought]

Hi,

VBA is encapsulated within the APPLICATION where the project is stored. The APPLICATION must be active. Hence the name, Visual BASIC for Applications -- as opposed to Visual BASIC.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ljsmith91]

Skip,

Thanks for the bad news. So, there is no way to programmatically call Excel and open the app, then call the VBA macro, and then exit, check return codes and return to the script for processing ? That is a bummer. It's hard to believe that this must be done manually in this day and age.

LJS

 

[SkipVought]

Yes, you can do that with VB. Not sure how that could be done with VB Script.

Check the Microsoft.com knowledgebase

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

ljsmith91, there is a forum Forum329 devoted to VBScript.
You may try something like this:
Set W=GetObject("\path\to\workbook.xls")
W.Application.Run "YourMacro", Arg1, Arg2, ...
W.Close
Or something like this:
Set X=CreateObject("Excel.Application")
Set W=X.Workbooks.Open("\path\to\workbook.xls")
X.Run "YourMacro", Arg1, Arg2, ...
W.Close
X.Quit

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SlakeB]

hey PHV, I used the code below and it worked great. Do you know how to stop the "Do you want to save changes" window from popping up?

Dim X

Set X=CreateObject("Excel.Application")
Set W=X.Workbooks.Open("D:\Docs\JaydipDB\OutputFiles\testExcelMacros.xls")
X.Run "macTest1"
X.Run "macTest2"
X.Quit

I tried adding "X.Save" before "X.QUIT" but then I got another pop up window asking if I wanted to replace "RESUME.XLW"? Have you encountered this before? The goal is no pop ups.

 

[SkipVought]

x.displayalerts=false

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]