Excel Automation

Hilmar Zonneveld, September 1, 2002

With Automation, you can control all aspects of Excel, Word, or other programs that provide this feature, from Visual FoxPro. In this article, I will concentrate on Excel. Its purpose is to provide a starting point, especially for people new to automation. Introduction With automation, you bas...

Introduction

With automation, you basically use the "object model" that a program like Excel "exposes", that is, makes available to other programs. Through this "object model", the programmer has access to Excel's worksheets, columns, rows, cells, formulae, graphics, etc. - all this, using a mixture of Visual FoxPro programming constructs, and Excel objects.

I often use Excel Automation as an alternative report format - a spreadsheet is generated, complete with column widths, borders, cell formatting, and formulae (so the user can experiment with the result of changes).

Requirements

First of all, Automation only works if Excel is installed. The idea is for Visual FoxPro to open Excel, and to control it, through what I sometimes call "remote control" - but the official name is "Automation".

I have tried the examples with Visual FoxPro 6, and Excel 2000. It will probably work with earlier versions of Visual FoxPro, and with Excel 97. But Excel 95 has a completely different programming language - in general, I would recommend to support only Excel 97 and later, in your programs.

Hello, World

Let's start with a simple test, that can serve as a starting point:

local oExcel, oSheet
oExcel = CreateObject("Excel.Application")
oExcel.Visible = -1   && for debugging
oExcel.Workbooks.Add()
oSheet = oExcel.ActiveSheet
oSheet.Cells(1,1).Value = "Hello, World!"

Explanation of the sample

The short sample above will open Excel, create a new worksheet, and insert a value into cell A1.

oExcel is a variable that will enable us to control Excel - if the CreateObject was successful. Once Excel is opened, we will control Excel through this variable.

oExcel.Visible = -1 will, of course, make Excel visible. If we omit this statement, Excel will be invisible. It is quite a lot faster to keep Excel invisible, insert values and do all the formatting, and make Excel visible when everything is ready. However, for debugging purposes, it may be better to see the results, step by step. -1 is used in Excel for true (it seems that other non-zero values can be used, too), and 0, for false.

The next two statements: oExcel.Workbooks.Add() oSheet = oExcel.ActiveSheet

will create a new workbook, and assign the active worksheet (the first worksheet in the workbook) to variable oSheet.

Finally, the last statement will insert the specified value into cell A1. The reference is in "row, column" format - both are specified as numbers, and the row is specified first.

While it is also possible to use the well-known "A1" syntax for cells, I don't recommend this for automation; it is much simpler to have numeric variables for both rows and columns. For instance, finding the next column after "AZ" (answer: "BA") is much more complicated than finding the next column after column number 52.

Save macros

One of the most important ways to get information about available Excel commands is to simply save a macro in Excel. I suggest you always try this before asking someone else: "What is the Excel syntax to do such-and-such an action?".

For instance, let's suppose we want to enhance the "Hello, World" sample, to show the cell in 20 points.

Just start by opening or selecting Excel, and saving a macro ("Tools | Macro | Save New Macro"), selecting "Ok", and then carrying out whatever action you want to analyze; in this case: click on the size selector (by default, the second element on the "Format" toolbar), type "20", and press Enter (or select "20" from the dropdown list). Finally, click on "Stop recording" on the macro toolbar that appears.

Now, to analyze the macro thus created, select "Tools | Macro | Macros". Select the macro you created (for instance, Macro1), and click on "Edit". Eliminating the macro name, and lots of comments, the relevant part in this case is:

With Selection.Font
   .Name = "Arial"
   .Size = 20
   ...

Since the font name and other formatting options are not part of the specification, this can be simplified to:

Selection.Font.Size = 20

"Selection" won't work in Visual FoxPro; we have to replace this either with a Cell object, or with a Range. I will explain ranges a little later. To apply the command to a Cell object, in our sample program, use something like:

oSheet.Cells(1,1).Font.Size = 20

