Volume 8, Number 20 -- May 21, 2008

Use PCOMM Scripts to Dynamically Build a Spreadsheet, Part 2

Published: May 21, 2008

by Michael Sansoterra

In Part 1 of this series, I demonstrated that users of IBM's Personnel Communications can use VBScript to run a remote PC command right from the 5250 emulator window. I want to build on that short tutorial by demonstrating how to build an Excel spreadsheet right from the context of the current 5250 screen.

First of all, if you haven't read Part 1, please read it now as there are some pointers about script writing for the PCOMM emulator that developers should be aware of. It also instructs you how to get started in writing a script and how to "scrape" a value from the current 5250 emulator screen. As an aside, a reader of Part 1 inquired if this technique is accessible from an RPG program and the answer is an unfortunate "no. " These PCOMM macros can only be fired by an emulator user.

The Scenario

To demonstrate how this works, I'm going to present a simple script that:

  1. uses ActiveX Data Objects (ADO) to establish a connection to the database;
  2. queries the tried and true QIWS/QCUSTCDT sample table;
  3. leverages the Excel library to create a spreadsheet and transfer the data from the query into the spreadsheet.

I will also present a few tips for how the script can be modified to read a value from the screen so that the query can be dynamically customized to select relevant data. When your new script is finished, your users will be able to create a spreadsheet from the emulator with just a few clicks. The idea is the user will click the "play macro" button on the emulator window:

Which will in turn allow the user to select a macro to run:

Which will result in the following Excel window magically appearing for the user:

Click here to download the sample VBScript code required to create this spreadsheet.

As a reminder, please note that when you review the attached code, the following lines of the code are inserted by the PCOMM emulator for its own internal use and are not part of the VBScript code:

DESCRIPTION=Create Customer Worksheet

The lines of code following the [PCOMM SCRIPT SOURCE] section are actual lines of VBScript. This is important to understand because if you paste this code into the PCOMM emulator's macro editor you'll need to remove these lines, because (1) the editor will add the lines again and (2) the system will attempt to interpret them as VBScript!. But, if you want to paste this code into notepad and tinker with it, then you'll want to keep these lines. Otherwise, you can just save the .MAC file to your PCOMM macro folder (default is c:\program files\ibm\Client Access\Emulator\private) and edit/execute it from the PCOMM emulator.

I'll just give a boring breakdown of what major portions of the code do. An instance of Excel is started on the local machine by using the CreateObject command:

Set XL=CreateObject("Excel.Application")

A new workbook is created and variable "ws" is made to contain a reference to sheet1 (the default work sheet.) The code will use this reference later when filling up the active worksheet with data.

    Set wb = xl.Workbooks.Add
    Set ws = wb.ActiveSheet

ADO connection and recordset objects are created to talk with the DB2 database:

    Set cn=CreateObject("ADODB.Connection")
    Set rs=CreateObject("ADODB.Recordset")

In this example, I use the IBMDASQL OLE DB provider, which is included in iSeries Access starting with version V5R3, to make the connection. The "Naming Convention" property in the connection string requires the V5R4 version of the product! Don't forget you should be on the latest service pack as well! You can also use the IBM ODBC driver, IBMDA400 provider, or a third-party provider to connect to DB2 for i5/OS, but you'll have to modify the connection string accordingly. Here is my sample connection string using IBMDASQL:

    cs="Provider=IBMDASQL;Data Source=MYHOST; " & _
       "Naming Convention=1;Force Translate=65535"
    cn.Open cs, [User], [Password]

Substitute your own host name in the connection string. Unfortunately, I could not find a PCOMM API accessible to VBScript that would allow the script to automatically learn the host name from the emulator. Therefore the host name has to be stored within the connection string. If you intend to re-use an established connection so that the current user name and password are assumed, you'll want the host name in the script to match the host name used in the emulator's configuration. When calling the open method of the connection variable (cn), the user name and password are optional. If possible, for security reasons, I would recommend leaving the credentials out of a script that is easily accessible to users! Just let the new ADO connection piggyback on the existing emulator's connection.

