Macro for Excel to Attachmate and Back Again 1

[CADD6953]

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 ):

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

 

[CADD6953]

Expression: PO Value: Nothing, Type: Object

 

[SkipVought]

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

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[CADD6953]

Microsoft Excel VBA

 

[SkipVought]

What about the value in PO on Debug/BREAK?

How about the value in X?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[CADD6953]

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

                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)

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[CADD6953]

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.

 

[SkipVought]

Then PO must be declared as STRING.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[CADD6953]

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.

 

[SkipVought]

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

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

Please replace my iPad QUOTES “” with PC QUOTES.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[CADD6953]

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

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

 

[SkipVought]

Do you have ONLY ONE session going?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[CADD6953]

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.

 

[CADD6953]

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:

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

 

[SkipVought]

Great!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein