Attachmate EXTRA! with Excel

kzhkr · Dec 11, 2018

Hi all,

I just completed a VBA course over at my side. So got a teeny bit knowledge of VBA.
I have a request which I want to achieve:

Working with Attachmate EXTRA! session, I want the macro to search for a value (eg. ABCDEF) in a excel table which I will be updating on the go.
once value is found, to offset 1 cell to the right to search for (eg. XYZ) then value found again, to offset 1 cell down to copy the value and paste back to EXTRA! session.

Is it do-able?

P/S: is there any pre-preparation I have to set in EXTRA! for it to 'communicate' with Excel? I copied some codings I found but always doesn't have any reaction.

Thanks in advance,
kzhkr

SkipVought · Dec 11, 2018

Hi,

It seems that you may have a situation similar to many I have had, where I had a list/table in an Excel sheet to use to populate a key value in the terminal emulator to request/SendKeys a screen of data from the mainframe and then take one or more values from the screen and put them in the same row in Excel.

Please supply the following:

1) a sample of the table in Excel
2) your VBA code
3) any additional information that may be helpful, like the screen coordinates for specific data of interest.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

kzhkr · Dec 12, 2018

Code:

' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$

Sub Main()
'--------------------------------------------------------------------------------
' Get the main system object
    Dim Sessions As Object
    Dim System As Object
    Set System = CreateObject("EXTRA.System")   ' Gets the system object
    If (System Is Nothing) Then
        MsgBox "Could not create the EXTRA System object.  Stopping macro playback."
        Stop
    End If
    Set Sessions = System.Sessions

    If (Sessions Is Nothing) Then
        MsgBox "Could not create the Sessions collection object.  Stopping macro playback."
        Stop
    End If
'--------------------------------------------------------------------------------
' Set the default wait timeout value
'   g_HostSettleTime = 2        ' milliseconds

    OldSystemTimeout& = System.TimeoutValue
    If (g_HostSettleTime > OldSystemTimeout) Then
        System.TimeoutValue = g_HostSettleTime
    End If

' Get the necessary Session Object
    Dim Sess0 As Object
    Set Sess0 = System.ActiveSession
    If (Sess0 Is Nothing) Then
        MsgBox "Could not create the Session object.  Stopping macro playback."
        Stop
    End If
    If Not Sess0.Visible Then Sess0.Visible = True
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    
    Dim obj As Object
    Dim objWorkbook As Object
    Set obj = CreateObject("Excel.Application")
    obj.Workbooks.Open Filename:="C:\Users\S90103\Desktop\rma_list_test.xlsx"
    obj.Visible = True
    
    Dim recBankID As String, pytCur As String, corBank As String
    
    recBankID = Trim(Sess0.Screen.GetString(11, 64, 8))
    ptyCur = Trim(Sess0.Screen.GetString(6, 46, 3))
    
    obj.Range("B14").Select
    Do While corBank = ""
        If recBankID = obj.ActiveCell.Value Then
            obj.ActiveCell.Offset(0, 1).Select
checkCur:
            If ptyCur = obj.ActiveCell.Value Then
                obj.ActiveCell.Offset(1, 0).Select
                corBank = obj.ActiveCell.Value
                MsgBox corBank
            Else
                obj.ActiveCell.Offset(0, 1).Select
                GoTo checkCur:
            End If
        Else
            obj.ActiveCell.Offset(1, 0).Select
        End If
    Loop
        MsgBox corBank
End Sub

Hi Skip!

Above is the code I manage to get it moving... I can't seem to attach my excel file though.
I have another qns is how do we close the excel window? From how it looks, I step through the codes how many times, is the number of windows left open behind.

Thanks,
kzhkr

SkipVought · Dec 12, 2018

Got some questions/clarifications:

First, I asked you for a sample of your Excel table. That visual would have answered some of my questions and also could have been a basis for conducting some tests for your code. But you failed to supply.

Column B, starting in row 14, seems to be where bank id values reside.

Beginning in column C, D, E... (with no end in sight) seems to be a horizontal list (not a particularly good design for a table) of ptyCur values.

Am I correct so far?

But now I run amok at [tt]checkCur:[/tt]

Code:

‘
    obj.Range("B14").Select
    Do While corBank = ""
        If [highlight #FCE94F]recBankID = obj.ActiveCell.Value[/highlight] Then
            obj.ActiveCell.Offset(0, 1).Select
[b]checkCur:[/b]
            If ptyCur = obj.ActiveCell.Value Then
                [highlight #8AE234]obj.ActiveCell.Offset(1, 0).Select[/highlight]
                corBank = obj.ActiveCell.Value
                MsgBox corBank
            Else
                obj.ActiveCell.Offset(0, 1).Select
                GoTo checkCur:
            End If

Didn’t you find the BankID [highlight #FCE94F]here[/highlight]? So why are you selecting [highlight #8AE234]here[/highlight] for the corBank? Or am I missing something?

Here’s where a representation of the Excel data AND a description of the BankID and ptyCur would help our discovery process.

BTW, right off the selection process in your code is problematic. But we’ll fix that once you supply the information we’ve been requesting.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

kzhkr · Feb 20, 2019

Hi Skip,

Sorry for the very very late reply as I was busy with another project.
I will putting this on hold for now as some information I cannot provide too due to some issues over at the other side.

Will be looking forward to more advices from you in the future!

Thanks

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Attachmate EXTRA! with Excel

kzhkr

Technical User

SkipVought

Programmer

kzhkr

Technical User

SkipVought

Programmer

kzhkr

Technical User

Similar threads

Part and Inventory Search

Sponsor