If you intend for this script to be used by users on multiple hosts (say a logical partition) so that you can't hardcode a single host name in the script, then you may want to dynamically retrieve the default system name from iSeries Navigator. You can read how to do this in Programmatically Retrieve Defined System i Names.

Once the connection is made, we'll execute a query using the database connection (cn) and store the results in a Recordset object called rs:

    rs.Open "Select * From QIWS/QCUSTCDT", cn

Once we have the data in the Recordset object, we can slam it into an Excel sheet by using Excel's CopyFromRecordset method:

    ws.Cells(1, 1).CopyFromRecordset rs

Recall that the ws variable is actually a reference to the Sheet1 worksheet. This line simply dumps the results of the query starting in cell A1 (row 1, column 1).

There is one more issue to work with--reading values from the screen to make the query context sensitive. Say we need to limit the query to create a spreadsheet for customers in a certain state. Also, the state that the user will want to query is found on the current emulator screen in row 10, column 5. To get this information, we first need a reference to the current emulator window. This has been done for us in the sample code using the following two lines:

Set pcommPS = autECLSession.autECLPS

The pcommPS (ps stands for presentation space) variable now references the text area of the emulator window that invoked the macro/script to being with. To retrieve our desired state code from the screen at coordinate 10,5, we simply call the GetText method and tell it to give us two characters:

Dim State

Once we have the state variable we can modify our query thus to only pick one state:

rs.Open "Select * From QIWS/QCUSTCDT Where State='"+State+"'", cn

That's all there is to it. Just think of it! The bean counters can now get any number of context sensitive spreadsheets built from within an application. Customer service managers can get sales information they need for the day. And it's already in the format the users know and love: Excel.

As for making the spreadsheet attractive for users, such as putting the field names on the top row for headings, automatically sizing the columns to fit the data and the like, see Load a Spreadsheet from a DB2/400 Database: Part 2 for a past Four Hundred Guru tip covering how to do this. Also, don't forget to review the list of cautions and concerns from Part 1 of this tip on how to write these scripts. If you're feeling adventurous, it isn't too difficult to research the Excel object model and dynamically create a chart or pivot table on the newly imported data.

By using the power of VBScript and the ability to reference COM/ActiveX objects in your code, there are few limits as to how much can be accomplished! You can create or fill in a Word document, build an Access database, call a Web service, create a specialized export file, read and write to a database, or even call business logic embedded in a COM or ActiveX object built by your own company. I hope you enjoy this new productivity tool that your users will love.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Click here to contact Michael Sansoterra by email.


Use PCOMM Scripts to Execute Remote PC Commands, Part 1

Programmatically Retrieve Defined System i Names

Load a Spreadsheet from a DB2/400 Database: Part 2

Data Entry Robots

Microsoft's VBScript documentation

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot

Sponsored By

Do you need area code information?
Do you need ZIP Code information?
Do you need ZIP+4 information?
Do you need city name information?
Do you need county information?
Do you need a nearest dealer locator system?

We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

Just call us and we'll arrange for 30 days FREE use of either

WorksRight Software, Inc.
Phone: 601-856-8337
Fax: 601-856-9432
E-mail: software@worksright.com
Web site: www.worksright.com

Senior Technical Editor: Ted Holt
Technical Editor: Joe Hertvik
Contributing Technical Editors: Edwin Earley, Brian Kelly, Michael Sansoterra
Publisher and Advertising Director: Jenny Thomas
Advertising Sales Representative: Kim Reed
Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.

Sponsored Links

Aldon:  Keep your organization in line with Application Lifecycle Management solutions
COMMON:  Join us at the Focus 2008 workshop conference, October 5 - 8, in San Francisco, California
Vision Solutions:  System i Management Tips Blog - Free i5/OS Tips Each Week!


