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.
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
|Last Name: Doe
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:
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:
- 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.
- 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:
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:
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:
The following code has been edited for screen scraping:
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/.