Sending information from Excel to Attachmate Reflection 1

[dvirgint]

Hello,

I have been able to scrape information from Attachmate Reflection into Excel with the help of a very patient SkipVaught and vzachin. Within Excel, I've manipulated the data which was scraped and I now need to return this information back to another system I have in Reflection. Has anyone done this before? Is this even possible?

Thanks for any help.

 

[SkipVought]

hi,

I have worked with Attachmate Extra, not Attachmate Reflection.

If it were me, in either case, I would code in Excel VBA to scrape data from the emulator into Excel OR read data from Excel and place, UPDATE, I presume, in the emulator.

Similar approch. First build your data/screen control code, including message processing, and then the particulars of data mapping. I recommend using a mapping table.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dvirgint]

Thanks Skip, Attachmate Reflection mainly uses VBA, but can read and execute Extra Basic.

I really appreciate your quick responses!!

This is the a continuation of the code you helped me with in the last few days where I was searching for more than one instance of a string on a page. If you remember, this code was done in Excel VBA.

Do you have an example of how to use a mapping table?

 

[SkipVought]

I use a table like this for mapping data from many screens to an Excel table for each ScrName...
[tt]
ScrNam FIELD FR TR CL LN TYP
PPNI6703 ScrNam 1 1 3 8 CHAR
PPNI6703 T_Dat 1 1 63 8 NUM
PPNI6703 T_Tim 1 1 73 8 NUM
PPNI6703 PN 4 4 20 16 CHAR
PPNI6703 NOMEN 4 4 45 35 CHAR
PPNI6703 SEL 8 21 4 1 CHAR
PPNI6703 PACKAGE 8 21 8 7 CHAR
PPNI6703 VENDOR 8 21 17 6 CHAR
PPNI6703 TY 8 21 25 1 CHAR
PPNI6703 ST 8 21 28 1 CHAR
PPNI6703 # TRAV 8 21 31 3 CHAR
PPNI6703 QTY 8 21 35 5 NUM
PPNI6703 REC_QTY 8 21 41 5 NUM
PPNI6703 BEG OPER 8 21 48 3 CHAR
PPNI6703 END OPER 8 21 54 3 CHAR
PPNI6703 C DATE 8 21 60 8 NUM
PPNI6703 NEED DATE 8 21 70 8 NUM
PPNI6703 MSG 22 22 13 68 CHAR
[/tt]
My Excel table uses the same FIELD names.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dvirgint]

OK Skip, thanks for the table example but would you bring that back to Extra? How do you get it to go from row to row and column to column? Could you give me an example of what code you would use for this?

 

[SkipVought]

I developed a methodology to scrape the emulator and map the data to an Excel table. Usually I run my scrapper using a data list in Excel to polulate the INQUIRE transaction and take the returned screen data from each screen and map to one row in my Excel table. I even use this method if I'm only interested in ONE piece of data.

1. I read the entire screen contents into one string (24 * 80 bytes long in the case of a 3270 terminal emulator)

2. I have several functions that return various data I use in scrping the screen
[tt]
Returns field value given input string, Field name and optional row when multi row exists
Returns whether a field is multi row or not
Returns number of rows in multi row
Returns whether multi row has data
Loads screen spec array
Returns Excel column forgiven field
[/tt]
I cannot post the code I developed, but I can post this snippet...

    Const COMP = "LAST PAGE DISPLAYED"
    Const MORE = "MORE DETAIL LINES - PRESS ENTER TO CONTINUE"
    Const NONE = "TRAVELER NOT FOUND ON SERIAL SYSTEM"

    With oScrn
        For Each r In wsPartList.[PART_ID]
            sPn = r.Value
            .Area(3, 17, 3, 33).Value = sPn & "               "
            Do
                .MoveRelative 1, 1, 1
                .SendKeys ("<enter>")
                Do Until (.WaitForCursor(3, 17))
                    DoEvents
                Loop

                sIn = .Area(1, 1, 24, 80).Value
                For nRow = 0 To NbrRows
                    For Each fld In ScreenSpecRange("PPKI6703", "FIELD")
                        If MultiRow(fld.Value) Then
                            If Not RowHasData(sIn, fld.Value, nRow) Then GoTo NextPN
                            wsOutPPKI.Cells(lRow, OutCol("OUT PPKI", fld.Value)).Value = GetField(sIn, fld.Value, nRow)
                        Else
                            wsOutPPKI.Cells(lRow, OutCol("OUT PPKI", fld.Value)).Value = GetField(sIn, fld.Value)
                        End If
                    Next
                    lRow = lRow + 1
                Next

