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

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
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,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Macro for Excel to Attachmate and Back Again
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
In the Excel VBA editor, you have features that make debugging your code a lot easier. That’s the first recommendation I’d offer.
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,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Macro for Excel to Attachmate and Back Again
CODE -->
RE: Macro for Excel to Attachmate and Back Again
How do you explain that?
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Macro for Excel to Attachmate and Back Again
CODE -->
RE: Macro for Excel to Attachmate and Back Again
CODE
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Macro for Excel to Attachmate and Back Again
RE: Macro for Excel to Attachmate and Back Again
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
RE: Macro for Excel to Attachmate and Back Again
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
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
RE: Macro for Excel to Attachmate and Back Again
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
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
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
RE: Macro for Excel to Attachmate and Back Again
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 -->
RE: Macro for Excel to Attachmate and Back Again
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
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
RE: Macro for Excel to Attachmate and Back Again
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 -->
CODE -->
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
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Macro for Excel to Attachmate and Back Again
CODE -->
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
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
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Macro for Excel to Attachmate and Back Again
RE: Macro for Excel to Attachmate and Back Again
Are you coding in Excel VBA or Extra VB?
CODE
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Macro for Excel to Attachmate and Back Again
RE: Macro for Excel to Attachmate and Back Again
How about the value in X?
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Macro for Excel to Attachmate and Back Again
CODE -->
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
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,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Macro for Excel to Attachmate and Back Again
RE: Macro for Excel to Attachmate and Back Again
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Macro for Excel to Attachmate and Back Again
Trim() was removed from the last expression as I was receiving an object required error.
RE: Macro for Excel to Attachmate and Back Again
THAT won’t work. If the cell contains 12, regardless of the FORMAT, the value is 12!
Here’s what you need...
CODE
Please replace my iPad QUOTES “” with PC QUOTES.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Macro for Excel to Attachmate and Back Again
CODE -->
RE: Macro for Excel to Attachmate and Back Again
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Macro for Excel to Attachmate and Back Again
RE: Macro for Excel to Attachmate and Back Again
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 -->
RE: Macro for Excel to Attachmate and Back Again
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein