IBM i Access Client Solutions

 View Only
Expand all | Collapse all

Need Help Converting Excel VBA Automated Scripts from i Access for Windows to Work with i Access Client Solutions

  • 1.  Need Help Converting Excel VBA Automated Scripts from i Access for Windows to Work with i Access Client Solutions

    Posted Fri February 17, 2023 01:52 PM

    As background, we had developed a series of automated interfaces between the AS400 and iSeries Navigator (file server) using Excel VBA and the IBM PCOMM emulator. 

     Recently,  however, following upgrades of the Microsoft Office environment (including Excel) to Windows 7 (64 bit), along with upgrade to IBM I Access Client Solutions (32 bit) emulator, we unable to use the previously developed Excel VBA scripts.

    Our ask is if there is a workaround to continue to use (and develop) VBA scripting in Excel (Windows 10 and 64 bit) with the IBM I Access Client Solutions emulator (32 bit) version 1.1.9.1?

    The screenshot, below, shown the current Excel/VBA to AS400 flow using Excel/VBA 32 bit with old i Access for Windows  32 bit which has worked very well. The flow below the dotted line shows Excel/VBA to AS400 flow using Excel/VBA 64 bit with new i Access Client Solutions 32 bit desired solution which is not working.  Would it be possible to have a quick Zoon call for further clarification and guidance?
    Current and Desired Flows


    ------------------------------
    Albert Rice
    ------------------------------


  • 2.  RE: Need Help Converting Excel VBA Automated Scripts from i Access for Windows to Work with i Access Client Solutions

    IBM Champion
    Posted Fri February 17, 2023 07:50 PM
    Edited by Satid Singkorapoom Sat February 18, 2023 08:42 PM

    Dear Albert

    Do you use customized scripts in the macro?  From the reference information below, macro conversion utility in ACS's 5250 emulator no longer works with customized scripts. Please read the following Technotes to see if they help or not ?    

    IBM i Access Client Solutions 5250 Macro Scripting

    IBM i Access Client Solutions (ACS) Macro Does Not Convert Modified PC5250 Macros

    Recording Keyboard Macros in Access Client Solutions

    If this means bad news for you, I suggest you describe what you want to do in sufficient details. Someone may know another tool that can help you on this. 

    Please also be informed that there are only 58 members in this group for now. I would say you stand a better chance of getting more response or suggestion in "IBM i Global" group of this community which has about a thousand members. 



    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 3.  RE: Need Help Converting Excel VBA Automated Scripts from i Access for Windows to Work with i Access Client Solutions

    IBM Champion
    Posted Mon February 20, 2023 03:41 PM

    As I see it you have a couple choices:

    -Maintain a running copy of old Client Access/IBM Access so you can run your Macros. 
    -Purchase a copy of IBM Personal Communications (PCOMM) which should be compatible with your macros. 
    -Learn HLLAPI and do screen scraping. (This would be excruciatingly painful and a large rework most likely)
    -Re-write your macros to directly interact with the IBMi database. (More work.) 

    Unfortunately, I don't think ACS has a usable Macro mechanism. And even if it does it's not compatible with the current stuff in IBM Access/PCOMM.



    ------------------------------
    Richard Schoen
    ------------------------------

    Original Message


  • 4.  RE: Need Help Converting Excel VBA Automated Scripts from i Access for Windows to Work with i Access Client Solutions

    Posted Tue February 21, 2023 11:17 AM

    Hello Rod and Satid:

    Thanks for your feedback and recommendations..  A couple observations for your comment.  Since we have litterly hundreds of Vbs/VBA scripts in PROD impacted by the constraints of i Access Client Solutions (ACS), I will recommend to my IT team to standup a pc with access to the AS400 using the old i Access for Windows (ACW). 

    I am not sure why IT decided to upgrade to ACS, given the tremendous effort to re-write so many Vbs scripts, which were devevoped to allow seamless interaction between Excel and the AS400 (by the Financial team) involving menus and macros developed in VBA and Vbs.  Note in the screenshot, below, this financial app allows the finannce team to press a blue button which orchestrantes iteractions between Excel and AS400 and i System Navigator file server (e.g.,  logging on/off the AS400; writing Excel data to AS400 screens; downloading files from i System Navigator, etc.).  Does ACS provide this capability?  Could I see sample code (Java, etc) around ACS which can be launched from with Excel to do the same as described above?  

    I would be happy to share sample of my VBA/Vbs code which underlie the above app.

    Thanks for sticking with me here, as we have been pursuring a solution for over a year.

    Thanks



    ------------------------------
    Albert Rice
    ------------------------------

    Original Message


  • 5.  RE: Need Help Converting Excel VBA Automated Scripts from i Access for Windows to Work with i Access Client Solutions

    IBM Champion
    Posted Wed February 22, 2023 03:45 AM
    Edited by Satid Singkorapoom Wed February 22, 2023 03:58 AM

    Dear Albert

    >>>> I am not sure why IT decided to upgrade to ACS <<<<

    The reason should be that IBM terminated its support for IBM i Access for Windows a long time ago.  But if you still can run IBM i Access for Windows without any problem, there is no reason why you cannot continue using it. Just be aware that if you should encounter any problem in the future, then you are on your own and it is prudent to seek a new alternative way to create this utility beyond VBA and Vbs.

    ACS itself provides only a simple data export/import between excel file on a PC and physical file in IBM i just like before.But I have no idea what your blue button for "Ad Hoc execution" does and I do not know VBA/Vbs but I guess your utility may have to do with running 5250 sessions in the background with key stroke manipulation from your utility. 

    There have been many free and chargeable third party tools or information available that enables programmatic way to develop such data import/export utility which goes beyond VBA Vbs and can help spare you such issue in the future. For example, do a search with "excel" in this URL to find many articles relating to excel and IBM i interop :  http://www.as400pro.com/tipListInq.php?cat=WinAS400 and this URL : https://www.easy400.net/hssfcgi/documentation/index.html

    Another thing you can do is to ask your colleague who developed this utility to post a question into IBM i Global group  describing what needs to be done between IBM i and the PC client without being specific to using VBA/Vbs and ask if any one knows what tools are available to achieve the goal. 



    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 6.  RE: Need Help Converting Excel VBA Automated Scripts from i Access for Windows to Work with i Access Client Solutions

    Posted Wed February 22, 2023 08:10 AM

    we too had many many macros built for access for windows 5250 client.

    we di not have the people power to rewrite for ACS.

    we bought a few seat licenses for IBM Personal Communications (PCOMM) macros. 

    so it seems that ACS IS making some $$ for IBM.



    ------------------------------
    Bryan Dietz
    ------------------------------

    Original Message


  • 7.  RE: Need Help Converting Excel VBA Automated Scripts from i Access for Windows to Work with i Access Client Solutions

    Posted Tue December 05, 2023 12:16 PM

    HLLAPI works only when called from 32-bit applications. Do you have a workaround for 64-bit apps.



    ------------------------------
    Manimala Balakumar
    ------------------------------

    Original Message


  • 8.  RE: Need Help Converting Excel VBA Automated Scripts from i Access for Windows to Work with i Access Client Solutions

    Posted Mon December 11, 2023 05:07 PM

    Thanks Richard, let me give API a try.

    Do you think PCOMM works?



    ------------------------------
    Manimala Balakumar
    ------------------------------

    Original Message


  • 9.  RE: Need Help Converting Excel VBA Automated Scripts from i Access for Windows to Work with i Access Client Solutions

    IBM Champion
    Posted Mon December 11, 2023 05:21 PM

    Pcomm is still a 32-bit app, but worth a try. 



    ------------------------------
    Richard Schoen
    Owner/President
    MobiGoGo LLC
    Minneapolis MN
    612-315-1745
    ------------------------------

    Original Message


  • 10.  RE: Need Help Converting Excel VBA Automated Scripts from i Access for Windows to Work with i Access Client Solutions

    Posted Tue December 05, 2023 12:11 PM

    Hi Albert,

    I work on a system where Excel VBA (32-bit app) code connects to iACS emulator on a 64-bit environment using EHLLAPI32.dll API functions (Screen scraping).

    As I upgrade to Excel VBA (64-bit), current solution doesn't work. I am looking for a solution to connect to iACS emulator from Excel 64-bit.

    Please let me know if you know any leads.

    THanks

    Mala



    ------------------------------
    Manimala Balakumar
    ------------------------------

    Original Message


  • 11.  RE: Need Help Converting Excel VBA Automated Scripts from i Access for Windows to Work with i Access Client Solutions

    IBM Champion
    Posted Tue December 05, 2023 12:45 PM

    Are you using HLLAPI functions in your Excel VBA Macros ?

    I think HLLAPI is a 32-bit API that may not work directly across the 64-bit process boundaries. 

    One thing you could consider doing is creating an API that allows you to call the 32-bit HLLAPI from a 64-bit app. 



    ------------------------------
    Richard Schoen
    Owner/President
    MobiGoGo LLC
    Minneapolis MN
    612-315-1745
    ------------------------------

    Original Message


  • 12.  RE: Need Help Converting Excel VBA Automated Scripts from i Access for Windows to Work with i Access Client Solutions

    Posted Mon December 11, 2023 03:05 PM

    Hi Mala,

    It is as Richard notes below, the EHLLAPI is only compatible with 32 bit Excel. You will have to rebuild your (laptop) with 32bit Office/Excel to be able to connect using the existing API.

    IBM have committed to delivering a 64bit version by Q2 2024 as per this IBM Power Idea submission - Requesting EHLLAPI support for 64bit Office | IBM Power Ideas Portal 

    Until that is delivered you will have to maintain on a 32 bit office environment.

    Greg



    ------------------------------
    Greg Craill
    ------------------------------

    Original Message


  • 13.  RE: Need Help Converting Excel VBA Automated Scripts from i Access for Windows to Work with i Access Client Solutions

    Posted Mon December 11, 2023 05:06 PM

    Hi Greg 

    do you think PCOMM works on 64-bit apps?

    Thanks

    Mala



    ------------------------------
    Manimala Balakumar
    ------------------------------

    Original Message


  • 14.  RE: Need Help Converting Excel VBA Automated Scripts from i Access for Windows to Work with i Access Client Solutions

    Posted Mon December 18, 2023 03:16 PM

    Hi Mala,

    I cannot recall the last time I used PCOMM, I would be guessing.

    I think you need to bite the bullet and upgrade to IBMi and ACS instead of AS400 and PCOMM. Sticking with old out of support systems and software because it is hard to upgrade just exposes you to bigger issues later when things just don't work. 

    GC



    ------------------------------
    Greg Craill
    ------------------------------

    Original Message