Macro for Excel to Attachmate and Back Again 1

CADD6953 · Dec 22, 2018

I want to start off by apologizing for my basic understanding of VBA and the coding language. I have gone through numerous threads to try and piece together a macro to suit my needs but continue to run into issues due to my limited understanding. Any help is appreciated since it currently takes me a few days to collect this data by hand whereas a macro could probably pull it all in minutes.

The macro I am needing would essentially allow me to plug values from Excel into Attachmate and then pull data from several pages in Attachmate back over to Excel.

Here is what I have so far (with errors and what I cannot figure out of course [sad]

):

Code:

Sub DataExtraction()

Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
        '----------------------------------
        'Data begins in row 2 column A
        'where each value in A is 6 alphanumerical bytes
        'columns B through L would be pulled from Attachmate
        'I would like these to go into a different sheet in Excel
        '----------------------------------
        rw = 2
        rw1 = 1
        With Worksheets("Workbook")
        
        For x = rw To ActiveSheet.Rows.Count
                                             
        PO = .Cells(x, 1)
        PA = .Cells(x, 6)
        
        '-----First Part of Attachmate Coding to be Retrieved-------
        
        If PO = "" Then Exit Sub
        Sess0.Screen.MoveTo 3, 8
        Sess0.Screen.PutString PO, 2, 6
        Sess0.Screen.MoveTo 5, 22
        Sess0.Screen.SendKeys ("CG")
        Sess0.Screen.SendKeys ("<Enter>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        GroupName = Sess0.Screen.GetString(6, 17, 40)
        .Cells(rw1, 2) = GroupName
        Do
        For r = 11 To 22
        dd = 11
        SubGroup = Sess0.Screen.GetString(r, 7, 10)
        If SubGroup = "**********" Then
        Exit Do

        Else
        EDate = Sess0.Screen.GetString(r, 51, 8)
        Fund = Sess0.Screen.GetString(r, 44, 1)
        Plan = Sess0.Screen.GetString(r, 18, 18)
        PLine = Sess0.Screen.GetString(r, 38, 4)
        rw1 = rw1 + 1
        .Cells(rw1, 3) = EDate
        .Cells(rw1, 4) = Fund
        .Cells(rw1, 5) = Plan
        .Cells(rw1, 6) = SubGroup
        .Cells(rw1, 13) = PLine
        End If

        Next r
        
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.SendKeys ("<Pf8>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        '----------------------------------
        'If there are values in r 22
        'I need the data pulled and then a command
        'given to go to the next page "<Pf8>" and
        'loop until no more values/subgroups
        '----------------------------------

        Loop

        '-----Second Part of Attachmate Coding to be Retrieved-------
        
        Do
        If PA = "" Then Exit Do
        Sess0.Screen.MoveTo 3, 31
        Sess0.Screen.PutString PA, 2, 10
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.PutString "CA", 5, 22
        Sess0.Screen.SendKeys ("<Enter>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        
        CCode = Sess0.Screen.GetString(6, 21, 4)
        rw1 = rw1 + 1
        .Cells(rw1, 7) = CCode
        
        Loop
        
        '-----Third Part of Attachmate Coding to be Retrieved-------

        If PA = "" Then Exit Sub
        Sess0.Screen.MoveTo 3, 31
        Sess0.Screen.PutString PA, 2, 10
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.PutString "VV", 5, 22
        Sess0.Screen.SendKeys ("<Enter>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Do
        For r = 8 To 18
        dd = 8
        Selection = Sess0.Screen.GetString(r, 8, 3)
        If Selection = "EMD" Then
        Sess0.Screen.PutString "S", r, 4
        Sess0.Screen.SendKeys ("<Enter>")
        '----------------------------------
        'I need it to look specifically for
        'the presence of "EMD" and then send a
        'command "S" next to it followed by "<Enter>"
        'to access the page with data I need to pull
        '----------------------------------
            BLEDate = Sess0.Screen.GetString(11, 49, 8)
            PC = Sess0.Screen.GetString(11, 20, 7)
            BL1 = Sess0.Screen.GetString(15, 3, 4)
            BL2 = Sess0.Screen.GetString(15, 18, 4)
            BL3 = Sess0.Screen.GetString(15, 38, 4)
            rw1 = rw1 + 1
            .Cells(rw1, 3) = PC
            .Cells(rw1, 4) = BL1
            .Cells(rw1, 5) = BL2
            .Cells(rw1, 6) = BL3
            .Cells(rw1, 13) = BLEDate
        Else
        End If
        Next r
        Exit Do
        
       
        Loop
        
        Next x  'next row/group
        
        End With

End Sub

SkipVought · Dec 22, 2018

Hi,

Seems you have two Excel sheets. You have given a sheet name for neither. Need these sheet names.

Seems you are getting data from one Excel sheet in columns A & F for PO & PA. Yes?

Then you SendKeys and get data from the mainframe, GroupName and assign GroupName to the same Sheet you got PO & PA from in row 1, column B. Don’t think that’s what you really want. Let’s assume that the first sheet is Sheet1 and the other sheet is Sheet2. So you really want... [tt]Sheet2.Cells(rw1, 2) = GroupName[/tt]. Same for CCode and everywhere else your rw1 row is used.

See if that helps.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

CADD6953 · Dec 22, 2018

Hey Skip,
So Sheet 1 would be "Groups" and Sheet 2 would be Workbook. I would like to pull the values from Column A in the first sheet to plug in and pull all the remaining values into the second sheet.
For some of the data I am needing pulled (outlined for the second and third part of the coding) I have to actually use the values from the first sheet (A) and second sheet (F) to plug back into Attachmate.
I've gone through and recoded all the .Cells to Sheet2.Cells or Sheet1.Cells as needed for my test workbook but it seems like the coding isn't even initially plugging into Attachmate anymore.
Thanks!

SkipVought · Dec 22, 2018

I’ve scraped screens for quite a while, though not any more. I discovered early on that the Attachmate VB editor is like a Yugo, while the Excel VBA editor is like a BMW. Consequently, I coded all my Attachmate scrapes in Excel VBA.

In the Excel VBA editor, you have features that make debugging your code a lot easier. That’s the first recommendation I’d offer.

use the values from the first sheet (A) and second sheet (F) to plug back into Attachmate.

Is that second sheet the PA value. That sheet reference needs to be changed, too.

Please post your current corrected code.

You might also want to use the actual sheet names rather than Sheet1, Sheet1.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

CADD6953 · Dec 22, 2018

Code:

Sub DataExtraction()

Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
        '----------------------------------
        'Data begins in row 2 column A
        'where each value in A is 6 alphanumerical bytes
        'columns B through L would be pulled from Attachmate
        'I would like these to go into a different sheet in Excel
        '----------------------------------
        rw = 2
        rw1 = 1
        With Worksheets("Workbook")
        
        For x = rw To ActiveSheet.Rows.Count
                                             
        PO = Sheet1.Cells(x, 1)
        PA = Sheet2.Cells(x, 6)
        
        '-----First Part of Attachmate Coding to be Retrieved-------
        
        If PO = "" Then Exit Sub
        Sess0.Screen.MoveTo 3, 8
        Sess0.Screen.PutString PO, 2, 6
        Sess0.Screen.MoveTo 5, 22
        Sess0.Screen.SendKeys ("CG")
        Sess0.Screen.SendKeys ("<Enter>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        GroupName = Sess0.Screen.GetString(6, 17, 40)
        Sheet2.Cells(rw1, 2) = GroupName
        Do
        For r = 11 To 22
        dd = 11
        SubGroup = Sess0.Screen.GetString(r, 7, 10)
        If SubGroup = "**********" Then
        Exit Do

        Else
        EDate = Sess0.Screen.GetString(r, 51, 8)
        Fund = Sess0.Screen.GetString(r, 44, 1)
        Plan = Sess0.Screen.GetString(r, 18, 18)
        PLine = Sess0.Screen.GetString(r, 38, 4)
        rw1 = rw1 + 1
        Sheet2.Cells(rw1, 3) = EDate
        Sheet2.Cells(rw1, 4) = Fund
        Sheet2.Cells(rw1, 5) = Plan
        Sheet2.Cells(rw1, 6) = SubGroup
        Sheet2.Cells(rw1, 13) = PLine
        End If

        Next r
        
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.SendKeys ("<Pf8>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        '----------------------------------
        'If there are values in r 22
        'I need the data pulled and then a command
        'given to go to the next page "<Pf8>" and
        'loop until no more values/subgroups
        '----------------------------------

        Loop

        '-----Second Part of Attachmate Coding to be Retrieved-------
        
        Do
        If PA = "" Then Exit Do
        Sess0.Screen.MoveTo 3, 31
        Sess0.Screen.PutString PA, 2, 10
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.PutString "CA", 5, 22
        Sess0.Screen.SendKeys ("<Enter>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        
        CCode = Sess0.Screen.GetString(6, 21, 4)
        rw1 = rw1 + 1
        Sheet2.Cells(rw1, 7) = CCode
        
        Loop
        
        '-----Third Part of Attachmate Coding to be Retrieved-------

        If PA = "" Then Exit Sub
        Sess0.Screen.MoveTo 3, 31
        Sess0.Screen.PutString PA, 2, 10
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.PutString "VV", 5, 22
        Sess0.Screen.SendKeys ("<Enter>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Do
        For r = 8 To 18
        dd = 8
        Selection = Sess0.Screen.GetString(r, 8, 3)
        If Selection = "EMD" Then
        Sess0.Screen.PutString "S", r, 4
        Sess0.Screen.SendKeys ("<Enter>")
        '----------------------------------
        'I need it to look specifically for
        'the presence of "EMD" and then send a
        'command "S" next to it followed by "<Enter>"
        'to access the page with data I need to pull
        '----------------------------------
            BLEDate = Sess0.Screen.GetString(11, 49, 8)
            PC = Sess0.Screen.GetString(11, 20, 7)
            BL1 = Sess0.Screen.GetString(15, 3, 4)
            BL2 = Sess0.Screen.GetString(15, 18, 4)
            BL3 = Sess0.Screen.GetString(15, 38, 4)
            rw1 = rw1 + 1
            Sheet2.Cells(rw1, 3) = PC
            Sheet2.Cells(rw1, 4) = BL1
            Sheet2.Cells(rw1, 5) = BL2
            Sheet2.Cells(rw1, 6) = BL3
            Sheet2.Cells(rw1, 13) = BLEDate
        Else
        End If
        Next r
        Exit Do
        
       
        Loop
        
        Next x  'next row/group
        
        End With

End Sub

SkipVought · Dec 22, 2018

You do realize that on Sheet2, column 6 (F), you are getting PA AND putting SubGroup and BL3.

How do you explain that?

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

CADD6953 · Dec 22, 2018

Sorry I've been back and forth with this coding and forgot to correct that, it should actually read as:

Code:

            BLEDate = Sess0.Screen.GetString(11, 49, 8)
            PC = Sess0.Screen.GetString(11, 20, 7)
            BL1 = Sess0.Screen.GetString(15, 3, 4)
            BL2 = Sess0.Screen.GetString(15, 18, 4)
            BL3 = Sess0.Screen.GetString(15, 38, 4)
            rw1 = rw1 + 1
            Sheet2.Cells(rw1, 8) = PC
            Sheet2.Cells(rw1, 9) = BL1
            Sheet2.Cells(rw1, 10) = BL2
            Sheet2.Cells(rw1, 11) = BL3
            Sheet2.Cells(rw1, 12) = BLEDate

SkipVought · Dec 22, 2018

Well what about

Code:

Sheet2.Cells(rw1, 6) = SubGroup

???

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

CADD6953 · Dec 22, 2018

Hmmm well wouldn't it technically be storing the information from Attachmate to 6 in the first part of the macro and then for the later parts excel would already have that information there to copy back into Attachmate? Like I said, unfortunately I'm not too skilled in creating these macros so I'm unsure if it's possible to pull the data, store it, and then proceed to re-reference it for the later parts of coding.

SkipVought · Dec 22, 2018

I need to look at this further, but you have THREE places where you incriment rw1 before you incriment x (next row in Sheet1).

Is that a problem?

Also, you already have PA up near the beginning. Is that also SubGroup?

So you had a value in Sheet2.Cells(x, 6), but you’re overwriting that value with SubGroup. That’s a bit unusual. Guess I’m not understanding.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

CADD6953 · Dec 23, 2018

Based on values from column A in sheet 1, I need data pulled based on values from different pages/locations within Attachmate which is why you see the three separations in the coding per row from sheet 1.

The first PA given in the coding is just esablishing the location where the value will be extracted from later in the coding process. So for the first part of the coding it would be pulling data from Attachmate and placing it into that field based on column A sheet 1 and then later in the coding it would use the data which was previously stored in the coding to submit back into Attachmate in order to pull the remaining data needed.

The coding language is quite difficult for me to understand though so when I receive errors for the loops/if, else/do for statements I don’t know if I’m writing it correctly.

SkipVought · Dec 24, 2018

Sorry. We’ve had visitors from out of town.

I receive errors for the loops/if, else/do for statements

Please describe any error message.

When you hit the Debug button, what statement is indicated?

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

CADD6953 · Dec 24, 2018

PA = Sheet2.Cells(x, 6)

Shows an error stating object required. I'm thinking I may need to just compose two macros...one to extract the initial data and then the other to run once the data is available in the excel workbook.

SkipVought · Dec 24, 2018

Well, things have gotten out of sorts. Initially Sheet1 is equivalent to Sheets(1), that is the first sheet in the workook and Sheet2 is equivalent to Sheets(2), the second sheet in the workbook and so on.

However, if you either deleted Sheet2 or moved Sheet2 to somewhere other than the second sheet in the workbook, then there’s a problem with your code.

It really would be much better to use the name of the sheet like Sheets(“Workbook”), but even that can change. There are many factors to be aware of.

So if you think that Sheet2 is the same sheet as Sheets(“Workbook”), then...

Code:

PA = Sheets(“Workbook”).Cells(x, 6)

As you encounter additional errors, please supply the error message and the statement in error.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

CADD6953 · Dec 24, 2018

Alright, I've decided to do it piece by piece.

I've used your suggestions and altered it a bit to pull exactly what I need and not loop 11 time per page. I was running it fine until I tried to add a new term condition since it kept looping the last page of data which was pulled (apparently "**" is not recognized as a blank, neither is " " or the IfEmpty command so I am stumped with that)...now I'm having Compile error (Invalid outside procedure) appearing in the prompt box for the Sub DataExtraction() line...no clue how it was working and recognizing without any changes to these fields up until the last few attempts.

If you know of a way for it to term the loop based on a blank value/missing value in one of the rows then please let me know, otherwise I have resorted to trying to have it recognize the LAST PAGE prompt that would appear at the bottom of the page.

Code:

Sub DataExtraction()

Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
g_HostSettleTime = 1000
        '----------------------------------
        'Data begins in row 2 column A
        'where each value in A is 6 alphanumerical bytes
        'columns B through L would be pulled from Attachmate
        'I would like these to go into a different sheet in Excel
        '----------------------------------
        rw = 2
        rw1 = 1
        With Worksheets("Workbook")
        
        For X = rw To ActiveSheet.Rows.Count
                                             
        PO = Sheets("Groups").Cells(X, 1)
        PA = Sheets("Workbook").Cells(X, 6)
        
        '-----First Part of Attachmate Coding to be Retrieved-------
        
        If PO = "" Then Exit Sub
        Sess0.Screen.MoveTo 3, 8
        Sess0.Screen.PutString PO, 2, 6
        Sess0.Screen.MoveTo 5, 22
        Sess0.Screen.SendKeys ("CG")
        Sess0.Screen.SendKeys ("<Enter>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

        Do
        For r = 11 To 22
        dd = 1
        S = Sess0.Screen.GetString(r, 3, 2)
        If S = "**" Then
        Exit Do

        Else
        EDate = Sess0.Screen.GetString(r, 51, 8)
        Fund = Sess0.Screen.GetString(r, 44, 1)
        Plan = Sess0.Screen.GetString(r, 18, 18)
        PLine = Sess0.Screen.GetString(r, 38, 4)
        GroupName = Sess0.Screen.GetString(6, 17, 40)
        CaseNumber = Sess0.Screen.GetString(3, 8, 6)
        rw1 = rw1 + 1
        Sheets("Workbook").Cells(rw1, 1) = CaseNumber
        Sheets("Workbook").Cells(rw1, 2) = GroupName
        Sheets("Workbook").Cells(rw1, 3) = EDate
        Sheets("Workbook").Cells(rw1, 4) = Fund
        Sheets("Workbook").Cells(rw1, 5) = Plan
        Sheets("Workbook").Cells(rw1, 6) = SubGroup
        Sheets("Workbook").Cells(rw1, 13) = PLine
        End If

        Next r
        
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.SendKeys ("<Pf8>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        '----------------------------------
        'If there are values in r 22
        'I need the data pulled and then a command
        'given to go to the next page "<Pf8>" and
        'loop until no more values/subgroups
        '----------------------------------
        For r = 23 To 23
        TermPage = Sess0.Screen.GetString(r, 2, 9)
        If TermPage = "LAST PAGE" Then
        Sess0.Screen.SendKeys ("<Pf2>")
        
        Loop
        
        Next X  'next row/group
        
        Sess0.Screen.SendKeys ("<Pf2>")
        
        End With

End Sub

SkipVought · Dec 24, 2018

Screen navigation is of primary concern: when to get more, when there’s no more, when there is no data. You ought to test that befor adding getting data from Excel and writing data to Excel.

Yes, a SPACE is not NOTHING.
Use Trim() to remove leading/trailing spaces ALL THE TIME.

Yes, “**” is not NOTHING. It is 2 ASTERISKS.

Whatever you scrape off a screen is TEXT. So...

Code:

PA = Trim(Sheets("Workbook").Cells(X, 6))
If PA = “” Then
   ‘Yer done
End If

In the IBM systems I got data from, row 24 contained messages related to navigation and was used exclusively to determine if there was another screen of data, for instance.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

CADD6953 · Dec 24, 2018

Hey Skip, thanks for the info. I'll add the trim into the code but it doesn't seem to help. Before the loop repeats with the next row in excel, I need the macro to recognize an empty value or "LAST PAGE" within Attachmate. The macro doesn't seem to be assigning values from "Groups" into the 3, 8 location anymore either so currently the macro runs as follows:
Either:
The value isn't transferred over from "Groups" to location 3, 8 and it errors.
Or:
If a value is manually typed into location 3, 8 prior to the macro running then it will screen swipe from the first page to the last (rows 11-22, ("<Pf8>"), rows 11-22, so on and so on) but the last page will continuously refresh and swipe via the ("<Pf8>") rather than recognizing no value in S or the "LAST PAGE" in the TermPage.

Code:

       S = Sess0.Screen.GetString(r, 3, 2)
        If S = "" Then
        Exit Do

Code:

        For r = 23 To 23
        TermPage = Sess0.Screen.GetString(r, 2, 9)
        If TermPage = "LAST PAGE" Then
        Sess0.Screen.SendKeys ("<Pf2>")

I realize having both of these commands in there would not be needed but I just wanted to show what was placed in an attempt to cease the loop for that value in "Groups" and move onto the next.

SkipVought · Dec 24, 2018

Code:

S = [b]Trim[/b](Sess0.Screen.GetString(r, 3, 2))
        If S = "" Then
        Exit Do 
‘.....
For r = 23 To 23
        TermPage = [b]Trim[/b](Sess0.Screen.GetString(r, 2, 9))
        If TermPage = "LAST PAGE" Then
        Sess0.Screen.SendKeys ("<Pf2>")

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

CADD6953 · Dec 24, 2018

Sorry, it's a Monday where my brain didn't think to put it there. That works perfectly! Now I'm just struggling with pulling the data in from Excel and having each of those loop through this process; it doesn't seem to want transfer the data from "Groups" into Attachmate at all. I'm not too sure if maybe the format when I assigned Values to PO was done incorrectly but I've tried Range ("A" & X) before too to no avail.

Code:

PO = Trim(Sheets("Groups").Cells(X, 1))
            PA = Trim(Sheets("Workbook").Cells(X, 6))
        
                If PO = "" Then Exit Sub
                Sess0.Screen.MoveTo 3, 8
                Sess0.Screen.PutString PO, 2, 6

Also, thank you so much for helping me with this! I'm sure you're probably banging your head against the wall when I come back with my responses LOL

SkipVought · Dec 24, 2018

Are you coding in Excel VBA or Extra VB?

If in Excel, you can STEP throught your code and observe the values that are in your variables. See how to use the Watch Window to see what happenings to any of your variables or Objects in Excel or Attachmate.

Faq707-4594

So what’s happening here...

Code:

Sess0.Screen.PutString PO, 2, 6

Do you not get the PO value at 2,6 on your screen?

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Macro for Excel to Attachmate and Back Again 1

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor