Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

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

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Excel VBA copying historic data from Attachmate sessionHelpful Member!(2) 

MazzaB (TechnicalUser) (OP)
2 Feb 12 13:53
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 (Programmer)
2 Feb 12 14:19


Code please.  Otherwise anyone would be guessing.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

MazzaB (TechnicalUser) (OP)
2 Feb 12 20:15
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 (Programmer)
2 Feb 12 21:12


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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

MazzaB (TechnicalUser) (OP)
2 Feb 12 23:53
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 (Programmer)
3 Feb 12 7:42
Please explain the sequence of events for one cycle of processing with respect to the workbook and the screen.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

MazzaB (TechnicalUser) (OP)
3 Feb 12 19:14
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 (TechnicalUser)
3 Feb 12 20:36
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 (Programmer)
3 Feb 12 22:56


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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

MazzaB (TechnicalUser) (OP)
4 Feb 12 3:15
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 (Programmer)
4 Feb 12 8:18


CODE


   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
'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

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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

Helpful Member!  SkipVought (Programmer)
4 Feb 12 8:26


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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

MazzaB (TechnicalUser) (OP)
4 Feb 12 14:52
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 (Programmer)
4 Feb 12 22:49
You said  nothing about the code i posted???

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

Helpful Member!  vzachin (TechnicalUser)
6 Feb 12 7:07
MazzaB, are you saying that an Attachmate Macro will work vs an Excel Macro?

you said

Quote:

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)

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

CODE

result = Sess0.Screen.GetString(Row, 1, 50)
will begin on row 1 and not row 25.

did you try skip's suggestion?

CODE

      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:

CODE

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 (TechnicalUser) (OP)
6 Feb 12 11:00
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 (Programmer)
6 Feb 12 11:03

Quote:

it gets stuck at DoEvents.

How did you code the Do...Loop?

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

vzachin (TechnicalUser)
6 Feb 12 12:27
MazzaB,

WaitHostQuiet works for me in Excel VBA.

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

how about adding this?

CODE

   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 (Programmer)
6 Feb 12 12:45

Quote:

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

MazzaB (TechnicalUser) (OP)
6 Feb 12 14:33
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
SkipVought (Programmer)
6 Feb 12 14:40


Thanks for the direct feedback on the code I provided!!!

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

vzachin (TechnicalUser)
7 Feb 12 9:30
mazzaB, moving the cursor to another position ensures that after a successful find transaction, the cursor is moved back to where you would expect it.

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