Output to Excel

Diagram: NOTE: This feature requires Thoroughbred Gateway for Windows.

There are two ways to output to Excel:

Select EXCEL as the printer device. This capability is only available from Report-IV.
Write a method that calls Report-IV setting “T” to the output type and also setting entry 6 to the name of the file to be used for the tab-delimited file that Report-IV generates.

Each time Excel is selected for output, a new Excel workbook is created. To change workbooks select the appropriate Microsoft Excel Book in the task bar; or from the Excel menu bar select the Window option and then select the appropriate Book (window).

Setting Up the EXCEL Printer Device

The Thoroughbred Environment includes an EXCEL printer table. From the System Administration menu select Printer Assignment. For more information see the Dictionary-IV Administrator Guide. Assign any printer device (i.e. RS, LP, P1) to the EXCEL printer table. The recommended printer device is RE. You will then need to modify your IPLINPUT file to include this printer device. For more information see the Thoroughbred Basic Customization and Tuning Guide.

IPLINPUT for Windows Basic:

DEV RE,4,,,,,,NULL

IPLINPUT for UNIX:

DEV RE,4,,,1,,,/dev/null

Creating a Method

Create a Method to perform the following:

Create and open a text file.
Call OOR (CONNECT REPORT) to execute the Report-IV definition directing output to a tabbed export file.

OOR$[1]= name of the Report-IV definition.
OOR$[6]= “CH:” + text file channel.
OOR$[7]=”T” specify output to a tabbed export file.
Call OO3AXPRT to read the text file and send the contents to Excel.

OO3A$[1]= “E” specifying output for Excel. OO3A$[1]=text_file_name

For example:

PROCEDURE
       WINDOW CREATE (80,24,0,0);
       DIM OOR$[10];
       CLEAR ERC;
       CHP=UNT;
       OPEN (CHP) ".";                        ! Find
       PFX$=FID(CHP),                         !  directory number
       PFX=ASC(PFX$(20,1));                   !  of prefix directory
       CLOSE (CHP);
       OUT$="SendToExcel";                    ! export file name
       WHILE ERC =0;                          ! erase
          ERASE OUT$,ERC=12                   !  all copies
       WEND;                                  !  of the file
       CLEAR ERC;                             ! clear error control
       TEXT OUT$,PFX,0;                       ! create export file
       OPEN (CHP,OPT="TEXT") OUT$;            ! open output channel*
       OOR$[1]="RRRCUST",                     ! set report name
       OOR$[6]="CH:"+STR(CHP),                ! set output to channel
       OOR$[7]="T";                           ! set Export Tabbed file*
       CALL "OOR",OOR$[ALL];                  ! execute report
       CLOSE (CHP);                           !
       IF OOR$[0]="."                         ! if good report
          DIM OO3A$[1];                       !
          OO3A$[0]="E",                       !    set excel
          OO3A$[1]=OUT$;                      !    set export file name
          CALL "OO3AXPRT",OO3A$[ALL]          !    send to excel*
              FI;

From the Report-IV Reference, version 8.9.1. Open this page in the PDF