Link to home
Start Free TrialLog in
Avatar of Aaron Greene
Aaron GreeneFlag for United States of America

asked on

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.
AutoRunScriptMON20.vbs
Avatar of Ken Butters
Ken Butters
Flag of United States of America image

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?
http://software.invensys.com/products/wonderware/production-information-management/historian-client/

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.
Avatar of Aaron Greene

ASKER

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.
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?
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.
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?
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.
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")
objNet.SetDefaultPrinter(ReportPrinter)

'--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")
objWb.Activate
'--Print the Worksheet
objXl.ActiveSheet.PrintOut

'Set the System Default printer back to what it was before
objNet.SetDefaultPrinter(CurrentDefPrinter)
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
objXL.Quit
Set objXL = Nothing
Wscript.Quit
ASKER CERTIFIED SOLUTION
Avatar of Aaron Greene
Aaron Greene
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I used a different method to run the report.