Trouble Using .vbs to run Excel VBA Subs

[SDJ98]

Hi,

I've been trying to automate an excel file to run overnight. The file basically loops through 50 states, refreshing the data for each state via Essbase and saving the file off on the directory.

To give the lay of the land a litlte bit, I have two modules:
"mdlEssbase" contains all the Essbase connect/retrieve logic. I'm pretty sure it's a relatively standard code block that you can find on the internet.
"Module1" contains my two main subs, "SaveFile" and "Retrieval". Save File has the loop and save.as logic, Retrieval specifies all the ranges to be pulled and does the hide/unhide tab type stuff.

I came across the idea of using Windows Task Scheduler to launch a .vbs file that would in turn run my excel workbook. The VBS is below:

Set fso = CreateObject("Scripting.FileSystemObject")

Set myxlApplication = CreateObject("Excel.Application")
myxlApplication.Visible = True
Set myWorkBook = myxlApplication.Workbooks.Open("Path & File Name.xlsm")
myWorkBook.Application.Run "Module1.SaveFile"
myxlApplication.Quit

The sub I'm targeting is "SaveFile", which looks like this:

Sub SaveFile()
Dim thefile, thepath As String
Dim State As String
Dim RngStates As Range

Set RngStates = ActiveWorkbook.Worksheets("Control").Range("$R$7:$R$57")
Call mdlEssbase.connectMe("R")
'Begin Loop
For Each cell In RngStates
ActiveWorkbook.Worksheets("Control").Range("C6") = cell.Value
State = Worksheets("Control").Range("C6")
Call Retrieval

Let thepath = "path goes here"
Let thefile = "filename goes here.xlsm"
ActiveWorkbook.SaveAs Filename:=thepath & thefile _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Next cell

MsgBox ("All States Run")

End Sub

and a little piece of Retrieval looks like this (there are many tabs and ranges to pull):

Sub Retrieval()
   
Sheets("PI_SF_NBAWP").Visible = True
   
Worksheets("PI_SF_NBAWP").Activate
    Range("SF_NB_AWP_Pull").Select
    Call mdlEssbase.EssbaseRetrieve
    Range("SF_NB_Leakage_Pull").Select
    Call mdlEssbase.EssbaseRetrieve
    Range("A1").Select

My issue is that when using the .vbs script to run the process, the connection to essbase isn't happening. It will loop through all the other commands, like selecting a range or hiding/unhiding a sheet and saving, etc, just not the stuff contained in mdlEssbase module. However, if I just run the SaveFile sub(launching from excel, not using the vbs), it'll all work correctly.

I'm not sure what my issue is - any thoughts? Thanks!

 

[RoryA]

Since you didn't post any of the Essbase code, I'll simply speculate that either it requires user credentials that aren't correct when using task scheduler, or it require an add-in, which will not be loaded when you start Excel using CreateObject. Hopefully one of those ideas will assist.

 

[SDJ98]

Ahhhhh, thank you. I think that is the issue.

I didn't post the code because it is rather long, but it references an essbase add-in that doesn't appear to be loading with the createobject method.

 

[RoryA]

You can explicitly load it in your VBS by toggling its Installed property to False and then back to True.

 

[SDJ98]

After your first reponse, I did some more exploring and actually ran across an old solution of yours:

http://www.mrexcel.com/forum/excel-questions/827009-loading-com-addin-vbscript.html

Just figured i'd include the link to the solution that worked for me if anyone else stumbled across this thread. Thanks again for your help.