Excel VBA copying historic data from Attachmate session 2

[MazzaB]

With the help of this forum I've managed to write a short Macro that enters a router command into Attachmate from Excel and then copies the data returned back to Excel, my plan being to use this data to compile the next router command.
The problem is that the data that is copied back into Excel is historic data from the Attachmate session. I can post the code when I get to work but I dont think it is a problem with the VBA code but rather with Attachmate.
My Attachmate cursor always shows Row 25 and I cant move it to any other position on the screen. If I copy rows 1 to 25 to Excel for example it copies historic data so I tried rows 25 to 50 but it doesn't copy anything in addition to what I did previously.
I'm finding it impossible to copy the current data.
Do I need to copy the data as it comes into Attachmate rather than from the screen after the command is executed ?

 

[SkipVought]

Code please. Otherwise anyone would be guessing.

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

 

[MazzaB]

Dim Row As Integer
Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession

With Worksheets("Sheet1")

sendCommand = .Cells(2, 1)

'-----send data to Attachmate-------

If sendCommand = "" Then Exit Sub
Sess0.Screen.PutString sendCommand
Sess0.Screen.SendKeys ("<Enter>")
For Row = 1 To 50
result = Sess0.Screen.GetString(Row, 1, 50)
.Cells(Row, 2) = result
Next Row
End With

End Sub

 

[SkipVought]

your code is not doing anything to the cursor, and why should it matter?

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

 

[MazzaB]

The cursor never moves from that row position, it cant be moved.
When I enter a command and then the screen fills with data the row is always 025. I can only move the cursor horizontally to a different column such as when typing a router command.
The row column indicator shows as 1(024,023)
The 023 might vary depending on the length of the router name I'm logged into.
It matters because unless I can capture the data from the router after entering a command there is no point in using the macro.

 

[SkipVought]

Please explain the sequence of events for one cycle of processing with respect to the workbook and the screen.

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

 

[MazzaB]

Well I enter one line or command at a time, that is what the router takes, only one line at a time, there is no moving fields or cursor.
When I Getstring as above it copies old data that is already on the screen from previous commands entered.
eg if I run my macro after logging on, the excel spreadsheet is populated with the generic logon warning about being sacked if disclosing info etc

If you need screen scrapes I will need to to change all the data before I upload here.

 

[vzachin]

MazzaB,

just a guess, maybe your macro is running faster than the screen refreshes with data?

did you try stepping through the code?

za

 

[SkipVought]

PutString needs a row,col, else, I believe that the string goes where the cursor is, and you never issue ANY code that does anything to the cursor, and why should you? Just give it the proper row,col to put the string!

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

 

[MazzaB]

Hi Skip, the putstring command works fine, I don't believe I do need to specify the row an column as the cursor is always the right place for the command to be entered, the router is always waiting for a single line command. It's the copying of data from Attachmate to Excel that is my problem though I will try your suggestion to see if it makes a difference.

 

[SkipVought]

   With Worksheets("Sheet1")
      
      sendCommand = .Cells(2, 1).value
        
      '-----send data to Attachmate-------
        
      If sendCommand = "" Then Exit Sub
      Sess0.Screen.PutString sendCommand
      Sess0.Screen.SendKeys ("<Enter>")

      Sess0.Screen.moverelative 1,1
'[b]this waits for the cursor to come back to this screen's rest WhatRow,WhatCol that YOU need to supply in the next statement[/b]
      do while Sess0.Screen.waitforcursor(WhatRow,WhatCol)
         DoEvents
      loop

      For Row = 1 To 50
        .Cells(Row, 2).value = Sess0.Screen.GetString(Row, 1, 50)
      Next Row
   End With

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

 

[SkipVought]

Your program is trying to work with an asynchronous process. When you issue a command to the emulator, that system goes off and tyrs to retrieve some data. Mean while, your code goes merrily on to the For...Next loop, thinking that it has the data, when it REALLY needs to WAIT for that other system to return the data and say, "OK, I'm ready." That's what the Do...Loop accomplishes.

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

 

[MazzaB]

Hi Skip, you are right that I need to wait for the data to be returned. Unfortunately it would seem that the data needs to be returned before I can read what is on the screen at a specified location hence the waitforcursor doesn't seem to work nor does waitforstring.

