GetString - PutString from Excel to Attachmate - response back Excel

[vzdog]

I am using a macro to send a command to an Attachmate session. I have an Excel worksheet that is called by the Macro in Attachmate.

It puts the data from column B in to the Attachmate session and sends it.as you can see it loops until an empty cell.
No Problem, works Great.....but
the host session provides a response that I need to capture back on to the Excel spreadsheet.

Can anyone give me an idea of how to do this?

' 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 = 3000        ' 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)
    
'--------------------------------------------------------------------------------
'Declare the Excel Object
        Dim xlApp As Object, xlSheet As Object, MyRange As Object
        Set xlApp = CreateObject("excel.application")
        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        xlApp.Visible = True
        xlApp.Workbooks.Open FileName:="D:\PMDaily\Fetch.xls"
        Set xlSheet = xlApp.activesheet
        Set MyRange = xlApp.activesheet.Range("B:B")
  
 
Dim Row As Long
        With xlApp.ActiveSheet
           Set MyRange = .Range("B2:B65536").Resize(xlApp.CountA(.Range("B2:B65536")))
        End With
        For Row = 1 To MyRange.Rows.Count
           Sess0.Screen.PutString MyRange.Rows(Row).Value, 24, 6
           Sess0.Screen.SendKeys "<ENTER>"
           Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Next Row
 
End Sub

 

[SkipVought]

"the host session provides a response that I need to capture back on to the Excel spreadsheet."

WHERE on the screen is this value and how long?

xlSheet.Cells(1,1).value = Sess0.Screen.GetString(row,col,len)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vzdog]

Skip,

I do not know where to add the code in but I believe i see what you are leading to.

xlSheet.Cells(1,1).value = Sess0.Screen.GetString(19,6,6)

I am not sure where this would put the response back (from the host)on the Excel sheet. I need it go in Column C so my data in Columns A and B are maintained.

When it ends I need it to "Save and Close".

 

[vzdog]

I am guessing: so that it is part of the loop..

Dim Row As Long
        With xlApp.ActiveSheet
           Set MyRange = .Range("B2:B65536").Resize(xlApp.CountA(.Range("B2:B65536")))
        End With
        For Row = 1 To MyRange.Rows.Count
           Sess0.Screen.PutString MyRange.Rows(Row).Value, 24, 6
           Sess0.Screen.SendKeys "<ENTER>"
           Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
           xlSheet.Cells(1,1).value = Sess0.Screen.GetString(19,6,6)
        Next Row
 
End Sub

 

[SkipVought]

"the host session provides a response that I need to capture back on to the Excel spreadsheet."

I'm guessing that the response is in row 19, column 6 length 6.

If you want it in Excel column a row 1, then...

xlSheet.Cells(1,1).value = Sess0.Screen.GetString(19,6,6)

is correct.

However, if you're in a loop, then EVERY "response" gets overwritten except the LAST one. Wouldn't it make sense to incriment the row ...

xlSheet.Cells(row,1).value = Sess0.Screen.GetString(19,6,6)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vzdog]

Skip,

I think I see the incrementing, what you mean by that. To clarify, once this is ran through and the responses are recorded, the excel sheet will moved in to an Access table and then of course this sheet (recording the responses) will be over written the next time. It will actually start fresh. So will the incrementing be the best way or the

xlSheet.Cells(3,1).value = Sess0.Screen.GetString(19,6,6)

 

[vzdog]

This is wrong, isnt it?

xlSheet.Cells(3,1).value = Sess0.Screen.GetString(19,6,6)

should be for Column C row 1:

[xlSheet.Cells(1,3).value = Sess0.Screen.GetString(19,6,6)

 

[SkipVought]

"This is wrong, isnt it?"

You should be able to answer the question yourself.

Why are you using LITERALS, rather than VARIABLES if this is in a loop?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vzdog]

Ok Skip

It works with this code,but.....

Dim Row As Long
        With xlApp.ActiveSheet
           Set MyRange = .Range("B2:B65536").Resize(xlApp.CountA(.Range("B2:B65536")))
        End With
        For Row = 1 To MyRange.Rows.Count
           Sess0.Screen.PutString MyRange.Rows(Row).Value, 21, 3
           Sess0.Screen.Sendkeys("<F12>")
           Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
           xlSheet.Cells(row,3).value = Sess0.Screen.GetString(19,6,6)
           Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

        Next Row

My "responses" are begining on Column "c" row 1.
I need them to start on column "c" row 2 and increment from there

 

[vzdog]

Skip,

Here is my Excel sheet. Perhaps this will clarify some.

 

[vzdog]

ok that didnt work like I thought...
Here is a sample of it:

A B C
TID EnableCmd Status
BLBGVABBK13 SET-PMMODE-T1::0038-04::,ALL,ON; COMPLD
SET-PMMODE-T1::0039-03::,ALL,ON;
SET-PMMODE-T1::0040-27::,ALL,ON;
SET-PMMODE-T1::0041-09::,ALL,ON;
SET-PMMODE-T1::0041-13::,ALL,ON;
SET-PMMODE-T1::0042-23::,ALL,ON;
SET-PMMODE-T1::0045-09::,ALL,ON;
SET-PMMODE-T1::0048-01::,ALL,ON;
SET-PMMODE-T1::0048-02::,ALL,ON;


