×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

IBM Personal Communications macro vbscript import data from excel

IBM Personal Communications macro vbscript import data from excel

IBM Personal Communications macro vbscript import data from excel

(OP)
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

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]


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 "    "
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[esc]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "1"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "    "
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[esc]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "    "
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "60859146"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "    "
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[esc]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "3"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "    "
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[esc]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "    "
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "60858824"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "    "
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[esc]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "130"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "    "
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[esc]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "    "
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "60858826"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "    "
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[esc]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "108"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "    "
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[esc]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "    "
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      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

RE: IBM Personal Communications macro vbscript import data from excel

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...
FIELD       FM  TH  CL  LN  TYPE
dostarcz    01  01  70  10  NUM
dostarczone 04  04  17  02  NUM
 
...where...

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)


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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: IBM Personal Communications macro vbscript import data from excel

Quote (malya)


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
...
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 "60859147" 
you use a variable

CODE

autECLSession.autECLPS.SendKeys my_variable 
which you need to set before.

RE: IBM Personal Communications macro vbscript import data from excel

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=172148...
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=167656...

RE: IBM Personal Communications macro vbscript import data from excel

(OP)
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

RE: IBM Personal Communications macro vbscript import data from excel

Quote (malya)


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 my earlier post, 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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close