To test the macro, choose Macros from the Code group on the Developer tab. Click the macro name and choose Run. The macro executes. If all is well, you're done. To run macros in Excel 2007, you have to save your file as an Excel Macro-Enabled Workbook (*.xlsm) and place it in a trusted location.
The recorded macro is also VBA code. If you choose Macros from the Code group on the Developer tab, click a macro name, and then click Edit, the macro opens in the VBA Editor. Each step in the procedure that you see was recorded while you were recording the macro.
Back in your Access VBA procedure, you most likely want to run the macro after your code copies new content to the worksheet — for example, just under the XlSheet.Range("FromAccess") = Me!GrandTotal statement in Listing 14-3, or under XlSheet.Range("B3").CopyFromRecordset MyRecordset in Listing 14-4. The syntax for running a macro in the open workbook, from Access, is objVar.Run ("macroName")
where objVar is the object variable to which you assigned the Excel application (Xl in previous examples), and macroName is the name of the macro in the worksheet that you want to run. For example, from any Access VBA procedure, the following statement runs the macro named FormatSheet in the open worksheet (assuming that the open worksheet contains a macro named FormatSheet):
If you want to put the cursor in a specific field before the macro executes, use the syntax objVar.Range("Address").Select before running the macro. For example, the following code positions the cursor to cell A1 and then executes the macro named FormatSheet in the open workbook:
'Go to cell A1. XlSheet.Range("A1").Select
'Run macro named FormatSheet. Xl.Run ("FormatSheet")
You also need to change the worksheet's extension from .xlsx to .xlsm because you created a new file. If you moved the file to a trusted location, make sure that you also update the path to the workbook file.
Automating interaction between Access and other Office programs can be cumbersome. This chapter only touches the surface of what you can do. By adding references to Outlook, Word, PowerPoint, and Excel, you can explore the various objects, methods, and properties available for you to control.
The Part of Tens
J\ #o For Dummies book would be complete without a # W Part of Tens. Ten is such a nice number to work with, given our ten fingers and all. Chapter 15 covers the main strategies that you should adopt to avoid going crazy trying to get VBA to do something. Then Chapter 16 lists the top ten nerdy programming tasks that you're most likely to want to do from Day 1 of using VBA.
Was this article helpful?