×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

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!
*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

Macro for Excel to Attachmate and Back Again

thread99-1791765

Macro for Excel to Attachmate and Back Again

Macro for Excel to Attachmate and Back Again

(OP)
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

RE: Macro for Excel to Attachmate and Back Again

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... Sheet2.Cells(rw1, 2) = GroupName. Same for CCode and everywhere else your rw1 row is used.

See if that helps.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Macro for Excel to Attachmate and Back Again

(OP)
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!

RE: Macro for Excel to Attachmate and Back Again

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.

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Macro for Excel to Attachmate and Back Again

(OP)

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

RE: Macro for Excel to Attachmate and Back Again

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

How do you explain that?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Macro for Excel to Attachmate and Back Again

(OP)
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

RE: Macro for Excel to Attachmate and Back Again

Well what about

CODE

Sheet2.Cells(rw1, 6) = SubGroup
???

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Macro for Excel to Attachmate and Back Again

(OP)
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.

RE: Macro for Excel to Attachmate and Back Again

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Macro for Excel to Attachmate and Back Again

(OP)
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.

RE: Macro for Excel to Attachmate and Back Again

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

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Macro for Excel to Attachmate and Back Again

(OP)
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.

RE: Macro for Excel to Attachmate and Back Again

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Macro for Excel to Attachmate and Back Again

(OP)
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

RE: Macro for Excel to Attachmate and Back Again

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Macro for Excel to Attachmate and Back Again

(OP)
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.

RE: Macro for Excel to Attachmate and Back Again

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Macro for Excel to Attachmate and Back Again

(OP)
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

RE: Macro for Excel to Attachmate and Back Again

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: How to use the Watch Window as a Power Programming Tool

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Macro for Excel to Attachmate and Back Again

(OP)
Expression: PO Value: Nothing, Type: Object

RE: Macro for Excel to Attachmate and Back Again

Again...
Are you coding in Excel VBA or Extra VB?

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Macro for Excel to Attachmate and Back Again

(OP)
Microsoft Excel VBA

RE: Macro for Excel to Attachmate and Back Again

What about the value in PO on Debug/BREAK?

How about the value in X?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Macro for Excel to Attachmate and Back Again

(OP)
I realized that object was the wrong variable type to have PO set for as there are some alphanumeric values within the column. I've changed PO to a variant variable and set the range for A1:A10000 from "Groups" sheet. Now I'm having an error of type mismatch at

CODE --> 

Sess0.Screen.PutString PO, 2, 6
How should this be re-formatted to paste the value from the array?

X value is 2, Type: Variant/Long. PO is set with blank value currently and Type: Variant/Variant (1 to 10000, 1 to 1)

RE: Macro for Excel to Attachmate and Back Again

You should not have PO declared as an array unless you intend to read the entire Groups table into the PO array BEFORE you do anything with Attachmate or you need super fast processing after getting the entire array, except for the fact that screen scraping is a relatively SLOW process, where your program has to WAIT for the mainframe to respond each time you SendKeys.

The reason for a missmatch is that you have referenced the entire array, rather than a specific element of the array. As I previously stated, an array is not necessary here.

Lets start near the beginning. What are some TYPICAL values of PO? Can PO containg numbers with leading zeros?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Macro for Excel to Attachmate and Back Again

(OP)
PO is a 6 byte alphanumeric value already preformatted with the padding of 0's (via an earlier macro) in front of any values. For example, if the cell originally shows 12 then it is formatted to be 000012 as the value being entered into Attachmate needs this format. Alphanumeric values would typically look like A47XH prior to formatting and then 0A47XH after.

RE: Macro for Excel to Attachmate and Back Again

Then PO must be declared as STRING.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Macro for Excel to Attachmate and Back Again

(OP)
It seems it's still not pulling the data into Attachmate though. X is now showing Empty, PO now shows "" for the value, and Sheets("Groups").Cells(X,1).Value has <Application-defined or object-defined error>.
Trim() was removed from the last expression as I was receiving an object required error.

RE: Macro for Excel to Attachmate and Back Again

Quote:

For example, if the cell originally shows 12 then it is formatted to be 000012

THAT won’t work. If the cell contains 12, regardless of the FORMAT, the value is 12!

Here’s what you need...

CODE

PO = Format(Sheets("Groups").Cells(X, 1)).Value, “000000”)

Please replace my iPad QUOTES “” with PC QUOTES.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Macro for Excel to Attachmate and Back Again

(OP)
When I DEBUG/Break I'm getting the correct values for PO w/ the expression and X but no transfer into Attachmate.

CODE --> 

With Worksheets("Workbook")
    Do
       For X = rw To ActiveSheet.Rows.Count
         PO = Format(Sheets("Groups").Cells(X, 1).Value, "000000")
         If PO = "" Then Exit Sub
         Sess0.Screen.MoveTo 3, 8
         Sess0.Screen.PutString PO, 2, 6
'This last line is where data should be transferring over

RE: Macro for Excel to Attachmate and Back Again

Do you have ONLY ONE session going?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Macro for Excel to Attachmate and Back Again

(OP)
Yes, I believe I figured it out. By removing the 2, 6 from Sess0.Screen.PutString PO, 2, 6 it has pasted it into Attachmate but only loops for the first value from "Groups". It looks like it pasted the next value but did not register it when it moved to the next screen.

RE: Macro for Excel to Attachmate and Back Again

(OP)
Hey Skip,
I just changed up the order in which the code was executed and it seems to be working perfectly now. Here is what I ended up with:

CODE --> 

Sub DataExtract()
Dim Sessions, System As Object, Sess0 As Object, PO As String, WB As WorkBook, WS As Worksheet
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
Set WB = ActiveWorkbook
Set WS = Sheets("Groups")
g_HostSettleTime = 1000
        rw = 2
        rw1 = 1
        With Worksheets("Workbook")
            Do
             For X = rw To ActiveSheet.Rows.Count
                PO = Format(Sheets("Groups").Cells(X, 1).Value, "000000")
                If PO = "" Then Exit Sub
                
                
                Sess0.Screen.MoveTo 5, 22
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
                Sess0.Screen.SendKeys ("CG")
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
                Sess0.Screen.SendKeys ("<Enter>")
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
                Sess0.Screen.MoveTo 3, 8
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
                Sess0.Screen.PutString PO, 2
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
                Sess0.Screen.SendKeys ("<Enter>")
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
                    Do
                        For r = 11 To 22
                        dd = 1
                        S = Trim(Sess0.Screen.GetString(r, 3, 2))
                            If S = "" Then
                            Exit Do

                            Else
                            CaseNumber = Sess0.Screen.GetString(3, 8, 6)
                            GroupName = Sess0.Screen.GetString(6, 17, 40)
                            EDate = Sess0.Screen.GetString(r, 51, 8)
                            Fund = Sess0.Screen.GetString(r, 44, 1)
                            Plan = Sess0.Screen.GetString(r, 18, 18)
                            SubGroup = Sess0.Screen.GetString(r, 7, 10)
                            PLine = Sess0.Screen.GetString(r, 38, 4)
                            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)

                    Loop
        
                Sess0.Screen.SendKeys ("<Pf2>")
    
            Next X  'next row/group
        Loop
        End With

End Sub

RE: Macro for Excel to Attachmate and Back Again

Great!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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