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
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
Talk To Other Members
RE: Attachmate EXTRA! with Excel
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!
RE: Attachmate EXTRA! with Excel
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 SubHi 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
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 IfHere’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!
RE: Attachmate EXTRA! with Excel
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