How to get AS/400 macro to pull data from Excel

35 pts.
AS/400 macros
Microsoft Excel
Hello I’m trying to create a macro on the AS/400 that will pull data from an Excel sheet and input data to the screen. I know how to create a macro to run the menu option by using the record macro button but not sure how to get it to access the excel file so it will run through the list.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi could you please information how you figure it out.

I want to write a macro which has to execute AS/400 commands and input the AS/400 screen through data present in EXCEL.
Could you please help on this.

Discuss This Question: 5  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    I never tried what it sounds like you want. I'm not even sure its possible. If I under stand you want a macro (may have to be a button that runs a VB script) to get data from an Excel file and then fill in fields on an AS/400 screen? How many rows in the Excel an how do you see on mapping the cell to the screen field? Sound like a LOT of work. What is the business logic behind the request? It may help us to understand the request.
    136,240 pointsBadges:
  • hugotap
    10 pointsBadges:
  • actuarygirl93
    can we pull data from As400 ACS without using OCDC?
    10 pointsBadges:
  • TheRealRaven
    @actuarygirl93 : Please clarify what you mean by pulling data from "ACS". Do you want to use ACS to pull data from an "As400"? Do you want to pull data without using ACS? Are you thinking you'd pull data from ACS itself somehow?
    36,880 pointsBadges:
  • actuarygirl93
    In April 2019, IBM will stop supporting the current version of AS 400 because, among other things, it will not be compatible with Windows 10, SO that companies will be installing almost mandatory due to the loss of support from Microsoft. The case is that IBM will provide a new version of AS 400, compatible with Windows 10 and with a very similar interface but that will use a completely different language with regard to macros.

    Here the problems come: the language will no longer be the one spoken of in other posts but it will be HAScript, a multiplatform language based on XML. That means that the macros that are used now will stop working. IBM provides software for translating old macros into the new language but it turns out that if they have been modified, it will not be able to help. Of course, if we want a macro recorded as is, that will continue to work either by recording it with the updated program or translating it with the software. 

    In my case, all my macros take data from an Excel and insert data into AS 400 or, conversely, take AS 400 to extract Excel. 

    I give you an example of what it was before. Header ... 

    Sub Insert_Msg () 
    Set autECLSession = CreateObject ("pcomm.auteclsession")
    Set autECLPS = CreateObject ("Pcomm.auteclps") 
    Set autECLOIA = CreateObject ("Pcomm.autecloia") 
    autECLSession.SetConnectionByName ("A") 
    Dim strSend As String 
    Dim I 
    Dim val

    the new macro applied in AS400 is HAscript which is you can see the example below : 

    <HAScript name="apa apa.mac" description="" timeout="60000" pausetime="300" promptall="true" blockinput="true" author="" creationdate="Jul 17, 2019 2:30:46 PM" supressclearevents="false" usevars="false" ignorepauseforenhancedtn="true" delayifnotenhancedtn="0" ignorepausetimeforenhancedtn="true" continueontimeout="false">

        <screen name="Screen1" entryscreen="true" exitscreen="false" transient="false">
            <description >
                <oia status="NOTINHIBITED" optional="false" invertmatch="false" />
                <numfields number="37" optional="true" invertmatch="false" />
                <numinputfields number="5" optional="true" invertmatch="false" />
                <input value="fuat14[tab]" row="0" col="0" movecursor="true" xlatehostkeys="true" encrypted="false" />
                <input value="4l9kzJCU4kDPA0fDka5nmA==" row="0" col="0" movecursor="true" xlatehostkeys="true" encrypted="true" />
            <nextscreens timeout="0" >
                <nextscreen name="Screen2" />

    My question is does anyone know - or have an idea - if there is a way to "connect" VBA to this language and keep communicating through Macros VBA to AS 400? 

    i hope you get my point @TheRealRaven. Thank you so much for your respond. :)

    10 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: