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

25 August 2009

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 scheduled basis (7 am 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 an 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 = FalseSet 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.

So allowing for parameters the VBS look something like this:

‘Arguments’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

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 daily.

Categories: Articles
<a href="https://blog.idx.co.za/author/bruce/" target="_self">Bruce Bean</a>

Bruce Bean

Academy and Site Services Manager

Bruce has over thirty years of experience in the consulting, information technology, project management and software development industries. His hobbies include karate, pilates, rebounding, piano and playing pool.

Show your love!

Recent Posts

Driving Productivity with LineView Training

In light of our partnership with LineView Solutions, our team has taken up the opportunity to equip themselves with insights into the LineView system and its impact on the industry. “LineView is a production line monitoring and recording system that automatically...

Exciting New Launch: Onsite ProfiDay

IDX is excited to announce the launch of our onsite ProfiDay offering. This is available to all industries and companies that utilise PROFIBUS and/or PROFINET within their industrial communications systems. What is ProfiDAY? An exclusive onsite program offering...

Introducing our Partnership with LineView Solutions

We are excited to announce our partnership with LineView Solutions, a leading UK-based company specialising in smart factory solutions for optimising production processes. LineView developed a cutting-edge software suite designed to automatically gather data from the...

Optimise PROFIBUS and PROFINET Network Performance

As a field technician handling PROFIBUS and PROFINET systems, it is crucial to recognise the common errors that can result in costly downtime. From improper network configuration to neglecting regular maintenance, these mistakes can greatly affect system performance...

Delighted Customer Shares Experience

We recently assisted a client in rectifying damage to the Anybus ABC4090 Communicator caused by incorrect power usage onsite. We arranged for the device to be sent to HMS in Sweden for repairs, and they restored it before promptly returning it to us. Here are some...

Price Decrease on PROFIBUS Connectors

PROFIBUS connectors play a crucial role in industrial automation and data exchange systems by facilitating smooth communication between devices and networks. We are pleased to announce a significant price decrease on all our PROFIBUS connectors. Thanks to recent...

PROFIBUS Standards: Functional bonding and shielding

Electromagnetic Interference (EMI) and earthing issues can significantly impact the performance of your network. By following these six proven recommendations, you can ensure that your network is always up and running.

PROFIBUS Standards: Terminations

What is the Volts Direct Current (VDC) required for optimal operation of a PROFIBUS DP termination circuit? Get the answers you need in our latest video on PROFIBUS Standards: Terminations. Don't miss out on this essential knowledge to boost your system's performance!

PROFIBUS Standards: Segment Cable Lengths

Finding the perfect balance between PROFIBUS cable length and network speed is crucial. While longer cable segments might seem tempting, they can lead to signal degradation and slower speeds. Conversely, too short segments might limit your layout options. Striking the...

Sign up for our newsletter

Get exclusive updates and a sneak peek into our world every two months. Stay connected, stay informed.

Related Posts

Our expertise lies in establishing, maintaining, and leveraging plant data for business benefit. Through our innovative solutions, we work alongside you to streamline processes, enhance efficiency, and reduce costly downtime.

CONTACT US

Main: +27 (11) 548 9960
Sales: +27 (11) 548 9970

SOCIAL MEDIA

Copyright @2024 Industrial Data Xchange. All rights reserved.