×
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

Excel VBA: Recognize last page of Attachmate Screen

Excel VBA: Recognize last page of Attachmate Screen

Excel VBA: Recognize last page of Attachmate Screen

(OP)
Hello,

LONG STORY: I am trying to automate a series of actions in Attachmate using macros in Excel. Without going too much into the detail, I am working with several accounts. I've made much progress in copying standard data that applies to all accounts, names, addresses, etc from Attachmate to Excel. However there are accounts that have varying data. This data is sometimes 1 or 2 pages long, in other cases it can be as many as 10-15 pages. I was wondering if it is possible to write a macro to copy the data of each page and identify the last page. I would hope that the macro would work for all circumstances whether there is 1 page or 20 pages.

SHORT STORY: I would like to use the SendKeys method if possible, I just need to know if a macro can identify the last page of data and if so, what is the coding for somthing like that.

This will be the first of many quesitons (each in their own thread of course). Any information would be greatly appreciated thank you!

RE: Excel VBA: Recognize last page of Attachmate Screen

hi,

Your VBA should have a DO...Loop where you test the Screen Message, to be a message that indicates either that there are MORE pages for the specified transaction or that there are NO MORE pages for the specified transaction.

This loop would be within any loop that you might have to process one or more transactions for a set of data that specifices the criteria for that transaction.

Skip,

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

RE: Excel VBA: Recognize last page of Attachmate Screen

(OP)
Thank you for your quick reply, I appreciate it. Loop! Thats right I forgot about that line, probably because I'm not sure how to use it. :(

I have several SendKey commands above it as to assist with the autmating of several processes within one Do...Loop segment. Here is what I have for this particular pages of data I am trying to extract:

CODE

Sess0.Screen.SendKeys ("<HOME>DATA2<ENTER>") 'Navigates to DATA screen
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Amts1 = Sess0.Screen.GetString(8, 8, 23)  'Gets Amounts from DATA
objWorkBook.Worksheets("Amounts").Cells(13, "B").Value = Amts1 'Transfers Data amounts to Excel

Loop Until Sess0.Screen.GetString(23, 2, 37) = "** NO MORE DATA **" 

Obivoulsy this is not working, i just plugged the data in where I thought may it was appropriate. I was wrong lol. It just looks incorrectly through the screens. How can I get this loop to apply just to this segment of SendKeys code, and not the others that are above it?

RE: Excel VBA: Recognize last page of Attachmate Screen

you might try...

CODE

Loop Until Trim(Sess0.Screen.GetString(23, 2, 37)) = "** NO MORE DATA **" 

Skip,

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

RE: Excel VBA: Recognize last page of Attachmate Screen

(OP)
I sure will give it a shot. I forgot to mention, or ask...The data I am trying to select is nearly the entire screen. The selection I have in the code I provided above selects just one row (8, 8, 23), of several rows and columns that I need to select in Attachmate. I probably should open a new thread per forum etiquette, but I need it to complete this particular code. Would you happen to know how to write that? I appreciate your help.

RE: Excel VBA: Recognize last page of Attachmate Screen

Writing a GOOD procedure for scraping a screen is a challenging task. The big, issue revolves around the fact that you have an asynchronous procces to synchronize with your code. That is, when you issue a SendKeys command, your code has to be able to wait until the system that your emulator is interfacing, responds with data or not. I have found that the WaitForCursor is the best method for guaranteeing that your code does not outrun the emulator.

The Outer loop supplies the data to the screen transaction(s) you wish to process.

The next loop might relate to the processing of the data on the screen, where you go row by row. When you finish processing the dataq, you determine if there is MORE or NO MORE. If there's MORE, thrn you issue a command to get the next page. If there's NO MORE, then you proceed to the next set of data in your outer loop.

So mow you want to process a group of rows/columns to an Excen sheet. Presumabaly these rows have one or more columns of some length and perhaps to be place into Excel as either TEXT or NUMERIC. I build a mapping table that describes the data like

TRANS FIELD FRM THR COL LEN TYP
ZCTRN Name 8 22 2 16 CHAR
ZCTRN Amt 8 22 20 6 NUM
...
ZCTRN MSG 24 24 2 79 CHAR

This says that the field the I am referring to as Name, has data in rows 8 thru 22, and columns 2 thru 17 and is character data. Amt is in columns 20 thru 25 and is numeric. I set this up in Excel and I read this into an array, or you could process it right off the sheet. The last Field is the Message field on row 24 of my 3270 emulator and 79 characters wide.

