Use PCOMM Scripts to Dynamically Build a Spreadsheet, Part 2

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:

[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=Create Customer Worksheet
[PCOMM SCRIPT SOURCE]

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 filesibmClient AccessEmulatorprivate) 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:

autECLSession.SetConnectionByName(ThisSessionName)
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
State=pcommPS.GetText(10,5,2)

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.

RELATED STORIES AND RESOURCES

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