How to take input from excel and use that value to run a macro in as400

Question

Today i am working on as400 macro, and i have a requirement to run the same macro for 3000+ records one by one. The excel sheet is containing those 3000+ lines in A column as a number format. Each time I need to run the macro and before that I need to change the AS400 macro code with the value from A column of the excel.

Below is the code of the AS400 Macro.

[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.WaitForAppAvailable

   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "dc74945"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[enter]"

   autECLSession.autECLPS.WaitForAttrib 5,28,"00","3c",3,10000

   autECLSession.autECLPS.WaitForCursor 5,29,10000

   autECLSession.autECLOIA.WaitForAppAvailable

   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[tab]"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "7"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[enter]"

   autECLSession.autECLPS.WaitForAttrib 5,24,"00","3c",3,10000

   autECLSession.autECLPS.WaitForCursor 5,25,10000

   autECLSession.autECLOIA.WaitForAppAvailable

   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "n"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[tab]"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[tab]"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[tab]"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[tab]"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[tab]"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "nnn"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[tab]"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "n"
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[enter]"
end sub

Now each time I need to change the below line by taking input from excel.

autECLSession.autECLPS.SendKeys "dc74945"

Excel contains data in below format:

ID  Name
AC71182 Andrew Cain
AC74398 Adam Cadman
AD39903 Alisha Davies (CARDIFF)
AC74952 Abby Clarke
AD54561 Alison Duffield
AD59529 Ann Rose Davies
AD60488 Alex Delmonte C'Diff COMBO

What I am looking for is an AS400 macro to read one by one cell from Column A in excel (containing ID) and then run the above macro for that ID by changing the value in the above mentioned line.

I searched a lot, but never find an easy way to do so by using AS400 Macro. I got VBA macro to do such things, but I need AS400 Macro. So requesting your help if you can.

Thanks, Maitreya

I think your problem here is that a PCOMM macro works entirely inside the PC5250 environment, i.e. has no access to Excel. Do you absolutely have to do this in PC5250? What's the business requirement? Could you not pull the Excel data into a database table and then process it via an RPG (or PHP, or whatever) program? — MandyShaw, Aug 13 '18 at 18:24
This can be done entirely in Excel VBA. You can make out-of-process COM calls to the`autECLSession` object to drive the emulator. — Mike, Aug 13 '18 at 18:28
@Mike Maybe you can make this an answer, and provide some sample code. — jmarkmurphy, Aug 13 '18 at 18:44
While the examples from [this IBM help page](https://www.ibm.com/support/knowledgecenter/SSEQ5Y_6.0.0/com.ibm.pcomm.doc/books/html/host_access08.htm) are rather short, they are in VBA / VB6 and should show you the fundamentals of connecting to the emulator from VBA. — Mike, Aug 13 '18 at 20:58

score 1 · Answer 1 · edited Apr 15 '20 at 09:11

These AS400 macros you are running in IBMs Personal Communications are just Visual Basic Scripts and you can instantiate other ActiveX-Objects inside your code, e.g. Excel.Application.

Here is a short example of such a macro. It opens an Excel sheet and loops through every row until there is no value in column A of your sheet left:

[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
[PCOMM SCRIPT SOURCE]
autECLSession.SetConnectionByName(ThisSessionName)
Dim excel
Dim row
Dim inputFilename 
Dim objWorkbook

inputFilename = "C:\...\YourExcelFile.xls"

Set excel = CreateObject("Excel.Application")
Set objWorkbook = excel.Workbooks.Open(inputFilename)
excel.visible = true 'If you want to

row = 2 'Start at row 2 of your excel sheet

'Loop until AS400 has blocked input (error in most cases) or there are no values in column A of excel sheet left
while autECLSession.autECLOIA.InputInhibited = 0 AND excel.Cells(row,1).Value <> "" 

   '### YOUR MACRO STARTS HERE
   autECLSession.autECLOIA.WaitForAppAvailable

   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys excel.Cells(row,1).Value 'Value of column A / current row from excel sheet
   autECLSession.autECLOIA.WaitForInputReady
   autECLSession.autECLPS.SendKeys "[enter]"

   autECLSession.autECLPS.WaitForAttrib 5,28,"00","3c",3,10000

   autECLSession.autECLPS.WaitForCursor 5,29,10000

   ' ...

   '### YOUR MACRO ENDS HERE

   row = row + 1
wend

excel.Quit
Set excel = Nothing

Best regards

score 0 · Answer 2 · edited May 14 '20 at 02:37

As long as you have installed IBM Personal Communications, then you can complete the coding by using Excel VBA marco, instead of VBS marco to call Excel application again.

The below link is the PCOMM library. You can start reading from page 29. https://www.ibm.com/support/knowledgecenter/SSEQ5Y_12.0.0/com.ibm.pcomm.doc/books/pdf/host_accessV120.pdf?view=kc

Let me know if you still want to have a sample code.

How to take input from excel and use that value to run a macro in as400

2 Answers2

Linked

Related