IBM Personal Communications macro vbscript import data from excel

malya · May 2, 2018

Hi everyone,
I am a corporate employee in Central Europe. Everyday I'm doing VBA macros for Excel, or Access, which simplifies my office work. I often have to manually enter a large amount of data (even 1000 records) from the Excel table to the system using IBM Personal Communications, which takes a lot of time. Now I've done this by formatting the files using VBA on the pcomm macro format, which look like below

[sub]Description =
"60859147
[tab field]
"1
[tab field]
[tab field]
"60859146
[tab field]
"3
[tab field]
[tab field]
"60858824
[tab field]
"130
[tab field]
[tab field]
"60858826
[tab field]
"108
[tab field][/sub]

I know to use a macro file in VBscript format and then the file looks like this, but I do not know how to get variables from the xls or csv file

[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
OPTION EXPLICIT
autECLSession.SetConnectionByName(ThisSessionName)

REM This line calls the macro subroutine
subSub1_

sub subSub1_()
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "60859147"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[esc]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "1"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[esc]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "60859146"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[esc]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "3"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[esc]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "60858824"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[esc]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "130"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[esc]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "60858826"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[esc]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "108"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[esc]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "#[[[B"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[newline]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "tt"
end sub

how to look, macro vbscript to have indexes and the number downloaded from * .xls or * .csv file
Additionally, after each [tab] check if there was no message on the tap, if there is no message, skip [esc] and go to the number, if the message [esc] is present. The Excel file format looks like in the attachment.

screen with a message

screen without a message

Thank you for your help

SkipVought · May 2, 2018

Hi,

It seems that you are using a terminal emulator, perhaps a 3270 terminal emulator, to interface between you/your VBA code and the mainframe.

First, I would code my solution in Excel VBA. The Excel VBA editor is much more a Mercedes and the terminal emulator’s editor is much more a Yugo, relatively speaking.

I imagine that you have your 1000 rows of data in an Excel sheet, arranged as a table for each of the fields you need to populate. Are these the fields that are light blue on your screen?

Unfortunately, although my maternal grandparents immigrated from Hungary

in 1908, I know no Central European languages; in fact none other than English. So my ability to ubderstand your screen is close to nill.

But I have done similar things many times during my career, taking data from a table on an Excel sheet, populating a screen and sending the data to the mainframe, row after row.

Have you done much programming? Can you write code to loop through the rows of a table and the fields in a row? If you can do that, that’s where to start.

But even before that, I’d map your screen to a table like this...
[pre]
FIELD FM TH CL LN TYPE
dostarcz 01 01 70 10 NUM
dostarczone 04 04 17 02 NUM
[/pre]
...where...
[tt]
FIELD - Name of field for identification
FM - From Row
TH - Thru Row
CL - Column
LN - Length of field
TYPE - CHR or NUM ( character or numeric [including date] field)
[/tt]

This will help make your code more general. You may have other screens that you need to populate.

If you also get data from screens based on an inquiry list, you may also want to map ALL the fields on the screen and include a column to identify which ones are read only.

I’ll let you chew on this for a while. Let me know how I can help.

Skip,
[sub]
[glasses]

Just traded in my OLD subtlety...
for a NUance! [tongue]

[/sub]

mikrom · May 2, 2018

malya said:
I know to use a macro file in VBscript format and then the file looks like this, [highlight #FCE94F]but I do not know how to get variables from the xls or csv file
[/highlight]
[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
...
sub subSub1_()
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "60859147"
...

You need to modify the generated subroutine from your recorded macro in that way that instead of using the literal constant

Code:

autECLSession.autECLPS.SendKeys [highlight #FCE94F]"60859147"[/highlight]

you use a variable

Code:

autECLSession.autECLPS.SendKeys [highlight #FCE94F]my_variable[/highlight]

which you need to set before.

mikrom · May 2, 2018

Hi malya,
In the past I created some scripts from recorded PCOMM macros.
Maybe, look at my previous posts, where I tried to explain how to modify the recorded macro to create script with variables.

https://www.tek-tips.com/viewthread.cfm?qid=1721480

Here I posted an example which reads CSV line by line and for every line it calls a processing subroutine:

https://www.tek-tips.com/viewthread.cfm?qid=1676567

malya · May 9, 2018

Ladies and Gentlemen, Thank you for your understanding and time to analyze.
SkipVought, your knowledge of the motorization of past communist countries is big than my knowledge about programming. I've already written the VBA codes, I know how I referenced the sheets and the right cells in excel, and that I can do. In VBS, I can not do this and move around the screen to emulate the combination of tab keys. I know that I can move around the emmulator screen using coordinates using the commands autECLSession.autECLPS.SetCursorPos, autECLSession.autECLPS.WaitForCursor, autECLSession.autECLPS.WaitForAttrib. but I can not do it. The coordinates of the

variable -> (code) are position 18.21,
variable -> (number) is position 20.21 and
I still have to go through 22.21 and
I am going back to variable (code) 18.21

microm, using your posts and reading other examples I make two codes, first reads csv files and second xlsx

Code:

[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
OPTION EXPLICIT
autECLSession.SetConnectionByName(ThisSessionName)

sub1
Sub Sub1()


On Error Resume Next '???
Dim dostep, ark, objxls, objxlsplik, w, wrs, kod, ilosc

dostep = "C:\Users\_name_user\Documents\makra\zam.xlsx"
ark = "Arkusz1"

Set objxls = CreateObject("Excel.Application")
objxls.Visible = False
Set objxlsplik = objxls.Workbooks.Open(dostep, 0, True)
wrs = objxlsplik.Sheets(ark).Range("a1").CurrentRegion.Rows.Count

For w = 2 To wrs
  kod = objxlsplik.Sheets(ark).Cells(w, 1).Value
  ilosc = objxlsplik.Sheets(ark).Cells(w, 2).Value

   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys (kod)
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[tab ]"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[esc]"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys (ilosc)
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[tab ]"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[esc]"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[tab ]"

Next

objxlsplik.Close
objxls.Quit
Set objxlsplik = Nothing
Set objxls = Nothing
'WScript.Quit '?????? 

end sub

Code:

[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
OPTION EXPLICIT
autECLSession.SetConnectionByName(ThisSessionName)

sub1
Sub Sub1()

Const ForReading = 1

Dim fs
' jako FileSystemObject
Dim TSa
' jako TextStream

Dim arr
Dim kod
Dim ilosc

set fs = CreateObject("Scripting.FileSystemObject")

Set TSa = fs.OpenTextFile("C:\Users\name_user\Documents\makra\zam.csv",ForReading)

Do Until TSa.AtEndOfStream

arr = Split(TSa.ReadLine, ";")
kod= arr(0)
ilosc= arr(1)

  autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys (kod)
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[tab ]"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[esc]"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys (ilosc)
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[tab ]"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[esc]"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[tab ]"
Loop

Tsa.Close

end sub

I need help to use the addressing format using coordinates 18.21

and after each autECLSession.autECLPS.SendKeys "[tab ]" I want to read the message (autECLSession.autECLPS.GetText) from line 14 if it contains the text "[esc]=koniec" send the key [esc] if it is different than "[esc]=koniec" go to the next field

mikrom · May 10, 2018

malya said:
I know that I can move around the emmulator screen using coordinates using the commands autECLSession.autECLPS.SetCursorPos, autECLSession.autECLPS.WaitForCursor, autECLSession.autECLPS.WaitForAttrib. but I can not do it.
...
I need help to use the addressing format using coordinates 18.21

Hi malya,

I did not bother into depth with the different commands autECLSession.xxx.
As I wrote in [url

https://www.tek-tips.com/viewthread.cfm?qid=1676567

]my earlier post[/url], I have always done a particular task on the green screen first by hand and recorded it as a macro. Then I took the macro source and manipulated the macro generated subroutine, so it could took variables. Here I simply replaced literals (which I first entered manually) with variables. This is IMHO the most reliable approach, how to do it right.

So my advice for your case is:
1. First, on the green screen fill the field on the position 18-21 by hand and record the process as a macro.
2. After that, open the source from the recorded macro and look at the subroutine (e.g. sub subSub1_).
Then you will see how the field on the position 18-21 is addressed and you can take the code in your script.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

IBM Personal Communications macro vbscript import data from excel

malya

Technical User

SkipVought

Programmer

mikrom

Programmer

mikrom

Programmer

malya

Technical User

mikrom

Programmer

Similar threads

Part and Inventory Search

Sponsor