Exporting Host Screen Data to Excel Using a Macro

Exporting Host Screen Data to Excel Using a Macro

Flex Terminal Emulator allows you to automate the extraction of data from host screens and export it directly into an Excel spreadsheet using a macro. This approach is useful for collecting structured data across multiple screens.

Overview

A macro can be used to:
  • Extract specific data fields from the current host screen
  • Write the data to an Excel spreadsheet
  • Navigate to the next host screen
  • Repeat the process for multiple screens

Sample Macro Code

The following example demonstrates how to retrieve data from defined screen positions and export it into Excel:
  1. Sub Main()

  3. ' Create the Excel objects
  4. Set objExcel = CreateObject("Excel.Application")
  5. objExcel.Visible = true
  6. Set objWorkbook = objExcel.Workbooks.Add

  8. nRow = 1
  9. nCol = 1

  11.   For i = 1 to 4
  12.     objExcel.Cells(nRow, nCol).Value     = FlexScreen.GetText (FlexScreen.Position(4, 2), 6)
  13.     objExcel.Cells(nRow, nCol+1).Value  = FlexScreen.GetText (FlexScreen.Position(4, 10), 20)
  14.     objExcel.Cells(nRow, nCol+2).Value  = FlexScreen.GetText (FlexScreen.Position(4, 31), 8)

  16.     objExcel.Cells(nRow+1, nCol).Value  = FlexScreen.GetText (FlexScreen.Position(5, 2), 6)
  17.     objExcel.Cells(nRow+1, nCol+1).Value= FlexScreen.GetText (FlexScreen.Position(5, 10), 20)
  18.     objExcel.Cells(nRow+1, nCol+2).Value= FlexScreen.GetText (FlexScreen.Position(5, 31), 8)

  20.     objExcel.Cells(nRow+2, nCol).Value  = FlexScreen.GetText (FlexScreen.Position(6, 2), 6)
  21.     objExcel.Cells(nRow+2, nCol+1).Value= FlexScreen.GetText (FlexScreen.Position(6, 10), 20)
  22.     objExcel.Cells(nRow+2, nCol+2).Value= FlexScreen.GetText (FlexScreen.Position(6, 31), 8)

  24.     objExcel.Cells(nRow+3, nCol).Value  = FlexScreen.GetText (FlexScreen.Position(7, 2), 6)
  25.     objExcel.Cells(nRow+3, nCol+1).Value= FlexScreen.GetText (FlexScreen.Position(7, 10), 20)
  26.     objExcel.Cells(nRow+3, nCol+2).Value= FlexScreen.GetText (FlexScreen.Position(7, 31), 8)

  28.     ' Move to the next host screen
  29.     Ok = FlexScreen.SendKeys(FlexKey.PF1)
  30.     Ok = FlexScreen.WaitForNoX()

  32.     nRow = nRow + 4
  33.   Next

  35. End Sub

How It Works

  • The macro creates and opens a new Excel workbook.
  • It reads data from specific positions on the host screen using FlexScreen.GetText.
  • Extracted values are written into Excel cells row by row.
  • After collecting data from one screen, it sends the PF1 key to navigate to the next screen.
  • This process repeats for a defined number of screens.

Notes and Customization

  • The sample assumes:
    • 4 rows of data per screen
    • 3 columns of data per row
    • Fixed screen positions for each field
  • The loop For i = 1 to 4 determines how many screens are processed.
    • Adjust this value to match the number of screens you want to export.
  • Modify the screen coordinates (Position(row, column)) and field lengths to match your application’s layout.
  • You can change the navigation key (PF1) if your host uses a different key to advance screens.

Saving and Using the Macro

To use this macro in your environment:
  1. Copy the sample code into the Flex Terminal Emulator macro editor, or any text editor (such as Notepad).
  2. Save the file with a .ams extension.
  3. Place the file in the Macros subfolder of your Flex Terminal Emulator user directory.
  4. Run the macro from within Flex Terminal Emulator.

Notes
This method provides a powerful way to automate data collection and eliminate manual data entry when working with multiple host screens.



    • Related Articles

    • Exporting a Host Screen to Microsoft Word

      Flex Terminal Emulator allows you to export the contents of a host screen directly into a Microsoft Word document using a macro. This is useful for documentation, reporting, or sharing screen data. Overview The macro performs the following actions: ...

    • Exporting a Host Screen to an Outlook Email

      Flex Terminal Emulator allows you to export the contents of a host screen directly into a new Microsoft Outlook email using a macro. This is useful for quickly sharing or documenting screen data. Overview The macro performs the following actions: ...

    • Recording a Login Macro and Running It Automatically at Startup

      Flex Terminal Emulator allows you to automate the login process by recording a macro and configuring it to run each time a session connects to the host. Step 1: Record a Login Macro Follow these steps to capture your login sequence: Open the Flex ...

    • Maximizing the Host Screen Area in Flex Terminal Emulator

      To increase the visible host screen area in Flex Terminal Emulator, you can hide or minimize certain interface elements. The following options can help you optimize screen space. Hide the Status Bar The Status Bar is displayed at the bottom of the ...

    • Supported Host Code Pages in Flex Terminal Emulator

      Flex Terminal Emulator supports a range of host code pages to ensure compatibility with different regional and language requirements. Available Host Code Pages The following host code pages are supported: 037 – US / Canada 273 – Austria / Germany 277 ...