IT Jungle Store Top Book Picks

Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
Getting Started with PHP for i5/OS: List Price, $59.95
The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
The System i Pocket RPG & RPG IV Guide: List Price, $69.95
The iSeries Pocket Database Guide: List Price, $59.00
The iSeries Pocket Developers' Guide: List Price, $59.00
The iSeries Pocket SQL Guide: List Price, $59.00
The iSeries Pocket Query Guide: List Price, $49.00
The iSeries Pocket WebFacing Primer: List Price, $39.00
Migrating to WebSphere Express for iSeries: List Price, $49.00
iSeries Express Web Implementer's Guide: List Price, $59.00
Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
Getting Started with WebSphere Express for iSeries: List Price, $49.00
WebFacing Application Design and Development Guide: List Price, $55.00
Can the AS/400 Survive IBM?: List Price, $49.00
The All-Everything Machine: List Price, $29.95
Chip Wars: List Price, $29.95

The Four Hundred
The Demographics of i Sales and Shipments

The i Edition of the BladeCenter S Finally Launches

HP More Than Doubles Services Biz with EDS Acquisition

Mad Dog 21/21: Saying No No No

A Word Cloud of IBM Server Brand Names

The Linux Beacon
NYSE Euronext Trades Mainframes and Unix for Linux and X64

Canonical Founder Calls for Synchronized Linux Releases

AMD Ships Low-Power Barcelonas as Two More Execs Exit

New and Updated Barcelona Boxes Debut from Sun

VMware Tweaks Virtualization Stack, Boasts of Greenness and Sales

Four Hundred Stuff
Symantec Combats Phishing with New Services Offering

BCD Slings a New C#-Based GUI with Catapult 7.0

SkyView and Innovatum Formalize Partnership with New Product

Profound Eliminates OLTP Requirement with Web Enablement Software

140 Apps and (Hopefully) Counting for i 6.1

Big Iron
NYSE Euronext Trades Mainframes and Unix for Linux and X64

Top Mainframe Stories From Around the Web

Chats, Webinars, Seminars, Shows, and Other Happenings

System i PTF Guide
May 17, 2008: Volume 10, Number 20

May 10, 2008: Volume 10, Number 19

May 3, 2008: Volume 10, Number 18

April 26, 2008: Volume 10, Number 17

April 19, 2008: Volume 10, Number 16

April 12, 2008: Volume 10, Number 15

The Windows Observer
Microsoft Patches Zero Day Flaw in Windows

HP More Than Doubles Services Biz with EDS Acquisition

Massive Expansion in Progress at Microsoft Data Centers

Microsoft Gives Customers a Break on New SMB Windows Packages

AMD Revises Opteron Roadmaps, Pushes Out Rev Gs

The Unix Guardian
New and Updated Barcelona Boxes Debut from Sun

HP More Than Doubles Services Biz with EDS Acquisition

Java Performance Is OS Agnostic on Power6 Gear

As I See It: Soothing the Savage Programmer

VMware Tweaks Virtualization Stack, Boasts of Greenness and Sales

Four Hundred Monitor
Four Hundred Monitor's
Full iSeries Events Calendar


WorksRight Software
Guild Companies

Printer Friendly Version

Use PCOMM Scripts to Dynamically Build a Spreadsheet, Part 2

Use SQL to Strip Out Tab Characters

Admin Alert: Monitoring the Monitors

Four Hundred Guru


From the IT Jungle Forums
SNMP Traps on i5OS

Java Messages

Copying recs from a subfile to a file and keeping highlights

Imbedded SQL


Restrict *cmd to specific user

Subscription Information:
You can unsubscribe, change your email address, or sign up for any of IT Jungle's free e-newsletters through our Web site at http://www.itjungle.com/sub/subscribe.html.

Copyright © 1996-2008 Guild Companies, Inc. All Rights Reserved.
Guild Companies, Inc., 50 Park Terrace East, Suite 8F, New York, NY 10034

Privacy Statement