Environment
Situation
This note describes ways you can use Visual Basic for Applications (VBA) to optimize your use of Reflection products and provides tips for using the Reflection Macro Recorder.
Resolution
Screen Scraping Simplified
Screen scraping in the context of this note means collecting data from the host session for use in a PC application or in a VBA macro. Complex screen scraping for the purposes of "front-ending" another application can require a major commitment in time and energy. This note describes one of the simplest cases of screen scraping: transferring the contents of host fields from the screen into a macro.
Screen data is simply ASCII characters at a specific row and column location on the terminal screen. Typically, host applications use escape sequences to position the cursor to the specific screen coordinates before sending data. A formatted data screen is a grid of words and numbers aligned into rows and columns. Often, a screen is arranged into alternating labels (field names) and values. For example:
| First Name: John ID: 456789 Phone: 555-1212 |
Last Name: Doe Dept: Forecasting FAX: 555-2121 |
Additional escape sequences might be used to add display attributes to some characters. For example, some data fields might be displayed as inverse video, while field names might be shown in bold. There are many ways to use display attributes, but the data itself is ASCII characters at specific locations on the display.
Revealing Screen Coordinates
To capture, or scrape, data from one screen before you move to the next, follow these steps to use the Reflection Macro Recorder:
- Click Macro > Start Recording.
- Use the mouse to select desired data. Remember that words or strings of words might be shorter for one record than for others. To select an entire field, you may need to extend your selection to include the entire field.
- Click Edit > Copy.
- Repeat this process for other data fields.
- Click Macro > Stop Recording. Save the recorded commands either to a new macro file or to the Clipboard, and then paste them into an existing script.
The result may look like the following:
'Generated by the Reflection Macro Recorder on 11-01-2001 'Generated by Reflection for UNIX and OpenVMSWindows Option Explicit Sub Macro1 With Session .SelectText 5, 12, 5, 23 ' Press EditCopy (Copy selected text to the Clipboard) .Copy rcSelection .SelectText 7, 38, 7, 42 ' Press EditCopy (Copy selected text to the Clipboard) .Copy rcSelection End With End Sub 'Recording stopped at 8:13:22.15. |
Reflection is doing exactly what you told it to do:
- SelectText highlights data from one point to another.
- Copy copies the selected data to the clipboard.
- rcSelection is a predefined constant that indicates what data to copy—in this case the current selection.
Editing the Macro
While the macro resulted from the automatic recording, you will need to edit the script to get the result you want. Note the following:
- SelectText and GetText accept similar parameters: StartRow, StartCol, EndRow, and EndCol for the desired text. GetText, however, returns a string. Therefore, it is necessary to define a new variable using the Dim statement to receive that data:
Dim customerName As String Dim customerID As String |
- Change SelectText to GetText, and then put parentheses around the screen coordinates. This is necessary because GetText returns a string; it is a function whose arguments must be within parentheses.
customerName = .GetText (5, 12, 5, 23) |
- Remove extraneous lines, if desired. Copy isn't required because you are saving the data in a variable. Comments (beginning with an apostrophe [']) can be removed or modified.
The final result would look like the following:
Sub Macro1 Dim customerName As String Dim customerID As String With Session customerName = .GetText(5, 12, 5, 23) customerID = .GetText(7, 38, 7, 42) End With End Sub |
The Macro Recorder makes the process of identifying screen coordinates much faster and easier for programmers.
Using With Session
With...End With is a BASIC construction that groups methods and properties from the same object, in this case Reflection's predefined Session object. Inside the With block, statements such as Session.GetText can be shortened to .GetText. The leading dot indicates GetText belongs to the object Session.
You can choose not to use the With...End With block. For example:
customerName = Session.GetText (5, 12, 5, 23) customerID = Session.GetText (7, 38, 7, 42) |
Visual Basic Commands
The Macro Recorder in Reflection for Windows-based products generates commands and comments that create and define an object data type, conveniently named Reflection, that points to Reflection as a COM object. If you save your recorded macro to the Clipboard and select Visual Basic as the Clipboard option, Reflection replaces Session in all commands that refer to Reflection's methods and properties, for example, With Reflection...End With.
Here's the first screen scraping example (above) recorded for Visual Basic:
'Generated by the Reflection Macro Recorder on 11-01-2001 'Generated by Reflection for UNIX and OpenVMS Option Explicit Sub Macro1 Dim Reflection As Object ' Create a new instance of Reflection Set Reflection = CreateObject("Reflection2.Session") ' Use the statement below instead to attach to an existing ' instance of Reflection. ' Set Reflection = GetObject(,"Reflection2.Session") ' Normally the Reflection session will be visible ' while this macro is running and will remain open when ' this macro terminates. ' Delete the following line if you want to be invisible and ' automatically close. Reflection.Visible = True Reflection.ProcessDatacomm = False ' Do not let Reflection process data comm between API calls. Reflection.SelectText 5, 12, 5, 23 ' Press EditCopy (Copy selected text to the Clipboard) Reflection.Copy rcSelection Reflection.SelectText 7, 38, 7, 42 ' Press EditCopy (Copy selected text to the Clipboard) Reflection.Copy rcSelection ' Resume normal datacomm processing. Reflection.ProcessDatacomm = True End Sub ' Recording stopped at 8:13:22.15. |
The following code has been edited for screen scraping:
Sub Macro1 Dim Reflection As Object Dim customerName As String Dim customerID As String Reflection = GetObject(,"Reflection2.Session") customerName = Reflection.GetText(5, 12, 5, 23) customerID = Reflection.GetText(7, 38, 7, 42) End Sub |
For more information about programming with Reflection, see the Reflection online help index. For training information, see Attachmate Training at https://www.attachmate.com/Products/Technical+Services/Training/.