NextPN:
            Loop Until GetField(sIn, "MSG") = COMP Or GetField(sIn, "MSG") = NONE
        Next
    End With

In there, you can see the functions I call.

Now here's someone's code that I began using and modifying and modifying and modifying, until I decided to make my screen spec table and functions etc...

Dim objSystem As Object
Dim objSessions As Object

Dim sf602431 As Object
Dim timestamp2 As Object

Dim cc2 As Object
Dim machgrp2 As Object
Dim ngrp2 As Object
Dim wseq2 As Object
Dim part2 As Object
Dim trav2 As Object
Dim qty2 As Object
Dim location2 As Object
Dim oper2 As Object
Dim holdc2 As Object
Dim lmov2 As Object

Dim note2 As Object
Dim message2 As Object
Dim x2 As Integer
Dim XX2 As Integer
Dim y2 As Integer

Dim x4 As Integer
Dim y4 As Integer
Dim lastx4 As Integer
Dim start2 As Date
Dim finish2 As Date

Public Sub Get_sf602431()

'clear old data
Sheets("Output_trav").Select
Range("A2:M5000").Select
Selection.ClearContents

'begin reading inputs
Sheets("Input").Select
Range("A2").Select

start2 = Now
x2 = ActiveCell.Row
y2 = ActiveCell.Column
XX2 = 2
Do While IsEmpty(ActiveCell) = False
get_sf602431_details
x2 = x2 + 1
Application.GoTo reference:=ActiveSheet.Cells(x2, y2)
Loop



Sheets("Input").Select
Range("A1").Select
finish2 = Now
msgtext = "The macro started at : " & start2 & " and ended at : " & finish2
MsgBox msgtext
End Sub
Private Sub get_sf602431_details()
    Set sys2 = CreateObject("extra.system")
    Set sess2 = sys2.activesession
    Set sf602431 = sess2.screen
    Set cc2 = sf602431.area(2, 6, 2, 9)
    cc2.Value = "   "
    cc2.Value = ActiveCell.Value
NXTPAGE:
    sf602431.moveto 2, 80
    sf602431.SendKeys ("<ENTER>")
REENTER:
    sf602431.MoveRelative 1, 1
    Do
        DoEvents
        datarefreshed = True
    Loop Until sf602431.waitforcursor(2, 6)
    If datarefreshed Then
        Set message2 = sf602431.area(22, 2, 22, 27)
        message2.Value = sf602431.area(22, 2, 22, 27).Value
        If message2.Value = "COST CENTER IS NOT ON FILE" Then
        READLINES2
        GoTo FINI
        End If
        
        If message2.Value = "INQUIRY COMPLETE          " Then
        READLINES2
        GoTo FINI
        End If
        
        If message2.Value = "PRESS ENTER FOR NEXT PAGE " Then
        READLINES2
        GoTo NXTPAGE
        End If
        
'        If message2.Value = "LAST PAGE DISPLAYED      " Then
'        READLINES2
'        GoTo FINI
 '       End If
    End If
FINI:
    Set sys2 = Nothing
    Set sess2 = Nothing
    Set sf602431 = Nothing
    Set trav2 = Nothing
    Set note2 = Nothing
    Set message2 = Nothing
End Sub
Private Sub READLINES2()

Set timestamp2 = sf602431.area(1, 61, 1, 77)
Set cc2 = sf602431.area(2, 6, 2, 9)
Set message2 = sf602431.area(22, 2, 22, 27)
cc2.Value = sf602431.area(2, 6, 2, 9).Value
timestamp2.Value = sf602431.area(1, 61, 1, 77).Value
message2.Value = sf602431.area(22, 2, 22, 27).Value

For w = 8 To 21

