Using a Script to Capture Data for Insertion into a Spreadsheet

Introduction

BlueZone can be scripted to automatically capture screen data which can be copied to the Windows clipboard and then pasted into a spreadsheet. This Application Note provides a fully functional script that can be modified to match the area of the screen that your application uses to display the data than needs to be captured. Also, the page down command and “end of file” string may have to be edited as well.

Objective

The objective of this script is to allow a BlueZone End User to be able to automatically select screen data from a host application, regardless of how many pages long, and paste that data into a Windows spreadsheet application for further analysis.

Also, this Application Note can also be used as a BlueZone Scripting training exercise.

What the Script Does

The script selects (highlights) the area of the screen that contains the desired data. Then the script copies the contents of the selected area to the Windows clipboard. Next, the script automatically pages down to the next screen of data, and appends the data from that screen to the Windows clipboard. Now the script loops so that any additional pages of data, regardless of how many, are also appended to the Windows clipboard. When the script sees the end of the data, the script will capture one final screen of data, even if it’s a partial screen, and append it to the Windows clipboard. Then, the selected area is de-selected, Microsoft Excel is launched, and the script is ended.

Now you can “paste” the captured data from the Windows clipboard into the Excel spreadsheet application using a standard Windows paste command. Since BlueZone has a feature called “Cell Delimited Formatting for Spreadsheet Paste”, the data will be inserted as separate cells in the spreadsheet.

Requirements

• BlueZone Version 3.2 or higher installed and running on a workstation
• The following sample BlueZone script - bz_script1.bzs (click to download, and save to your BlueZone scripts folder)
• A Windows spreadsheet application installed and running on the same workstation

Configuring the BlueZone Emulation Client

1. Launch the BlueZone Display Emulation Client.
2. On the BlueZone MenuBar, select Edit:Properties. The following dialog will be displayed:

3. Make sure the “Auto-Deselect after Copy” checkbox is NOT checked.
4. Select the Copy To Clipboard tab. The following dialog will be displayed:

5. Match your settings to the settings shown above.
6. Select the Paste from Clipboard tab.

7. Match your settings to the settings shown above and click the OK button.
8. On your host, navigate to the application that displays the data that you wish to capture.

Download and Save the Sample Script

1. Download the sample script (bz_script1.bzs), and copy it to the “Scripts” folder in your BlueZone Working Directory. The location of the BlueZone Working Directory is based on End User’s Windows Temp Variable.

 Click here to download the sample script

NOTE This script was written as a Mainframe script. To convert it to an iSeries (AS/400) script, change the “Mode” located on the MenuBar of the BlueZone Script Editor.

Launching the BlueZone Script Editor

Edit the sample BlueZone Script by selecting Script:Edit from the BlueZone MenuBar, then choosing “bz_script1.bzs” from the list and clicking the Open button.

The BlueZone Script Editor will launch as shown here:

The BlueZone Script Editor is a GUI application that allows you to create and edit BlueZone scripts using a standard Windows “drag and drop” or “cut and paste” interface. Since we are editing an existing sample script, you do not have to create the script from scratch. You will be editing this script so that it plays properly in your host application.

The Sample Script Explained

Watch_For - Take a look at the sample script, you will see that a Watch_For event is placed at the beginning of the script. This tells the script to Watch_For a string of 10 asterisks. I chose this because the only palce that a string of at least 10 asteriks appears is at the end of the data. When BlueZone “sees” the string, it will Goto “OneMore”.

Tab & Cursor Right - After the Watch_For event, the script contains the number of cursor movement commands to move the cursor from the “rest” position, to the place where my data starts.

TIP Use the Script Editor’s scroll bar to move down the script.

Edit::Select Right & Edit::Select Down - After the cursor movement commands, the script will start to select (highlight) the potions of the screen that contains the data.

Edit::Copy to Clipboard - Once the data is selected, the script issues a Edit::Copy to Clipboard command. This copies the selected data to the Windows clipboard.

PF08 - The script then issues a “page down” command so that the next screen of data can be captured. The area of the screen that is selected will remian selected until the script issues a Select Cancel command. In the sample application, a “page down” is issued by pressing the F8 key. Your application may use a different command for “page down”.

Loop: - The next item is a Goto Label called “Loop”. A Goto Label is a place in the script that can be “called out” elsewhere in the script. When the script encounters a Goto command, the script will “goto” that part of the script and continue from that spot. Goto’s are particularily useful when you want to repeat an action many times.

Edit::Append to Clipboard - The next item in the script is Edit::Append to Clipboard. This causes the area of the screen that is currently selected, to be appended to the clipboard. It does not overwrite the data that is already in the clipboard but adds this screen of data to the end of the data that is already in the clipboard.

PF08 - The next item is PF8, page down. This displays the next page of data.

