IBM PCOMM Macro – working with MS Excel
IBM Personnel Communications (PCOM) is a host communication and terminal emulation package for Microsoft Windows (both 32 and true 64-bit OS). It supports IBM System Z (3270 mainframes) and IBM System I (5250 AS/400) systems.
PCOMM has features like font scaling, wide-screen display & high resolution, new-age visual style, keyboard, color remap, and other customizations, and powerful APIs to support automation. It allows the user to record a macro in the Host screen or use the automation APIs to enable the automation.
A PCOMM recorded macro (.mac file) is a sequence of keyboard and host commands that a single action, such as a keystroke. Using macros you can read data from the screen, write data into the screen, automatic screen traversal, reduce human data entry error, etc.
In this document, we understand how to use PCOMM macro to import data from Excel Sheet to the host screen and export data from the Host screen to Excel Sheet.
- Get Cust Number from an excel sheet and place it in the blue highlighted area of the Emulator screen as displayed above.
- Retrieve Customer details, type ‘1’ in the ‘Select option’ and press ENTER.
- Read the Customer details from the screen and copy them to the Excel sheet.
To achieve this automation, perform the below sequence of tasks using the PCOMM macro:
- Record macro to reach to above Host screen.
- Open an Excel file and read the cell value indicating Cust Number.
- Send this Cust Number in the Host screen and initiate ‘inquiry’.
- Extract the response from the above red highlighted area of the host screen and pass it to the Excel sheet.
To perform screen-scraping from the screen, we use a few HACL APIs like SetText, SendKeys, GetText, etc.
The SendKeys method sends a string of keys to the presentation space for the connection associated with the autECLPS object. This method allows you to send mnemonic keystrokes to the presentation space.
The SetText method sends a string to the presentation space for the connection associated with the autECLPS object. Although this method is like the SendKeys method, this method does not send mnemonic keystrokes (for example, [enter] or [pf1]).
We use SetText to write data read from the excel sheet to the host screen and GetText to retrieve the data from the host screen. Using Excel APIs, we send data to the excel sheet and save the updated excel.
Import and export data from excel sheet to host screen
Step-1: Launch the PCOMM Application
Step-2: Go to actions-> Start Recording macro, from the toolbar click Record macro.
Step-3: Record the screen navigation, perform the applicable commands, and reach the page where you do transactions with Excel.
Step-4: Stop and Save the macro.
Step-5: Open the macro in a file editor to add the HACL script.
Step-6: Update the macro with changes as below to initialize a few Excel application objects:
REM Initialize Excel Application objects to connect with the Excel Sheet:
Dim ExcelApp, CustInfoSheet, CustInfo,PSText
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True
Set CustInfoSheet=ExcelApp.Workbooks.Open("C:\Users\ashok.erla\AppData\Roaming\IBM\Personal Communications\CustInfoSheet.xlsm")
Step-7: Read customer ID from the cell (3,3) and paste into the host screen at location 4th row and 60th col and select option “1” on the host screen.
autECLSession.autECLPS.SetText CustInfo.Cells(3,3).Value, 4, 60
autECLSession.autECLPS.SetText "1", 22, 25
The above script execution display details of the Customer with ID ‘1’:
Step-8: Read the values from the Emulator screen using HACL API GetText and place the value in the Excel sheet.
REM Read values from host screen to Excel sheet using GetText
PSText = autECLSession.autECLPS.GetText(5,51,30)
CustInfo.cells(4,3). Value = PSText
The below-highlighted field value of the Cust name from the host screen gets exported to the excel sheet from the below screen.
Step-9: Similarly, read rest of the values from the screen and send them to the Excel sheet (as shown in the image above).
Step-10: Save the changes and run the macro from the PCOMM session.
We can achieve simple to complex automation using IBM PCOMM macro with the combination of automation APIs. As seen above, using a few standard APIs, we can screen scrape data from the Host screen and send it to Excel and vice-versa. Not only does it automate repetitive tasks but also reduces data entry errors.
IBM PCOMM: www.ibm.com/docs/en/personal-communications/14.0?topic=product-documentation
PCOMM Macro: www.ibm.com/docs/en/personal-communications/142.0?topic=use-creating-macro
HACL Automation API:
For further information on Automation, Services offerings, or technical details in IBM HACP/HATS, please contact the HCL mainframe Lab services.
Mainframe Lab Services