5 Replies Latest reply on Jul 13, 2010 3:02 PM by wpurrer

    Scheduling the printing of an Excel file which has an add-in from the command line using visual basic scripting (vbs) via wscript

    bruce.bean

      On a recent project I was required to print out an Excel based report which made extensive use of OSIsoft PI DataLink's Excel Add-in on a sechduled basis (7am daily).

      Unfortunately Excel does not easily allow for the printing of an Excel file via the command line via switches or parameters when calling Excel.exe (e.g. C:\Windows\MS Office\Excel.exe -print "C:\Reports\My Report.xls").

      I was able to find examples on the Internet which made use of cscript or wscript where one calls VBS code, and where one provides the file name as argument, for example:

      wscript "C:\Data\My Scripts\printXLS.vbs" "C:\Data\My Reports\Daily Analyser Excursion Report.xls"

      Note the convention of using "" around full path filenames as they contain spaces.

      The file printXLS.vbs would contain something akin to the following code:

      Dim XLApp
      Dim XLWkbk
      Dim ObjArgs

      set ObjArgs = wscript.arguments
      if ObjArgs.count <> 1 then
      wscript.echo "Invalid passed arguments"
      wscript.quit
      end if

      Set XLApp = CreateObject("Excel.Application")
      XLApp.Visible = False

      Set XLWkbk = XLApp.Workbooks.Open(objargs(0))
      XLWkbk.PrintOut
      XLWkbk.Close False

      XLApp.Quit

      Set XLWkbk = Nothing
      Set XLApp = Nothing
      Set ObjArgs = nothing

      The problem I experienced was Excel Add-ins are not automatically loaded in Excel applications created via Automation. This resulted in the Excel file printing but with OSIsoft PI DataLink Excel Add-in functions not being recognised and #NAME! appearing in the relevant cells on my printout.

      The solution I found was to explicitly load the Excel Add-in via XLApp.RegisterXLL ("fullpath filename")

      So my VBS file looked like something this:

      Dim XLApp
      Dim XLWkbk

      Set XLApp= CreateObject("excel.application")

      XLApp.Visible = False
      Set XLWkbk = XLApp.Workbooks.Open("C:\OSIsoft\Reports\Scheduled\PI Report - Analyzers - Quality Excursions.xls")
      XLApp.RegisterXLL ("C:\Program Files\PIPC\Excel\pipc32.xll")

      XLWkbk.Worksheets("Report").Calculate
      XLWkbk.PrintOut
      XLWkbk.Close False

      XLApp.Quit

      Set XLWkbk = Nothing
      Set XLApp = Nothing

      I also wanted to be able to specify the number of copies to print and which printer should be used namely PRC2 in my case. If the printer is not specified then the windows default printer is used.

      I also decided to allow one to specify the Excel workbook and which specific worksheet in the Excel workbook to be printed.

      Parameters defined:

      1 Excel Name e.g. "C:\OSIsoft\Reports\Scheduled\PI Report - Analyzers - Quality Excursions.xls"'
      2 Worksheet Name e.g. "Report"'
      3 Printer Name e.g. "PRC2"'
      4 No of copies e.g. 1

      C:\Data\My Scripts\printXLS.vbs "C:\OSIsoft\Reports\Scheduled\PI Report - Analyzers - Quality Excursions.xls", "Report", "PRC2", 1

       

      So allowing for parameters the VBS look something like this:

      Dim XLApp
      Dim XLWkbk
      Dim ObjArgs
      Dim strFileName
      Dim strWorkSheetName
      Dim strPrinter
      Dim intCopies

      Set ObjArgs = wscript.arguments
      If objArgs.count <> 4 Then
      wscript.echo "Invalid Passed Arguments"
      wscript.quit
      End If

      strFileName = objargs(0)
      strWorkSheetName = objargs(1)
      strPrinter = objargs(2)
      intCopies = objargs(3)

      Set XLApp= CreateObject("Excel.Application")
      XLApp.Visible = False
      Set XLWkbk = XLApp.Workbooks.Open(strFileName)
      XLApp.RegisterXLL ("C:\Program Files\PIPC\Excel\pipc32.xll")
      XLWkbk.Worksheets(strWorkSheetName).Calculate
      XLWkbk.PrintOut , , intCopies, , strPrinter
      XLWkbk.Close False
      XLApp.Quit

      Set XLWkbk = Nothing
      Set XLApp = Nothing
      Set ObjArgs = Nothing

      I was then able to use Windows Task Scheduler to call the VBS file to print my Excel files on a daily basis.