EHLLAPI in Excel 64-bit

[ipherrox]

Hello all,

The company I work for is going through a technology modernization push, and recently upgraded to IBM IAccess Client Solutions which broke our VBA based Excel macros. Fortunately i was able to convert the macros to use the EHLLAPI interface to work with both the new and old clients to keep our operation running (with the added side effect of being faster and more stable than using the old COM Objects!).

However, now IT is moving to Office 365, and wants to install the 64-bit version on all computers. Unfortunately the EHLLAPI Bridge simply does not work on 64-bit Excel, due to being a 32-bit DLL. So I was wondering if anyone has found a way to get this to work in 64-bit Excel. Googling has turned up very few leads. Thank you very much.

[john.sev99]

There is a RFE open with IBM to provide this support so I would suggest getting as many people to vote for it as you can:

https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=140958

[ipherrox]

Thank you. I will pass this RFE to our IT team and see what they can do. I would think that IBM would make this a priority as user-side automation of iSeries systems is a huge advantage that it has over many competitors.

[BlackyGxG]

Hello,

The company that I work for has moved to the new IBM iAccess Client Solutions and, as ipherrox, we are having trouble running macros. We have tryied some indications found on the web to install and run them thru EHLLAPI Bridge, but we aren't getting any luck.

Could anyone sugest/help us with some tips regarding the installing and migrating the VBscript macros to HAscript?

Thank you!

[Al Rice]

Hello All

I found this stream here and I am having the exact same problem today.

Could anyone suggest/help us with some tips regarding the installing and migrating the VBscript macros to HAscript, if necessary to interact with the AS400 and Excel? I get the error, below, when trying to connect to the AS400 session via iSeries Access for Windows ver. 6.0 (Emulator Ver. 1.1.8.2...32-bit). Windows 10 Pro is 64-Bit, and Excel 2013 is 32-Bit Any help or sample VBA code would be much appreciated.

[ipherrox]

It seems that IBM just released a new version of the EHLLAPI Bridge and client to work with 64-bit applications. I'm waiting for IT to install the 64-bit version on my machine, but was wondering if anybody else had a chance to see if it works without code modifications?

BlackyGxG and Al Rice - Sorry, I haven't looked at this forum in a long time. I had to basically write a translation layer for existing Excel VBA macros to work with the bridge.
I used the below declaration to link to the DLL in it's own Module

Private Declare PtrSafe Function hllapi Lib "ehlapi32.Dll" (Func%, ByVal Buffer$, bSize%, RetC%) As Long

And then I wrote a subprocedure for each of the functions, according to the EHLLAPI dll specs. Each call requires 4 values (function number, data buffer, the length of the data buffer, and a return code that in some functions doubles as a position code).
You can then use find-and-replace on the actual macro code to convert everything over to the new format (say if your old code used autECLPSObj.SendKeys "Value" to send keystrokes, just find&replace "autECLPSObj." with "AS00_" to make it use the new module's code. There were a lot of little oddities I had to handle (the function keys are formatted completely different, so i had to make a lookup table to convert them, buffers have to be pre-allocated, etc) but it works faster and more reliably than the old native VBA code.

Sub AS400_ConnectSession(session As String)


'=================AS400 EHLLAPI Header=====================
    
    Const FunctionNum As Long = 1 ' Connect Presentation Space
    Dim DataBuffer As String
        DataBuffer = UCase(Left(session, 1)) 'sessions are 1 character, and uppercase!
    Dim BufferLength As Integer
        BufferLength = Len(DataBuffer)
    Dim ReturnCode As Integer 'doubles as a "Position" code in some functions!
    
'=================END AS400 EHLLAPI Header=================


'main API call
    hllapi FunctionNum, DataBuffer, BufferLength, ReturnCode

    
'error handling and endstuff
    If ReturnCode <> 0 Then
        If ReturnCode = 11 Then
            AS400_ResetSystem
            ReturnCode = 0
            hllapi FunctionNum, DataBuffer, BufferLength, ReturnCode
        End If
    End If
    
    If ReturnCode <> 0 Then
        MsgBox "AS400_ConnectSession Error: " & ReturnCode & ". Check your Session letter and try again. If this error continues reboot your computer and try again."
        AS400_ResetSystem
        End
    End If


End Sub
​

GetText function

Function AS400_GetText(row As Long, col As Long, length As Long) As String


'=================AS400 EHLLAPI Header=====================

Const FunctionNum As Long = 8 ' Copy Presentation Space to String
Dim DataBuffer As String
DataBuffer = "" 'Text
Dim BufferLength As Integer
BufferLength = length

Dim ReturnCode As Integer 'doubles as a "Position" code in some functions!
ReturnCode = AS400_RC_to_Pos(row, col)

'=================END AS400 EHLLAPI Header=================


'prep
'Need to pre-allocate the DataBuffer to the expected Length
Dim tmp As Integer

For tmp = 1 To BufferLength
DataBuffer = DataBuffer & " "
Next

'main API call
hllapi FunctionNum, DataBuffer, BufferLength, ReturnCode

'error handling and endstuff
If ReturnCode <> 0 And DebugMode Then MsgBox "AS400_GetText Error: " & ReturnCode
AS400_GetText = DataBuffer

End Function​