Goto Loop - The next item is a Goto command which causes the script to Goto “Loop”. When thescript gets to this point, it goes to the label “Loop” and runs the script from that point. When the script gets to “Loop”, the script will capture and append the selected data to the clipboard (Edit::Append to Clipboard) then issue a page down command (PF08) to get ready for the next screen of data. Then the script will encounter the Goto Loop again, and the process will start over. This is why the script will automatically capture the entire data set regardless of how many screens of data there are.

If you’re observant, you will notice that this creates an infinite loop. An infinite loop is one that will run forever unless something happens to interrupt the script. In this script, we set a Watch_For script event at the top of the script. This Watch_For will interrupt the loop when the Watch_For condition is met. The condition is a text string that contains 10 asterisks. When the script sees the 10 asterisks, the Watch_For will issue a Goto “OneMore”.

OneMore: - When the Watch_For condition is met, the script will execute a Goto “OneMore”. “OneMore” will append the last screen of data the clipboard.

Edit::Select Cancel - This will de-select that capture area of the screen so that you don’t have to do it manually.

Run “C:\Program Files\Microsft Office\Office\Excel.exe - This will launch Microsoft Excel. This is optional.

Beep - This will issue a beep tone to let you know that the script has completed. This is optional.

Script Stop - This will end the script.

Editing the Sample Script

Selecting the Data Area to be Captured

Since the data area of the sample screen will most likely not match the data area of your screen, it may be necessary for you to modify the sample script to work with your application. The objective here is to define and select (highlight) just the area of the screen that contains the data that you wish to capture, so that the script will do it automatically.

1. On your screen, with the cursor at the “rest” position, determine the number of keystrokes it will take to move the cursor from the command line to the first column and row of data that you wish to capture.

For instance, in the sample application, it took 8 “Tabs” and 9 “Cursor Rights” to move the cursor from the “rest” position (the command line) to the place on the screen where the data begins, as shown in the following example:

2. If necessary, edit the sample script with more or less Tabs and Cursor Rights. To delete a Tab or Cursor Right, use the Delete key, to add a Tab or Cursor Right, use “Copy and Paste”. If you need to add a different cursor movement command, you can drag the item from the appropriate box on the right.
3. Determine the number of columns that need to be highlighted to the right. Use your right arrow key and count. In the BlueZone script, these movements are referred to as Edit::Select Right.
4. If necessary, edit the script with the correct number of Edit::Select Right commands.
5. Determine the number of rows that need to be highlighted going down. In the BlueZone script, these movements are referred to as Edit::Select Down.
6. If necessary, edit the script with the correct number of Edit::Select Down commands.

The following screen shot shows the data area selected (highlighted), and the cursor at the “rest” position.

Establising an End of File Marker

1. On your host application, go to the end of the data file and look for some unique text that marks the end of the data display. This unique text can then be inserted into the script as a Watch_For script event.

In the sample application, note the text line that is displayed at the end of the Data Set listing:

If your application is similar to the sample application, and you can use the 10 asteriks as an end of file identifier, proceed to Determining the Page Down Command.

If your application is different, look for a string of unique characters or words that can be used in the script.

NOTE In some applications, a message line is displayed that indicates when you are at the end of the file. This message can be utilized in the BlueZone script. It does not matter where on the screen the unique text is located. The Watch_For script event watches the entire screen, not just the selected (highlighted) area, for the condition to be met.

Determining the Page Down Command

1. Determine what your application uses as a “page down” command. In the sample application, F8 is the “Page down” command. If your application requires a different command, you will have to edit the script with the appropriate command.
2. If necessary, edit the script by deleting the PF08 command and replacing the command used by your application. You can drag the appropriate command from the LU Functions box.

Testing the Modified Script

1. Test the BlueZone script, first select Script:Properties from the BlueZone MenuBar. The Script Properties dialog will display.

2. With your mouse pointer, move the Playback Speed slider to the position shown above. This will slow the playback speed of the script to the point where you can see exactly what is happening in real time.
3. Click the OK button.
4. To run the script, select Script:Play from the BlueZoneMenuBar. Choose bz_script1.bzs and click the Open button. The script will immediately start running.

Watch the script highlight your data. If you see that the cursor position is off or the data is not being highlighted correctly, you can stop the script by clicking the Stop button on the Script Status progress bar display as shown:

If necessary, edit the script and add or delete the cursor movement commands until the highlighting is correct.

If your data is being highlighted correctly, and you have a unique text string in the Watch_For variable, and the script is using the correct page down command, the script should automatically capture all the data and store it in the Windows clipboard, launch Microsoft Excel and beep to indicate that the scrip has ended properly.

Now simply paste the data into the spreadsheet (Control-V).

Congratulations! You have successfully automated this function using BlueZone Scripting.

NOTE At this point you may wish to configure BlueZone so that you can launch this script by clicking a single button.

 How To Assign a BlueZone Script to a Power Key