HOME | SIGN UP | ACCOUNT | SUBSCRIBE | TROUBLESHOOTING | LOGIN
News · Meetings · Jobs · Downloads · Universal Thread articles · Web articles · Conference coverages · Consultants · Blogs · FAQ · User groups · Photos · Videos

Excel Automation

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.

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 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
This will make all the constants available from this point on. If you don't understand what is going on, please see the help topics for "#DEFINE" and "#INCLUDE". The downloaded file contains "#DEFINE" statements for all the relevant Excel constants.

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.

More articles from this author

NoTitleDate
1.A Basic Audit-TrailMay 2003
2.BufferingJuly 2002
3.Hierarchical To-Do ListMarch 2003
4.NormalizationAugust 2002
5.Primary keysMay 2002
6.RecursionJanuary 2003
7.Reducing your codeDecember 2002
8.SQL CommandsApril 2002
9.Text ManipulationAugust 2003
10.Triggers and referencial integrityJune 2002
11.Using the HTML Help WorkshopNovember 2002
12.Using the Visual FoxPro DebuggerFebruary 2003
13.VisioModeler - a free CASE toolMay 2006

Copyright © 1993 - 2014 Level Extreme Inc., All Rights Reserved · Telephone: 506-783-9007 Email: info@universalthread.com · Privacy & Security · Copyright · Terms & Conditions