Attachmate Screen Scrape To Excel Does Not Loop or Page Properly
I have a screen scrape that works, but not very efficiently. The complete of data that I am trying to scrape is made up of 2 rows of data and I would like to get it into 1 linear row within Excel. I have tried the Offset function, but I cannot get it to work (I end up creating object, mismatch, etc. errors). Also, I do not know how to eliminate empty rows of data; my scrape is set to retrieve host rows (hr) 9 to 20 and by doing this I collect empty rows of data. My paging is also set to 1 to 5 and this too causes me to collect even more rows of empty data (there is a '+' at Host position 21, 79 to indicate there is no more pages)WTY_LIMITS_CAPTURE_v1.xls . Can someone assist me fixing this ( offset the data into 1 linear row, eliminating the empty row and paging properly)?
Attached is my Excel spreadsheet with the code that I am using.
Attached is my Excel spreadsheet with the code that I am using.
ASKER
Thanks for the quick response. The code is designed to log onto the our mainframe and run the CICS application. I will attempt to do as you suggested.
I have also attached a couple of screen shots of the application that I am dealing with. The boxed red rectangle is information for 1 entry and that is what I am trying to offset into 1 linear row. Also, iBeginning-Page.docxs there a snip of code that if the '+' symbol on the right bottom of the screen is blank then do not run the script, but end and go the next item to enter? I was wondering if there is an 'If-Then' type of code that might work to end and go to the next item? (That would remove my need for the page snips.)
I have also attached a couple of screen shots of the application that I am dealing with. The boxed red rectangle is information for 1 entry and that is what I am trying to offset into 1 linear row. Also, iBeginning-Page.docxs there a snip of code that if the '+' symbol on the right bottom of the screen is blank then do not run the script, but end and go the next item to enter? I was wondering if there is an 'If-Then' type of code that might work to end and go to the next item? (That would remove my need for the page snips.)
5. It looks like your code is only processing 12 of the 21 fields in the paired rows.
6. I would expect to see something like the following if you were processing the line pairs.
8. What is the purpose of the Delay? (may be taken care of when you get a clean compile)
9. I formatted your code and added some statements to parse the second line of each pair for as much data as would supply a model.
Note: the hr + 1 lines
Note: the no data comments
Note: the Application.ScreenUpdating
6. I would expect to see something like the following if you were processing the line pairs.
For hr = 9 To 20 Step 2 'Repeat for each line pair in detail list8. What is the purpose of the Delay? (may be taken care of when you get a clean compile)
9. I formatted your code and added some statements to parse the second line of each pair for as much data as would supply a model.
Note: the hr + 1 lines
Note: the no data comments
Note: the Application.ScreenUpdating
r = 3
Application.ScreenUpdating = False
For Each Cell In [FRF_CODE].Cells 'Input for FRF
myScreen.putstring (" "), 3, 15 'FRF Code
myScreen.putstring (Cell), 3, 15 'FRF Code
'' myScreen.putstring (" "), 6, 25 'PN suffix
'' myScreen.putstring (Cell.Offset(0, 1)), 6, 25 'PN suffix
myScreen.SendKeys "<Enter>"
'?Delay
For p = 1 To 5
'?Delay
For hr = 9 To 20 Step 2 'Repeat for each line pair in list
If Trim(myScreen.GetString(22, 27, 17)) = "NO MORE DATA FOUND" Then Exit For
Call TK_WaitForCursor(2, 15)
Delay
Cells(r, 1).Value = Trim(myScreen.GetString(3, 15, 5))
Cells(r, 2) = Trim(myScreen.GetString(hr, 3, 1))
Cells(r, 3) = Trim(myScreen.GetString(hr, 7, 1))
Cells(r, 4) = Trim(myScreen.GetString(hr, 9, 7))
Cells(r, 5) = Trim(myScreen.GetString(hr, 19, 7))
Cells(r, 6) = Trim(myScreen.GetString(hr, 27, 1))
Cells(r, 7) = Trim(myScreen.GetString(hr, 31, 6))
Cells(r, 8) = Trim(myScreen.GetString(hr, 41, 6))
Cells(r, 9) = Trim(myScreen.GetString(hr, 51, 3))
Cells(r, 10) = Trim(myScreen.GetString(hr, 56, 6))
Cells(r, 11) = Trim(myScreen.GetString(hr, 65, 1))
Cells(r, 12) = Trim(myScreen.GetString(hr, 69, 1))
Cells(r, 13) = Trim(myScreen.GetString(hr, 73, 5))
Cells(r, 14) = Trim(myScreen.GetString(hr + 1, 9, 7))
Cells(r, 15) = Trim(myScreen.GetString(hr + 1, 18, 8))
Cells(r, 16) = Trim(myScreen.GetString(hr + 1, 27, 1))
Cells(r, 17) = Trim(myScreen.GetString(hr + 1, 31, 6))
Cells(r, 18) = Trim(myScreen.GetString(hr + 1, 41, 6)) 'no data
Cells(r, 19) = Trim(myScreen.GetString(hr + 1, 51, 3)) 'no data
Cells(r, 20) = Trim(myScreen.GetString(hr + 1, 56, 6)) 'no data
Cells(r, 21) = Trim(myScreen.GetString(hr + 1, 65, 1)) 'no data
Call TK_WaitForCursor(2, 15)
r = r + 1 'Next Excel row
Next hr
myScreen.SendKeys "<Right><PF8>"
'?Delay
Call TK_WaitForCursor(2, 15)
Next p
Next 'Cells
Application.ScreenUpdating = TrueASKER
I have turned on the Option Explicit or the Required Variable Declaration statement on via Options and complied my code. Attached is the result of the changes. The only issues that remain are 1) ending/paging so that there are no blanks rows or 2) duplicated values. I boxed in the issues. Thanks again for the assistance. Also, is there a site or document that I can review to understand more? For example, the use of 'Step 2' in the statement.WTY_LIMITS_CAPTURE_v2.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did not add them to the code yet (that is something I know I can do since you opened my eyes to 'Step 2'). I'll run the code and let you know what happens. What are your thoughts on using Do-Until or something like that for paging or not scraping with rows with no data?
What are your thoughts on using Do-UntilThat would be my preference. However, I'm not sure I understand your outermost loop. So, that 'preference' is guarded advice.
...or not scraping with rows with no data?That is what this is supposed to do:
If Trim(myScreen.GetString(hr, 3, 1)) = " " Then
Exit For
End IfASKER
That could be my issue; the 'GetString(hr, 3, 1))' is the wrong reference. Can I join/add/reference to HOST rows (hr) together and if together they are null/blank/" " then exit the For?
You can alter the If statement condition to do any check you need for an 'empty'/'missing'/'blank' entry. I used a space character in the AFF data position.
ASKER
Well, I've tried these combinations and still getting duplication of the last page with its empty rows:
1)
2)
1)
If IsEmpty(Trim(myScreen.GetString(hr, 9, 7))) + IsEmpty(Trim(myScreen.GetString(hr + 1, 9, 7))) Then Exit For2)
If Trim(myScreen.GetString(hr, 9, 7)) = " " + Trim(myScreen.GetString(hr + 1, 9, 7)) = " "Then Exit ForIf Trim(myScreen.GetString(hr, 9, 7)) = " " + Trim(myScreen.GetString(hr + 1, 9, 7)) = " "Then Exit ForSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
AWESOME! SUCCESS!! So Len() is looking for a numerical value and if the two rows equal 0 then we exit the For. Thanks again for the help and making me learn something new!
Len() returns the length of the string. If the result of trimming the concatenated strings has a zero length, then both of the strings must contain only space characters.
ASKER
I was able to get the code wot work; thanks again!
That's great. Time to close the question
ASKER
If I messed up, I am sorry. Could you instruct/tell me what I need to do?
2. Add an Option Explicit statement in every general declarations sections of all modules and forms and compile your code. Define your variables and recompile until you get a clean compile.
3. Beware that you are using at least one VB instrinsic function word, Str as a parameter/variable name.
4. You should probably create a text file containing the results of your attachmate method results. We might be able to fake it in order to see what your code is seeing.