So for each command in column "B' sent I am trying to "capture" the host response in column "C

 

[SkipVought]

So for each command in column "B' sent I am trying to "capture" the host response in column "C"

Again, why are you using LITERALS in this statement?

xlSheet.Cells(row,3).value = Sess0.Screen.GetString(19,6,6)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vzdog]

Skip,

I believe we have a miss communication. I wish i could have sent you my Excel sheet. I have a heading on my column c.

So, it has to start on Row2. But i am not sure how to get it there. I did not think that qualified as a Variable.

 

[vzdog]

Lets try row + 1:

Dim Row As Long
        With xlApp.ActiveSheet
           Set MyRange = .Range("B2:B65536").Resize(xlApp.CountA(.Range("B2:B65536")))
        End With
        For Row = 1 To MyRange.Rows.Count
           Sess0.Screen.PutString MyRange.Rows(Row).Value, 21, 3
           Sess0.Screen.Sendkeys("<F12>")
           Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
           xlSheet.Cells(row + 1,3).value = Sess0.Screen.GetString(19,6,6)
           Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

        Next Row

Thank you Skip

 

[SkipVought]

xlSheet.Cells(row + 1,3).value = Sess0.Screen.GetString(19,6,6)

puts the value from the screen into column C.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tps14334]

I was looking for something just like this. Thank you, it has been a big help for me as well!

 

[SkipVought]

Wonderful!

Welcome to Tek-Tips, and Merry Christmas!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tps14334]

Skip, im hoping you can help me here some. I took this code and tried expanding on it to do a-bit more. Here is the code I currently have sans a majority of the variables:

        Const g_HostSettleTime as integer = 1

        Global System As Object,Sess0 As Object,Sessions As Object,Screen As Object

        Dim X as integer,Y as integer,Z as integer,Y1 as integer,Y2 as integer,Y3 as integer,Y4 as integer
        Dim Line1 as string,Line2 as string,Line3 as string,Line4 as string

Sub Trap_Alert()
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Do
        If InStr(Sess0.Screen.GetString (1,1,70),"ATTENTION") = 0 Then Exit Sub
        Sess0.Screen.Sendkeys("<Enter>"):Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Loop
End Sub

Sub Main()
' Get the main system 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
	OldSystemTimeout& = System.TimeoutValue
	If (g_HostSettleTime > OldSystemTimeout) Then
		System.TimeoutValue = g_HostSettleTime
	End If
' Get the necessary Session 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
        Set Screen = Sess0.Screen
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
    
'--------------------------------------------------------------------------------
'Declare the Excel Object
        Dim xlApp As Object, xlSheet As Object, Row As Long
        Dim Status As Object, Action As Object, HouseKey As Object, AddrLn1 As Object
                        
        Set xlApp = CreateObject("excel.application")
        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        xlApp.Visible = True
        xlApp.Workbooks.Open FileName:="File Location"
        Set xlSheet = xlApp.activesheet
        Set Status = xlApp.activesheet.Range("A:A")
        Set Action = xlApp.activesheet.Range("B:B")
        Set HouseKey = xlApp.activesheet.Range("C:C")
        Set AddrLn1 = xlApp.activesheet.Range("D:D")

        With xlApp.ActiveSheet
        
           Set Status = .Range("A4:A65536").Resize(xlApp.CountA(.Range("A4:A65536")))
           Set Action = .Range("B4:B65536").Resize(xlApp.CountB(.Range("B4:B65536")))
           Set HouseKey = .Range("C4:C65536").Resize(xlApp.CountC(.Range("C4:C65536")))
           Set AddrLn1 = .Range("D4:D65536").Resize(xlApp.CountD(.Range("D4:D65536")))
                           
        End With        
        
        
        '*** Action Time ***
        For Row = 1 To Status.Rows.Count
        
           Sess0.Screen.SendKeys("<CLEAR>"):Trap_Alert
           Sess0.Screen.SendKeys("<HOME>" + "HIU A"):Trap_Alert
           Sess0.Screen.SendKeys "<ENTER>":Trap_Alert
           
           Sess0.Screen.PutString ("7000"),2,47:Trap_Alert
           Sess0.Screen.PutString AddrLn1.Rows(Row).Value, 4, 7:Trap_Alert
                      
           xlSheet.Cells(row + 3,48).value = Sess0.Screen.GetString(1,7,14):Trap_Alert
                     
           If time > #10:00:00 AM# and time <#4:00:00 PM# then
	       Pause (10)
	   End if
           
        Next Row
 
End Sub

I am trying to take a lot columns from Excel and make them dynamic/tangible variables to use in EXTRA! to interact with my billing system. When trying to run the code the file is correctly opened in Excel with a active window but stops running with a error prompt like this:

EXTRA! Basic Error
Microsoft Office Excel
Line number: 74
Stopping macro playback.

I hope that makes sense and that you can help shed some light on this problem.

Thank you.

 

[SkipVought]

tps14334,

Please post your question in a new thread.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]