Building Reports in Excel using IBM PCOMM Automation API
IBM Personal Communications provides an emulator interface to communicate with IBM Mainframe/AS400/VT sessions. This is used for reading & updating host data and interfacing host with other applications. Performing manual tasks on emulator screen increases in transaction and becomes repetitive, this over a long term would have issues:
- Redundant and hence error prone
- Time consuming and thus expensive
Consider a business scenario where bank employees, on a regular basis, build reports on their customer’s financial information by querying their credit limit, balance due etc. This report building operation involves below set of activities:
a. Execute macros on the emulator screen to perform certain tasks.
b. Query database to read customers’ credit and balance amount.
c. Generate the final report by exporting query results to a text or CSV file.
Performing manual operations to generate such reports is inefficient and prone to errors. This is where automation, in the form of API’s can help.
Exploring IBM PCOMM Automation API
In this post we will look at a sample application that uses PCOMM Automation API’s in a Microsoft Excel sheet. This application can start multiple PCOMM sessions or connect to the existing ones. On a selected session it can execute PCOMM macros (.mac) and extract information from the emulator screen to generate a user report.
This application is divided into two sections:
A. Session Manager:
a. Use ‘Connect Session’ dropdown list to start a new emulator session or connect to an existing one.
b. Use ‘Stop Session’ to gracefully terminate any active session which was started using this application.
B. Generate Report:
a. Use ‘Select Session’ to connect to a session started using above steps.
b. Select macro (.mac) from ‘Table Definition’ list which will log into the Host session and move to the table definition page.
c. Select macro (.mac) from ‘Query Window’ list to move to table query page.
d. Click on ‘Generate report’ to collect Customers information from the Emulator page.
A. Starting PCOMM Session: The PCOMM session can be launched by couple of methods:
a. Using PCSAPI, ‘pcsStartSession’:
b. Using HACL API, ‘autECLConnMgr.StartConnection’:
Once the session is started, use PCOMM HACL API to connect to it:
B. Stopping PCOMM Session: The session started above can be stopped using either of the methods below:
a. Using PCSAPI, ‘pcsStopSession’:
b. Using HACL API, ‘autECLConnMgr.StopConnection’:
C. PCOMM Session Management: In this application, user can select any session to generate the report. To manage these sessions, we should associate each session with its PCOMM connection objects. To do so, use Dictionary objects to store session name as a key and session objects as its value.
In an array store the newly created autECLPS and autECLOIA objects. As shown below, add session name (A-Z, a-z) as a Key to the dictionary and the array as its value:
Once user selects a session to generate a report, get the PCOMM Session objects from the dictionary ‘SessionObjsDict’ by providing ‘sessionName’ as a key (described in section below).
A. In this section, user generates a report by running macros on the Host and performs query on the host table using Excel User Form. Once user selects a session under ‘Select Session’, application retrieves the session objects from the ‘SessionObjsDict’ dictionary:
B. To execute any macro (.mac) file in the Host presentation space, use autECLPS API ‘StartMacro’:
C. User Form ‘Generate Report’ displays the column list of a table which has been read from a table inside the Emulator screen. In this User Form, user can select the column which they want to view in the final report and can add filters on the columns ‘Balance Due’ (BALDUE) or ‘Credit Due’ (CDTDUE).
By using PCOMM HACL API’s, the data is read from the Emulator presentation space and is stored in the Excel sheet.
For more information on PCOMM Automation API, please refer to PCOMM documentation.
Below is link to a video that shows a demo of this application.
Please email [email protected] or [email protected] to get more information on Services offerings.
Technical Architect, Lab Services, IBM HACP & HATS
5/10/2018 12:06:30 pm
A very useful blog that describes a powerful feature in IBM Personal Communications
12/1/2018 03:41:28 am
6/24/2020 01:40:51 am
The software issues and making the wonderful server topics always, the software methods and mailing info as well. We all are using the site for interesting techniques with us.
8/18/2020 05:00:18 am
Please provide me the excel, it’s so useful and important.
Leave a Reply.
Excel, from Microsoft, is a powerful spreadsheet application for Mac, Windows, and OS/2. Excel was first released for the Mac. When it was ported to Windows 2.x, they started at version '2.0' to one up current Mac version. There was never a DOS version. Instead, DOS and 8-bit platforms used the older Microsoft Multiplan. Excel was later bundled as part of Microsoft Office
Sketchup pvc pipe. Based on concepts from the granddaddy of all spreadsheets, VisiCalc, some of the spreadsheets it competed against and ultimately conquered include: Lotus 1-2-3, Quattro Pro, The Twin, VP-Planner, Ashton Tate Full Impact, Boeing Calc, Surpass Software System's Surpass Spreadsheet, SuperCalc, PlanPerfect and the German StarCalc (Today part of LibreOffice ).
Also see WinWorld's complete list of preserved spreadsheet products
Applies to: Excel for Mac PowerPoint for Mac Word for Mac Office 2016 for Mac. If you are authoring Macros for Office for Mac, you can use most of the same objects that are available in VBA for Office. For information about VBA for Excel, PowerPoint, and Word, see the following: Excel VBA reference; PowerPoint VBA reference; Word VBA. Now you can create a new virtual machine for Mac, including a Windows VM on Mac. Get Windows from Microsoft or install freely available operating systems such as Ubuntu, Fedora, or other Linux systems supported by Parallels Desktop.
Microsoft Excel 2010 Free Download
How To Install Excel For Free
Excel Emulator For Mac
Excel 2.0 is the very first version of Excel for the PC. Version 1.x was only available for the Mac. Excel 2.x for Windows runs under Microsoft Windows 2 or a Windows 2 runtime. Excel 2.0 for Windows was released on both 3.5' 720k and 5.25' 1.2mb floppy disk images.