如何使用Excel VBA从AS400抓取屏幕数据？ActiveX错误求助

Hey Anthony, let's break down that ActiveX error you're hitting on the Set Mainframe line of your VBA code. I've messed around with System i Navigator and IBM's HLLAPI (the library behind saahlapi.dll) quite a bit, so here are the most likely fixes and improvements to get your data scraping up and running.

Common Causes & Solutions #

1. HLLAPI DLL Isn't Registered (or Wrong Architecture) #

First off, saahlapi.dll needs to be properly registered in Windows, and it has to match your Office architecture (32-bit vs 64-bit):

• Register the DLL: Open Command Prompt as Administrator and run this command (adjust the path to match your System i Navigator installation—this is the default path for most setups):

  regsvr32 "C:\Program Files\IBM\Client Access\Emulator\Private\saahlapi.dll"
  

• Match Architecture: If you're using 32-bit Excel, you need the 32-bit version of the DLL (even on 64-bit Windows). If Office is 64-bit, use the 64-bit DLL. Mismatches almost always trigger ActiveX errors.

2. Wrong ProgID in CreateObject #

Using the DLL filename directly (saahlapi.dll) isn't reliable—you should use the registered ProgID instead. IBM's HLLAPI typically registers under one of these:

• IBM.HLLAPI
• SAHLLAPI.HLLAPI.1

Replace your Set Mainframe line with one of these, and add proper session initialization (since CurrentHost requires an active session to exist).

3. Ensure a Simulator Session is Active #

Your code assumes a session is already running and connected to AS400. If no session is open, CurrentHost will throw an error. Make sure you:

• Launch System i Navigator
• Connect to your AS400 instance and open a terminal session
• Confirm the session is active (not minimized or disconnected)

4. Permission Issues #

Sometimes Excel doesn't have permission to access the HLLAPI component. Try opening Excel as Administrator, then running your macro—this often fixes permission-related ActiveX errors.

Modified Working Code Example #

Here's a revised version of your code that addresses these issues, with proper error handling and session management:

Sub AS400Connect()
    Dim hllapi As Object
    Dim sessionID As String
    Dim result As Long
    Dim outputSheet As Worksheet
    
    ' Initialize HLLAPI component
    On Error Resume Next
    Set hllapi = CreateObject("IBM.HLLAPI")
    On Error GoTo 0
    
    If hllapi Is Nothing Then
        MsgBox "Failed to load HLLAPI component. Check if it's registered correctly."
        Exit Sub
    End If
    
    ' Use your session ID (usually "A" for the first session—check your simulator settings)
    sessionID = "A"
    
    ' Connect to the active session
    result = hllapi.Connect(sessionID)
    If result <> 0 Then
        MsgBox "Couldn't connect to session " & sessionID & ". Error code: " & result
        Exit Sub
    End If
    
    ' Activate the session and send Enter
    result = hllapi.Activate(sessionID)
    If result = 0 Then
        hllapi.SendKeys sessionID, "{Enter}"
    Else
        MsgBox "Failed to activate session " & sessionID
    End If
    
    ' Set your output sheet
    Set outputSheet = ThisWorkbook.Sheets("Sheet1")
    
    ' Add your data scraping logic here—for example, reading screen content:
    ' Dim screenContent As String
    ' screenContent = hllapi.GetScreen(sessionID, 1, 1, 24, 80) ' Reads 24x80 screen
    
    ' Clean up
    hllapi.Disconnect sessionID
    Set hllapi = Nothing
End Sub

Extra Tips #

• If you don't know your session ID, check the simulator's "Session Properties" or use hllapi.EnumSessions to list all available sessions.
• Some System i Navigator versions require installing the HLLAPI component separately—check your installation options if the DLL isn't found.
• Make sure Excel's Trust Center allows macros and ActiveX controls (File > Options > Trust Center > Trust Center Settings).

内容的提问来源于stack exchange，提问作者Anthony