Excel VBA: Recognize last page of Attachmate Screen 1

[SuperKoopa]

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!

 

[SkipVought]

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,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SuperKoopa]

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:

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?

 

[SkipVought]

you might try...

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

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

 

[SuperKoopa]

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.

 

[SkipVought]

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
[tt]
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
[/tt]
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,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SuperKoopa]

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

 

[SkipVought]

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,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SuperKoopa]

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!

 

[SuperKoopa]

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.

 

[SkipVought]

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

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,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]