Of course, in actual practice you will often use variables for the row and column, to process several cells in a loop.

Constants

When saving a macro, Excel will sometimes use constants that Excel understands, but Visual FoxPro doesn't. For example, for drawing cell borders, we might get the following macro in Excel:

With Selection.Borders(xlEdgeTop)
   .LineStyle = xlContinuous
   .Weight = xlMedium
   .ColorIndex = xlAutomatic
End With

The easiest way to deal with this is to get a ready-made list of constants. Just search the Universal Thread download section for "constants" - constant files exist both for Excel 97 (download #9498), and Word 97 (download #9409). These should work for Excel 2000 and Word 2000, too - since versions 97 and 2000 basically use the same language.

After downloading and uncompressing the file for the Excel constants, just include the following line at the beginning of your program (before the constants are used):

#INCLUDE xl97cons.h

Named Parameters

One problem that sometimes appears in the saved macros is that VBA (Visual Basic for Applications, the language used in MS-Office macros) understands "named parameters", while Visual FoxPro doesn't. Named parameters have the advantage that you need to specify only the parameters that you actually need - there is no need to fill in missing parameters. Also, you can specify the parameters in any order!

But this is no use when automating from Visual FoxPro. Fortunately, it seems that all the methods that use named parameters in saved macros also support an alternate syntax (parameter by position, as in Visual FoxPro). All we have to do is look up the corresponding parameter in the Excel help file. (Actually, it seems that in many cases, the named parameters are specified in the correct order, so you only have to delete the names.) Note: you may have to run the MS-Office installation, to add VBA Help to your computer.

As an example, save a macro to save the current worksheet as a dBASE III file. The relevant line in the macro is:

ActiveWorkbook.SaveAs Filename:="C:\My documents\test.dbf", FileFormat:= _
   xlDBF3, CreateBackup:=False

The problem here is the named parameters, not supported by Visual FoxPro. In the macro editor, clicking on the command "SaveAs" and pressing F1 reveals in what order you should specify the parameters (when not using named parameters), and the meaning, type and options for each parameter.

The final result, in this case, is the following Visual FoxPro code:

oSheet.SaveAs("c:\My documents\test.dbf", xlDBF3)

since in this case we can omit the additional parameters, like password, etc.

Formulae

When saving macros, Excel saves formulae in the R1C1 format. Once again, since you will very likely use variables for rows and columns, I recommend to use this format, and not the alternative "A1"-style formulae. For instance, the following macro was from a formula that multiplies the cell above the current cell (that is, a relative reference), with the (absolute) cell A1:

ActiveCell.FormulaR1C1 = "=R[-1]C*R1C1"

Of course, "ActiveCell" should, once again, be replaced with a Cell object.

Note that in Excel, all formulae must needs start with "=".

The second part, R1C1, is an absolute reference, to row 1, column 1. (Relative vs. absolute references are explained in the Excel help. In actual practice, you usually need both, although relative references are more common.)

In actual practice, you will often have to construct the formula in pieces. For instance, if instead of "[-1]" you need a variable offset, the above might become:

lnOffset = 5
lcFormula = "=R[-" + alltrim(str(nOffset)) + "]*R1C1"
.Cells(nRow, nCol).FormulaR1C1 = lcFormula

(In this and a few other examples, with oSheet is implied. See the Visual FoxPro help for WITH if you are not familiar with this statement.)

Ranges

Often, you can apply a format to a whole group of cells at a time. This may, or may not, be less code to write; but mainly, it is likely to execute much faster.

The following Visual FoxPro example will change all cells in the rectangular area from A1:E5, to 20 points:

.Range(.Cells(1,1), .Cells(5,5)).Font.Size = 20

Creating a graph

As an example of how to apply the different principles explained previously, we will create a simple graph. I chose to create a line graph, with four data points.

Saving a macro, Excel produces the following code:

Range("A1:B4").Select
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("Hoja1").Range("A1:B4"), PlotBy:= _
   xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Hoja1"
With ActiveChart
   .HasTitle = True
   .ChartTitle.Characters.Text = "Sample Automation Graph"
   .Axes(xlCategory, xlPrimary).HasTitle = True
   .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
   .Axes(xlValue, xlPrimary).HasTitle = True
   .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales"
End With

ActiveChart.HasLegend = False
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowNone, LegendKey:=False

After adding some additional code, mainly to define constants and insert values into the spreadsheet, changing "Hoja1" to oSheet.Name to make the macro independent of the fact that I happen to have the Spanish version of Excel, and some experimenting (for instance, when should we prepend oExcel, when oSheet?), we get the following VFP program: #INCLUDE xl97cons.h #define xlTrue -1 #define xlFalse 0 local oExcel, oSheet oExcel = CreateObject("Excel.Application") oExcel.Visible = -1 && for debugging oExcel.Workbooks.Add() oSheet = oExcel.ActiveSheet oSheet.Cells(1,1).Value = "Jan" oSheet.Cells(1,2).Value = 10 oSheet.Cells(2,1).Value = "Feb" oSheet.Cells(2,2).Value = 15 oSheet.Cells(3,1).Value = "Mar" oSheet.Cells(3,2).Value = 12 oSheet.Cells(4,1).Value = "Apr" oSheet.Cells(4,2).Value = 10 oSheet.Range("A1:B4").Select oExcel.Charts.Add With oExcel.ActiveChart .ChartType = xlLine .SetSourceData(oSheet.Range("A1:B4"), xlColumns) .Location(xlLocationAsObject, oSheet.Name) endwith with oExcel.ActiveChart .HasTitle = xlTrue .ChartTitle.Characters.Text = "Sample Automation Graph" .Axes(xlCategory, xlPrimary).HasTitle = xlTrue .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month" .Axes(xlValue, xlPrimary).HasTitle = xlTrue .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales" .HasLegend = xlFalse .ApplyDataLabels(xlDataLabelsShowNone, xlFalse) EndWith oSheet.Parent.Saved = xlTrue

With Automation, you can control all aspects of Excel; but sometimes this can be slow. You won't have problems for the equivalent of one or two pages, but if you have to transfer many thousands of records, things can get very slow. For instance, it is much, much faster output data to a text file in a loop, or using a command like COPY TO ... TYPE XLS, than to output the same amount of cells in a loop, through Automation. The following tips can help you make your Automation faster.

• Make sure you have enough RAM, so that Windows doesn't need virtual memory to run both Visual FoxPro and Excel at the same time.
• As suggested previously, applying a format to a range, instead of individual cells in a loop, should speed things up. The reason is that if you use a range, Visual FoxPro needs to send only a single message to Excel - and the "sending messages" part seems to be the main bottleneck most of the time.
• For formulae, and other repetitive data, automating copy-and-paste should be much faster than inserting data to lots of individual cells in a loop.
• One way to insert a large amount of data is to COPY TO ... TYPE XLS (or FoxPlus, or DELIMITED) from Visual FoxPro, and then read the data with a single command from Excel. Of course, this can be automated, too.
• Another way to insert lots of data into Excel is to prepare a variable that contains data with adjacent cells in Visual FoxPro, assigning it to the _ClipText variable, and automating the "Paste" command in Excel. Separate cells with the tab character, chr(9), and rows with a new line, chr(13) + chr(10).
• If you do process cells in a loop, it may help to skip automation for cells that you don't need to process. For instance, if a certain field doesn't have a value, skip that cell - don't insert a zero.

Note that most of the time, the critical factor you should consider is the number of commands Visual FoxPro gives to Excel. Most of the recommendations I gave above are intended to reduce this.

Additional considerations

• To mark a spreadsheet as not modified, use the following command (assuming our initial example): oSheet.Parent.Saved = xlTRUE (where xlTRUE is defined as -1). "Parent" refers to the file (workbook), which is the parent of the current spreadsheet. Thus, the user can close the file without additional dialogs - but if the user makes additional changes, he will, quite logically, be asked whether he wants to save it.
• For currency values, I usually use syntax like mton(MyCurrencyField), to convert to a normal number. This avoids the currency value being shown with the currency symbol. It also avoids rounding problems, which you may want to avoid - apparently, when transferring data, Excel rounds to 2 decimals (or whatever is the option in the Control Panel).
• One thing that is completely missing, in the samples in this article, is error handling - for instance, "Can't open Excel". At the very least, after the initial CreateObject(), you should check if variable oExcel is of type "O" (object).
• I did not provide Automation samples for Word or Outlook. Perhaps someone else can write an article covering the basics, sometime in the future. I won't do this in the foreseeable future, for lack of real-world experience. However, many of the basic ideas provided in this article can be applied to Word: macro recording, the use of constants, etc.

Summary

If you didn't do so already, I highly recommend you start using Automation, to automatically generate reports, graphics, etc., in Excel - and, later, to automate other compatible programs. It is relatively easy, and the results are impressive, and very gratifying.

Hilmar Zonneveld, Independent Consultant
Hilmar Zonneveld works in programming since 1986, using dBASE, FoxPro and Visual FoxPro. He is available as an independent consultant. He currently works as a programmer at Bata Shoe Organization; also as an instructor at Cisco Networking Academy. You can contact him through the Universal Thread, or, via e-mail, at hilmarz@yahoo.com. Personal Web page (mainly in Spanish): www.geocities.com/hilmarz.

A Basic Audit-Trail

Hilmar Zonneveld, May 1, 2003

An audit-trail is a record of who did what changes, and when. In Visual FoxPro, this can easily be accomplished through triggers. I hinted at the possibility of doing an audit-trail, in my article on triggers - now, as a reaction to questions in the Universal Thread, I want to present a sample...

Breaking Inheritance

Hilmar Zonneveld, December 6, 2001

(The latest update contains minor edits only.) Five easy and fun ways to get yourself into trouble with inheritance. A frequent source of problems in OOP is called "breaking inheritance". This document briefly describes what inheritance is, how it applies to properties and methods, and how it ...

Buffering

Hilmar Zonneveld, July 1, 2002

Introduction Buffering is a feature in Visual FoxPro that allows us to give the user "undo" and "save" capabilities. In the old FoxPro 2.x days, programmers either didn't provide this capability, or edited memory variables, and copied information between these variables and the table fiel...

CHM help file shows error messages instead of help pages

Hilmar Zonneveld, October 6, 2005

Due to a recent Windows security fix, users can no longer access a CHM file on a server. The table of contents appears, but the individual pages are replaced by error messages. Access to CHM files in specific folders can be explicitly allowed through special registry settings.

Control Arrays

Hilmar Zonneveld, July 20, 2001

(The last update contains minor edits only.) The idea is to have several controls on a form controlled with an array. Thus, you can quickly go through all the controls on the form, managing the array. The sample code included will help you get started quickly. You can easily adapt it to manage...

Hierarchical To-Do List

Hilmar Zonneveld, March 1, 2003

Introduction One common task in programming is to keep track of what problems are pending. For this purpose, I use a "hierarchical to-do list": a list of items, each of which can have sub-items. All you need is Microsoft Word. Alternatives are available as freeware or shareware, but in t...

Inheritance tutorial

Hilmar Zonneveld, October 7, 2005

This is a step-by-step tutorial to show inheritance, specifically in Visual FoxPro forms, as a guidance for people who are not familiar with inheritance in general, or who don’t know how to implement it in Visual FoxPro. The basic idea of inheritance is that all your forms, or several of your for...

Least common multiple and greatest common factor

Hilmar Zonneveld, May 30, 2004

The code shows how to quickly obtain the greatest common factor, and the least common multiple. Both functions are used when manipulating fractions, among others. Several methods are possible; the method usually taught in school involves prime numbers, but this code will execute much faster (and it ...

Multi-level cost calculation

Hilmar Zonneveld, November 1, 2006

A standard requirement in a production system, or in systems for cost calculation, is to add up all the raw materials for a number of finished articles, to get the total cost, or simply to purchase the materials. In this article, Hilmar outlines how to do this with multiple levels of intermediate ar...

Normalization

Hilmar Zonneveld, August 1, 2002

Overview The purpose of this article is to give an overview of normalization. Basically, normalization refers to having an efficient table structure. I will not discuss the famous "first to fifth normal forms" - if you want that information, enough texts exist about it in other places (search sit...

Open any document

Hilmar Zonneveld, November 8, 2001

The following function will open any document, with its default association (the same application that will be called when you double-click on the file, in Windows Explorer). Use it to open a text-file, a Word or Excel document, an image, etc., with an external application.

Primary keys

Hilmar Zonneveld, May 1, 2002

Introduction This document explains the meaning of primary key, foreign key and candidate index in Visual FoxPro. A discussion of natural and surrogate keys (keys visible, or not visible, to the end-user) is included, including the advantages of each approach, as well as different methods for o...

Recursion

Hilmar Zonneveld, January 1, 2003

Continuing my series of introductory articles, this article presents an introduction of a simple yet powerful programming concept: recursion. Introduction "To understand recursion, you must first understand recursion." "To make yogurt, you need milk and yogurt." If you are not accustomed...

Reducing your code

Hilmar Zonneveld, December 1, 2002

Introduction This article presents an introduction to coding shortcuts in Visual FoxPro - when to use them, and when not to. Notes on coding in general This article is about coding shortcuts; however, I should first emphasize that making the code as small as possible is usually not the number...

Rushmore Optimization - not always optimal

Hilmar Zonneveld, July 20, 2001

Rushmore Optimization can help make queries much faster. However, "Full Rushmore Optimization" is not always a desirable goal. "Partial Optimization" is sometimes much faster. It is often believed that to speed things up, you need to have as many indices as possible. This article explains that so...

Show seconds in a readable format

Hilmar Zonneveld, June 7, 2002

If you need to check elapsed time with seconds() or a datetime value, this function allows you to display the elapsed time in a human-readable format, that is, hours:minutes:seconds, instead of the total number of seconds. Just pass a number of seconds as a parameter.

SQL Commands

Hilmar Zonneveld, April 1, 2002

SQL is a standard language used to manipulate databases. Several of the SQL commands are integrated into the Visual FoxPro language. Select This is a very flexible command, used to select data from a table, or from several tables. This command has options to get totals from several record...

Text Manipulation

Hilmar Zonneveld, August 1, 2003

In this article, I will show several ways to manipulate text-files. Knowledge of these methods is often important to import and export specific formats. Some of the techniques can also be used to work with files of any content; however, this article will concentrate on text-files. When ...

Triggers and referencial integrity

Hilmar Zonneveld, June 1, 2002

The purpose of this article is to show how to use some aspects provided by the Visual FoxPro database engine, to control our data. Indices Perhaps most readers already know indices; anyway, I find it convenient to include a brief summary of the topic, since this is a requisite to understan...

Using the HTML Help Workshop

Hilmar Zonneveld, November 1, 2002

A help file can be used either for interactive help, or as an online manual. In this article, I will give an overview over creating help files in the new help format (CHM), for Visual FoxPro 6 and later. This article is introductory and assumes no prior knowledge of the Help Compiler, or of HTML cod...

Using the Visual FoxPro Debugger

Hilmar Zonneveld, February 1, 2003

Introduction Any real-world application will sooner or later misbehave. It is important to be able to find those problems. Visual FoxPro's built-in debugger can help a lot to find out why your program doesn't work as you thought it would. Most of the material in this article applies to Visual...

VisioModeler - a free CASE tool

Hilmar Zonneveld, May 1, 2006

This article is an introduction to VisioModeler. This is a free CASE tool, that can help you design your database, in the process sharing the information with the client in a visual, easy-to-understand, format.