I can get it to work 100% of the time and proved the above to be true by putting a pause after the command is sent to Attachmate
Application.Wait Time + TimeSerial(0, 0, 2)
This isn't really a solution as different commands will take a lot longer to return the data so I tried:
Sess0.Screen.WaitHostQuiet (500)
This also works but when I increase the timeout figure in brackets for another command that takes approx 10 secs the macro just waited for the timeout for some reason.
At the moment the only solution seems to be to use an Attachmate Macro rather than in Excel.

 

[SkipVought]

You said nothing about the code i posted???

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

 

[vzachin]

MazzaB, are you saying that an Attachmate Macro will work vs an Excel Macro?

you said

When I enter a command and then the screen fills with data the row is always 025. I can only move the cursor horizontally to a different column such as when typing a router command.
The row column indicator shows as [blue]1(024,023)[/blue]

are you saying that your cursor is always on row 1, col 23 or col 24? and the "new" data begins in row 25?

result = Sess0.Screen.GetString(Row, 1, 50)

will begin on row 1 and not row 25.

did you try skip's suggestion?

      Sess0.Screen.moverelative 1,1
'this waits for the cursor to come back to this screen's rest WhatRow,WhatCol that YOU need to supply in the next statement
      do while Sess0.Screen.waitforcursor(WhatRow,WhatCol)
         DoEvents
      loop

i do something similar:

 Sess0.Screen.Moveto 24,2
do
   DoEvents
loop until sess0.screen.row = 1 and sess0.screen.col = 24

where sess0.screen.row & col equals where i would expect my cursor.

za

 

[MazzaB]

Hi Skip, I tried your code but it gets stuck at DoEvents.

vzachin, yes an Attachmate Macro will work because the Waithostquiet statement works whereas when I use this in Excel VBA it appears to just wait for the timeout even though Attachmate has entered the command and retrieved all the data and is sitting there with a flashing cursor.

The cursor is always on row 24, the column depends on the name of the router I'm logged into as it precedes the cursor.
In the example I was using after the command is executed and the data returned the cursor is at row 24 column 23. This is shown as 1(024,023) at the bottom of the screen, I don't know what the 1 refers to.

I can't move the cursor to a different row, the down key does nothing and if I press enter whatever is typed after the cursor will be sent to the router. I can only type one line at a time.

 

[SkipVought]

it gets stuck at DoEvents.

How did you code the Do...Loop?

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

 

[vzachin]

MazzaB,

WaitHostQuiet works for me in Excel VBA.

i don't see the WaitHostQuiet in your code though.

how about adding this?

   Do While Sess.OIA.XStatus <> 0
         Loop

You are trying to capture 50 rows of data from Attachmate?
i can't change my attachmate screen to show more than 24 rows.

what happens when you step through the code without DoEvents?

 

[SkipVought]

is shown as 1(024,023) at the bottom of the screen, I don't know what the 1 refers to.

Someone in your IT ought to know, but I'd GUESS that 1 might be the SESSION, so Session 1 is active with cursor at row 24 column 23.

Again, need to see your code!!!


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

 

[MazzaB]

Ok guys the code below works, what was needed was the pause after sending the command to the router from Attachmate so the screen changed befor the code started to look for data at the specified screen positions.
As the router names have variable lengths I need to work out how to get the current cursor column as a variable before I run the code so I can test for when the cursor has returned to the rest position. For some reason I didnt need the moveto or moverelative command, does this just move the cursor ?

Many thanks for your help chaps !

With Worksheets("Sheet1")

sendCommand = .Cells(2, 1).Value

'-----send data to Attachmate-------

If sendCommand = "" Then Exit Sub
Sess0.Screen.PutString sendCommand
Sess0.Screen.SendKeys ("<Enter>")
Sess0.Screen.WaitHostQuiet (1000)
Do
DoEvents
Loop Until Sess0.Screen.Row = 25 And Sess0.Screen.col = 22
For Row = 15 To 25
.Cells(Row - 14, 2).Value = Sess0.Screen.GetString(Row, 1, 50)

Next Row
End With


End Sub