So your code would loop thru the FRM THR row values and use the COL and LEN values to assign the GetString(r, c, l) arguments.

Skip,

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

RE: Excel VBA: Recognize last page of Attachmate Screen

(OP)
Thank Skip, I appreciate the info. That might be out of my scope, Im extremely new to the Excel VBA-Attachmate data extraction, but what you're saying does sound a lot more efficient, I'm just really ignorant to the process outside of the simplistic SendKey method. lol...I'll probably need to do more reading when I can...

RE: Excel VBA: Recognize last page of Attachmate Screen

I am not suggesting something other than the SendKey method. That is fundamental.

SOMEHOW you have to be able to loop from x to y and then process the data on that row. It is a repetative process, that THAT is what arrays can help you do that.

What code do you have so far that you need help with.

Skip,

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

RE: Excel VBA: Recognize last page of Attachmate Screen

(OP)
Sorry for my late reply...I've been fiddling around with it and this has been my most successful attempt at writing a code for what I need to do:

Sub main()
Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
Call PrintScreen(Sess0)
End Sub
'-------------------------------
Sub PrintScreen(Sess0 As Object)
On Error Resume Next
' Set up error handling.
CurPath = "C:\Test\"
Filename = "ScreenCapture.xls"
MkDir CurPath
IDFile = CurPath + "\" + Filename
Close #1
Sess0.Screen.WaitHostQuiet (50)
Open IDFile For Append As #1
For I = 9 To 21
MyArea = Sess0.Screen.GetString(I, 22, 58)
Print #1, MyArea
Next I
Do
Sess0.Screen.SendKeys ("<PF8>")
Loop Until Sess0.Screen.GetString(23, 2, 38) = "** NO MORE ITEMS IN ACTIVITY LEDGER **"
Close #1
End Sub

But of course, this is posing other issues.

For each Attachmate page that I need to pull data from, it will always be rows 9 To 21 for 58 characters in length. Where I need each page of this data to paste is the ActiveWorkbook, on the "DataAmt" sheet in Cells B5, B6, B7...etc all the way until B143 max. if needed.

With the code above, I have not been able to print the results to the ActiveWorkbook, in the sheet or cells that I want. The only thing it does is create a new blank workbook and paste the date in the first sheet.

When the date is put in the sheet it will overwrite the date being printed in that cell so I need to know how to identify my target cell as well.

I hope I've explained it propertly in a way that makes sense...I don't know the terms all that well. Please let me know if I can clarify it further. Thank you SO much for your help!

RE: Excel VBA: Recognize last page of Attachmate Screen

(OP)
Not sure how to edit my previous post, but everywhere I said DATE I mean DATA. Geez, its been a long day. Sorry about that.

RE: Excel VBA: Recognize last page of Attachmate Screen

FIRST, always use Option Explicit!!!

Second, a workbook is not a TEXT FILE!!! You do not PRINT to an excel file. You cannot, for instance, specify a column B in a text file...

CODE

Option Explicit

Sub main()
    Dim Sessions, System As Object, Sess0 As Object
    Set System = CreateObject("EXTRA.System")
    Set Sessions = System.Sessions
    Set Sess0 = System.ActiveSession
    Call PrintScreen(Sess0)
End Sub
'-------------------------------
Sub PrintScreen(Sess0 As Object)
    Dim curpath As String, FileName As String, i As Integer, lRow As Long
    
    On Error Resume Next
    ' Set up error handling.
    curpath = "C:\Test\"
    FileName = "ScreenCapture.xls"
    
    With Workbooks.Open(curpath & FileName)
    
        Do
            With Sheets(1)
                lRow = .Cells(1, 1).CurrentRegion.Rows.Count + 1
                
                For i = 9 To 21
                    .Cells(lRow, "B").Value = Sess0.Screen.GetString(i, 22, 58)
                    lRow = lRow + 1
                Next i
            End With
            
            If Sess0.Screen.GetString(23, 2, 38) = "** NO MORE ITEMS IN ACTIVITY LEDGER **" Then Exit Do
            
            Sess0.Screen.SendKeys ("<PF8>")
            Do While Sess0.Screen.WaitForCursor("?", "?")   '[highlight]You need the coordinates for the screen resp postiton[/Sess0.Screen]
                DoEvents
            Loop
        Loop
        
        .Save
        .Close
    End With
End Sub 

Skip,

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

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! Already a Member? Login

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