ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

IBM i Access Client Solutions

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • IBM i Access Client Solutions

    For years people have had macros running on ExCel VBA that connect to the old green screen and do screen scraping style macro processing. Sometimes reading data from the IBM i and writing to the spreadsheet, and sometimes taking input data froma spreadsheet and making updates to suit on the IBM i.

    Existing stuff is done via this interface http://www-01.ibm.com/support/knowle...t_access08.htm

    That's all understood and working funky like, then along comes IBM i Client Access Solutions, the new thing running java. While there are macros under an XML format that can be recorded etc, I am struggling to find any documentation on integrating these with VBA for Excel.

    Anyone else looked at this?

    GC
    Last edited by gcraill; October 4, 2016, 09:59 PM.
    Greg Craill: "Life's hard - Get a helmet !!"

  • #2
    Re: IBM i Client Access Solutions

    The old IBM i Access ("Client Access") supported something called HLLAPI (or the enhanced version, EHLLAPI). This is not supported by ACS, and frankly, is a technology that is dying and should be replaced with better solutions.

    Is it possible that you VBA macros rely on this technology?

    Comment


    • #3
      Re: IBM i Access Client Solutions

      Heya Scott, not sure about HLLAPI - all I know from the IBM manual is ...

      The Host Access Class Library Automation Objects allow the Personal Communications product to support Microsoft® COM-based automation technology (formerly known as OLE automation). The ECL Automation Objects are a series of automation servers that allow automation controllers, for example, Microsoft Visual Basic, to programmatically access Personal Communications data and functionality.
      Last edited by gcraill; October 4, 2016, 10:02 PM.
      Greg Craill: "Life's hard - Get a helmet !!"

      Comment


      • #4
        [Update] Heya Scott, coming back to revisit this, and yes as you say ACS no longer supports the older HLLAPI stuff.

        So does anyone know any way to get ACS to communicate with Excel and use values in the excel workbook as input data for a macro function in ACS? Every month or so I go through IBMS's docco on ACS but I don't seem to find anything that means a lot to me or helps in anyway.

        Cheers
        GC
        Greg Craill: "Life's hard - Get a helmet !!"

        Comment


        • #5
          FYI--
          "EHLLAPI With the Access Client Solutions Emulator" at http://www-01.ibm.com/support/docvie...d=nas8N1010639

          Comment


          • #6
            Thanks JTaylor ... going through that process now, will let you know how I get on.
            Greg Craill: "Life's hard - Get a helmet !!"

            Comment


            • #7
              ok i have
              Installed ACS
              Installed the EHLLAPI as above with correct path etc
              uninstalled IBM i Access for Windows
              Installed ACS - windows application package
              Checked VBA and it has these reference bindings loaded

              IBM i Access for Windows ActiveX Object Library
              AS/400 Express Toolkit Table Index Type Library 1.2
              Client Access Object Library

              Which are all in the IBM i Access Client Solutions (Windows application Package) install lib.

              But when I check with this sample script from the manual I get a systems count of 0. So i am missing something that is preventing it from finding the systems I am connected to ...

              Any help appreciated ...

              PHP Code:
                //' Declare variables
                
              Dim systemNames As New cwbx.systemNames
                Dim count 
              As Long
                Dim index 
              As Long
                
              //' Determine the number of system names in the list
                
              count systemNames.count
                
              //' Display each system name in a message box
                
              For index 1 To count
                    MsgBox 
              "System " index " = " systemNames.Item(index)
                
              Next index 
              Greg Craill: "Life's hard - Get a helmet !!"

              Comment


              • #8
                Opened a PMR with IBM and the official reply is that "This is not possible" so if you want to use excel with macros you have to stick to the 5250 emulator (a subset of the pcom/IBM i Access for Windows products) and not use the java based ACS.
                Greg Craill: "Life's hard - Get a helmet !!"

                Comment


                • #9
                  Hello All:

                  This post has been dormant for some time, but I hope I can resurrect discussion around Excel interoperability with AS400 using VBA scripting. The problem has been ever since IT upgraded our office environment, and also upgraded the IBM emulator to IBM i Client Access Solutions, none of the hundreds of VBA scripts no longer work.. sad face...

                  IT did install a EHLLAPI patch, but the VBA code consistently error when trying to connect to the AS400.

                  Does anyone have sample VBA code which works with IBM i Client Access Solutions (v 1.1.9.1) 32 bit? Not that we are running Excel is running in 64 bit mode.

                  Any help would be greatly appreciated.

                  Al

                  Comment


                  • #10
                    It may be something that helps someone, but i've been working with code that uses cwbx.systemNames. It appears to have the same issue that people are seeing when using the sample script. I figured out that cwbx.systemNames is looking for what used to be the "Central System" when using Client Access. ACS doesn't seem to have a "Central System" so this comes back with 0 results. After checking another PC with Client Access on it, I finally found a few registry entries that can be added to supply this information.

                    Steps I used to get this to work with my visual studio code (unknown if it will fix the excel issues for sure).
                    1. Install IBM i Access Client Solutions
                    2. Install ACS Windows App Pkg English
                    3. Imported registry entries for the missing "Central System" information.
                    My code now works and finds a entry when using cwbx.systemNames using a Windows 10 PC. Below is the entries I added in the registry, just save the below text to a file.reg, edit the IP to your iSeries IP, and import into your registry.

                    ​[HKEY_CURRENT_USER\SOFTWARE\IBM\Client Access Express\CurrentVersion\Environments\My Connections]
                    "DefaultSystem"="9.5.5.3"

                    [HKEY_CURRENT_USER\SOFTWARE\IBM\Client Access Express\CurrentVersion\Environments\My Connections\9.5.5.3]

                    [HKEY_CURRENT_USER\SOFTWARE\IBM\Client Access Express\CurrentVersion\Environments\My Connections\9.5.5.3\Communication]
                    "Version Release Level"=dword:00070300
                    "Admin System Indicator"=dword:00000001
                    "Signon Mode"=dword:00000001
                    "User ID"=""
                    "Secure Sockets Layer"=dword:00000000
                    "Port lookup mode"=dword:00000000
                    "IP address lookup mode"=dword:00000000
                    "Persistence Mode"=dword:00000000
                    "Connect Timeout"=dword:0000001e
                    "Description"=""
                    "IP Address"=""
                    "IP Address cache timeout"=dword:64405cbc


                    Hope this helps someone!​

                    Comment

                    Working...
                    X