Automating MS-Excel with vbscript

I have inherited a set of reports that are generated from Microsoft Excel workbooks.  Each workbook is executed triggered by a .vbs file at certain points of the day.  I am fairly familiar with the vba in the workbook, but I don't know that much about vbscript files.  I would like to have someone review the .vbs file that I have uploaded with this question to tell me if there are problems.  I would like to eliminate the vbscript file as the problem, so I can concentrate on troubleshooting the excel workbook.
Aaron GreeneProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ken ButtersCommented:
What sort of problems are you looking for?

Just reviewing the code, it looks like the purpose of the vbs script is basically to open the spreadsheet and print it.

It is also doing something with an excel add-in?
objXL.Workbooks("HistClient.xla").RunAutoMacros 1

based on the name... looks like that add-in might belong to this company?

A description of the problem you are experiencing would go a long ways to help in figuring out what is going wrong in your process.
Aaron GreeneProgrammerAuthor Commented:
You assumptions are correct.  The script opens the spreadsheet, verifies that the add-in is loaded properly, then executes a procedure in the spreadsheet.  The procedure performs a calculation and then exports a copy of a worksheet as a stand alone file.  Then the report saves and closes itself.  I have verified that the add-in is located where it is supposed to be, but the report continues to fail.
Ken ButtersCommented:
When you say the report fails.... what happens?   Do you get any sort of error, or do you just not see a report being generated?
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Aaron GreeneProgrammerAuthor Commented:
I don't think that the procedures are being called.  The report is ran at midnight, and in the mornings it has not been printed.  My thought is that this line:

'Perform the CT Calculations (call macro in report template)
objXL.Run "AutoRunCTCalcs"

 is not being executed.
Ken ButtersCommented:
objXL.Run "AutoRunCTCalcs" --- does not exist in the vbscript that you posted in your original post.

That aside... Do you know for sure that the job is kicked off in the first place?
Aaron GreeneProgrammerAuthor Commented:
Yes.  The vbscript is launched from a .bat file that also creates a log when it is complete, so I am confident that the job is being launched.
Aaron GreeneProgrammerAuthor Commented:
I see that I did post the wrong vbscript file.  This is the correct one.

Option Explicit
Const vbNormal = 1         ' window style

DIM wshShell, EnvVar, CurrentDefPrinter, ReportPrinter
'Create a new Windows Script Host Shell Object
Set wshShell = CreateObject("Wscript.Shell")
'Set it to read the env variables
Set EnvVar = wshShell.Environment("Process")
ReportPrinter = EnvVar.Item("AF11x17Printer")

DIM objXL, objWb, objR, objNet     ' Excel object variables
DIM file, st, gfuncs, installpath
file = "\\ESTEL\WTPReports\Templates\Monthly\MON10 - Primary Disinfection.xls"

'--Determine the install path of ActiveFactory
Set gfuncs = CreateObject("ArchestrA.HistClient.Util.aaHistClientGlobalFunctions")
installpath = gfuncs.getinstallpath
Set gfuncs = Nothing

'--Start Excel and run it visibly
Set objXL = WScript.CreateObject("Excel.Application")
objXL.Visible = False
objXL.DisplayAlerts = False

'Get current default printer settings and save them in order to reapply them after the script has finished.
CurrentDefPrinter = Left(objXL.Application.ActivePrinter,Len(objXL.Application.ActivePrinter)-(Len(objXL.Application.ActivePrinter)-InStr(objXL.Application.ActivePrinter,"on"))-2)

'--Set the system default printer to the desired report's printer
Set objNet =WScript.CreateObject("WScript.Network")

'--Ensure the ActiveFactory Workbook add-in is loaded and ensure the auto macros will run
st = InstallPath & "\HistClient.xla"
Set objwb = objXL.Workbooks.Open(st)
objXL.Workbooks("HistClient.xla").RunAutoMacros 1

'-- Open the file
Set objWb = objXl.WorkBooks.Open(file)

'Perform the CT Calculations (call macro in report template)
objXL.Run "AutoRunCTCalcs"

Set objWb = objXL.ActiveWorkBook.WorkSheets("Report")
'--Print the Worksheet

'Set the System Default printer back to what it was before
Set objNet = Nothing

'Save a Finished Copy of the Workbook (call macro in report template)
objXL.Workbooks("MON10 - Primary Disinfection.xls").Activate
objXL.Run "AutoRunSaveAndClose"

'--Close the workbook and Excel
objXL.ActiveWorkbook.Close False
Set objwb = Nothing
Set objXL = Nothing
Aaron GreeneProgrammerAuthor Commented:
I was never able to get this code to work, so I decided to rewrite it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aaron GreeneProgrammerAuthor Commented:
I used a different method to run the report.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.