8.6 Automating macros | Microsoft Office Automation with Visual FoxPro

Automating macros

Although you can perform pretty much any Excel action through Automation, if you already have an Excel macro to do a particular thing, it may make more sense to use the existing macro than to rewrite it in VFP. This is especially true if the macro doesn t involve transferring information between the two applications, or if it s part of a pre-packaged set of macros. A benefit to running Excel macros rather than translating them into Automation code is that macros run faster than the analogous Automation code.

The Application object s Run method allows you to execute Excel macros. You pass the name of the macro and any parameters needed to run the macro. For example, say Macro1 is the default name for a recorded macro. It may perform a variety of tasks. This line executes it:

oExcel.Run("Macro1")

In many cases, it wouldn t be hard to create the same functionality through Automation. However, there are situations where rewriting an existing macro as Automation would present a problem. In those cases, Run provides an easy solution.

Another common situation is to write a macro using Automation code. For an example, you could write a macro to save an Excel workbook. Writing an Excel macro to save the workbook allows you to take advantage of the Excel Application s DisplayAlerts property, which takes a logical value and works in a manner similar to FoxPro s SET SAFETY command. DisplayAlerts only works in the macro environment, and it s reset to true at the end of the macro. Using the following macro allows you to save a file without all of the workarounds discussed in Chapter 7, "Excel Basics."

We re assuming that you know how to write Excel macros. Determine the code you need, and put it into a file. This example uses low-level file functions to create the file, though there are many other ways to accomplish this task in FoxPro. (In VFP 6, the StrToFile() function provides a more readable way to create files.)

* Open the file and put in the lines of Excel Code

m.MacroFileName = "C:\Temp\SaveMacro.Txt"

m.MFHandle = FCREATE(m.MacroFileName)

* Add the body of the macro

= FPUTS(m.MFHandle, "' QuietSave Macro")

= FPUTS(m.MFHandle, "' ")

= FPUTS(m.MFHandle, "Sub QuietSave()")

= FPUTS(m.MFHandle, "' ")

= FPUTS(m.MFHandle, " Application.DisplayAlerts = False")

= FPUTS(m.MFHandle, " ActiveWorkbook.Save")

= FPUTS(m.MFHandle, "End Sub")

* Close the open LL file.

=FCLOSE(m.MFHandle)

Once you have the macro code written to a file, you re ready to bring it into Excel. Use the Modules collection object s Add method to add a blank macro module. Then use the new Module object s InsertFile method to import the macro code. Name the Macro object, and you re ready to run it:

* Add a new Module to the Modules collection

oMacroModule = oExcelObject.Modules.Add()

* Pull in the file containing the macro code

oMacroModule.InsertFile(m.MacroFileName)

* Name the macro

oMacroModule.Name = "MacroSave"

* Run the macro

oExcel.Application.Run("QuietSave")

This example is not necessarily an endorsement of using the QuietSave macro instead of the workarounds described in Chapter 7, "Excel Basics." It s an alternate method that may or may not work for your application s environment. But it does illustrate how to generate macros using Automation.