[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

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.
  • 6
  • 3
1 Solution
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.
AaronGreene1906Author 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?
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

AaronGreene1906Author 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?
AaronGreene1906Author 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.
AaronGreene1906Author 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
AaronGreene1906Author Commented:
I was never able to get this code to work, so I decided to rewrite it.
AaronGreene1906Author Commented:
I used a different method to run the report.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now