Is there a way to parse text from IBM Personal Communications into Excel?

Question

I would like to create a script in Excel to look at my IBM Personal Communications Screen or data source(preferred) and then to parse that screen text into cells.

Any ideas on where to even begin?

Example of Screen

Answer 1

I would handle this using SQL inside of Excel. This technique will not grab data from your display file. It will instead use your second option of getting it from the underlying database tables.

1. Create an .odc file with connection information to get to the IBM i DB2 database. On my PC, ODBC and OLE DB drivers were installed along with the emulator so that you can connect to the IBM i using Microsoft protocols.
2. In Excel, choose "Data, Connections". In that dialog box pick "Add" and choose your connection file. It may ask you for a specific table to select from, but you can pick whichever because we are going to change it in just a minute.
3. A new data connection is now on the list in the dialog box. Choose "Properties" to edit it.
4. Replace the autogenerated SQL on the "Definition" tab "Command Text" field with SQL that actually selects the data relevant to you from the tables you are interested in. You can get as fancy as you want with this SELECT statement: joins, CTE's, sub-queries, anything that is well-formed DB2 for i SQL.
5. Close these windows, saving changes. The connection is now independent of the original .odc source.
6. Choose "Exisiting Connections" to run the query and to have Excel populate a worksheet table with the data. If you have SQL errors, you will get them at this point when the query is actually sent to the IBM i.
7. Edit the SQL until you are happy with the data you are returning. You can also use "Refresh" to re-run it and get the latest data whenever you want.

Answer 2

You can use a VBScript macro in IBM Personal Communicagtions. Here is a sample script that will read information from the screen and then send that information to an Excel spreadsheet.

[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=Paste to Excel
[PCOMM SCRIPT SOURCE]

Dim wsh
Set wsh=CreateObject("WScript.Shell")
Dim pComm5250

' Set up Variables
Dim ReadThis
Dim strArray
Dim i

'
' Get Presentation Space of current emulator window
autECLSession.SetConnectionByName(ThisSessionName)
Set pComm5250=autECLSession.autECLPS

' Set up a Function for correcting the case of names
Function PCase(strInput)
Dim i, x, strOut, flg
flg = True
For i = 1 To Len(strInput)
  x = LCase(Mid(strInput, i, 1))
  If Not IsNumeric(x) And (x < "a" Or x > "z") Then
    flg = True
  ElseIf flg Then
    x = UCase(x)
    flg = False
  End If
  strOut = strOut & x
Next
PCase = strOut
End Function


' The first two numbers are the screen location row and column.  The third number is how many characters to read.
autECLSession.autECLOIA.WaitForInputReady
ReadThis=pComm5250.GetText(1,2,10)

' Trim any extra blank spaces to the right
ReadThis=RTrim(ReadThis)

' If the variable is numbers, you use this to replace any blank space with a "0".
ReadThis=Replace(ReadThis," ","0")

' If the variable is text you can do this to capitalize each word
ReadThis=PCase(ReadThis)

'Create the excel object
set objExcel = CreateObject("Excel.Application") 

'View the Excel program and file, set to false to hide the whole process
objExcel.Visible = True 

'Open the excel file
Set objWorkbook = objExcel.Workbooks.Open("C:\Path\ExcelFile.xlsm", , True)

'Set the cell value at row 2 column 3
objExcel.Cells(2,3).Value = (ReadThis)

Set wsh=Nothing
Set pComm5250=Nothing