Set machgrp2 = sf602431.area(w, 7, w, 11)
Set ngrp2 = sf602431.area(w, 13, w, 16)
Set wseq2 = sf602431.area(w, 18, w, 22)
Set part2 = sf602431.area(w, 23, w, 39)
Set trav2 = sf602431.area(w, 40, w, 46)
Set qty2 = sf602431.area(w, 53, w, 57)
Set location2 = sf602431.area(w, 59, w, 64)
Set oper2 = sf602431.area(w, 66, w, 68)
Set holdc2 = sf602431.area(w, 71, w, 74)
Set lmov2 = sf602431.area(w, 77, w, 80)

machgrp2.Value = sf602431.area(w, 7, w, 11).Value
ngrp2.Value = sf602431.area(w, 13, w, 16).Value
wseq2.Value = sf602431.area(w, 18, w, 22).Value
part2.Value = sf602431.area(w, 23, w, 39).Value
trav2.Value = sf602431.area(w, 40, w, 46).Value
qty2.Value = sf602431.area(w, 53, w, 57).Value
location2.Value = sf602431.area(w, 59, w, 64).Value
oper2.Value = sf602431.area(w, 66, w, 68).Value
holdc2.Value = sf602431.area(w, 71, w, 74).Value
lmov2 = sf602431.area(w, 77, w, 80).Value


'if trav is blank then skip lines
If trav2.Value = "        " Then
w = 21
End If

'if pn and oper are not blank
If part2.Value <> "                " And trav2.Value <> "        " Then
Sheets("Output_trav").Cells(XX2, 1).Value = "'" & Trim(cc2.Value)
Sheets("Output_trav").Cells(XX2, 2).Value = "'" & Trim(machgrp2.Value)
Sheets("Output_trav").Cells(XX2, 3).Value = Trim(ngrp2.Value)
Sheets("Output_trav").Cells(XX2, 4).Value = "'" & Trim(wseq2.Value)
Sheets("Output_trav").Cells(XX2, 5).Value = Trim(part2.Value)
Sheets("output_trav").Cells(XX2, 6).Value = "'" & Trim(trav2.Value)
Sheets("Output_trav").Cells(XX2, 7).Value = Trim(qty2.Value)
Sheets("Output_trav").Cells(XX2, 8).Value = "'" & Trim(location2.Value)
Sheets("Output_trav").Cells(XX2, 9).Value = "'" & Trim(oper2.Value)
Sheets("Output_trav").Cells(XX2, 10).Value = Trim(holdc2.Value)
Sheets("Output_trav").Cells(XX2, 11).Value = "'" & Trim(lmov2.Value)
Sheets("Output_trav").Cells(XX2, 12).Value = Trim(timestamp2.Value)
Sheets("Output_trav").Cells(XX2, 13).Value = Trim(message2.Value)

XX2 = XX2 + 1
End If
Next w

Set machgrp2 = Nothing
Set ngrp2 = Nothing
Set wseq2 = Nothing
Set part2 = Nothing
Set trav2 = Nothing
Set qty2 = Nothing
Set location2 = Nothing
Set oper2 = Nothing
Set holdc2 = Nothing
Set lmov2 = Nothing

Set timestamp2 = Nothing
Set message2 = Nothing
Set note2 = Nothing
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dvirgint]

Thanks a lot Skip, this should keep me busy for a while!!

I'll let you know how it turns out.

 

[dvirgint]

Hi Skip,

I was able to get the macro to work as needed. This is what I did:

rAmount = 2

Do
    pAmt = Sheets("Paiements").Cells(rAmount, "h")
    pEid = Sheets("Paiements").Cells(rAmount, "i")
    If pAmt = "0000" Then
        Exit Do
    Else
        Sess0.Screen.SendKeys (pAmt & "<tab>" & pEid)
    End If
    rAmount = rAmount + 1
    If rAmount = 27 Then
        Sess0.Screen.SendKeys ("<pf8>")
        Sess0.Screen.WaitHostQuiet (50)
    ElseIf rAmount = 72 Then
        Sess0.Screen.SendKeys ("<pf8>")
        Sess0.Screen.WaitHostQuiet (50)
    End If
Loop

I actually had more trouble getting the number formatted the way I needed in Excel than coding.

Thanks a bunch.
dvirgint

 

[SkipVought]

I actually had more trouble getting the number formatted the way I needed in Excel than coding.

How is that?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}