Number: tb0019

Availability: As of BuildPro 8.00 on all platforms

Introduction

Many BuildPro applications provide list output of data for further analysis by products like Microsoft Excel. This page contains information on how to build generic macros to analyze this type of data.
 

Creating a List Output File

The easiest method to prepare data for further analysis in Excel is by creating a comma delimited ASCII file from BuildPro. In this example our BuildPro application created a file containing the following sales data:

Division,Sales,Cost,Profit
A,123,34,123
A,234,345,234
A,345,23,322
B,456,435,21
B,567,56,511
B,56,123,-67

Naming this file with the extension .csv and then executing this file using the SYSTEM *UI command will automatically bring up Excel. Excel will automatically expand the data into several columns.

Formating the Data

Before letting the user take over, you may want to build a series of automatic or optional macros to post-format the list data. Since the data is in Excel for the user's ability to privately analyze this data you will want to limit the amount of post-formatting. A few generic formatting options are:

• Selecting the Data Region so that the user can automatically use all the Excel data functions.
   
• Highlight the column headers.
   
• Provide AutoFilter.
   
• Resize all column widths.
   
• Create named regions so that the user can automatically apply his pre-defined formulas.

Variable Length Data

It can be tricky to name a region of data that in one list might be 10 rows an in another list 10,000 rows long. In Excel use the xlDown option to automatically select data regions based on actual contents.

For example to name a region (from cell C5 and below) use VB code similar to the following:

Range("C5").Select
Set AreaToName = Range(ActiveCell, ActiveCell.End(xlDown))
Address = AreaToName.Address ' Convert range to text string.
ActiveWorkbook.Names.Add Name:="SalesData", RefersTo:= _
"=" & Address

By providing a named region, the user can simply refer to the relevant numbers in simple generic formulas:

=SUM(SalesData)

=AVG(SalesData)

Excel Example

We have created a simple Excel document that illustrates the use of macros to post format list data. Download it here (need to save a local copy before running the macros).