Automating MS-Excel with vbscript

Posted on 2014-08-04
Last Modified: 2014-11-13
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.
Question by:AaronGreene1906
    LVL 19

    Expert Comment

    by:Ken Butters
    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.

    Author Comment

    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.
    LVL 19

    Expert Comment

    by:Ken Butters
    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?

    Author Comment

    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.
    LVL 19

    Expert Comment

    by:Ken Butters
    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?

    Author Comment

    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.

    Author Comment

    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

    Accepted Solution

    I was never able to get this code to work, so I decided to rewrite it.

    Author Closing Comment

    I used a different method to run the report.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    This script will sweep a range of IP addresses (class c only, and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
    Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
    This video discusses moving either the default database or any database to a new volume.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now