×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Attachmate EXTRA! with Excel

Attachmate EXTRA! with Excel

Attachmate EXTRA! with Excel

(OP)
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. :D

Thanks in advance,
kzhkr

RE: Attachmate EXTRA! with Excel

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate EXTRA! with Excel

(OP)

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

RE: Attachmate EXTRA! with Excel

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 checkCur:

CODE

‘
    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 
Didn’t you find the BankID here? So why are you selecting here 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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate EXTRA! with Excel

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close