Click Convert and then click OK when the conversion is finished

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

To get to your converted code, click Modules in the Navigation pane. The converted macro is in a module named Converted Macro -yourMacroName where yourMacroName is the name of the macro that you converted. Double-click that module name to see the converted code.

The converted code is inside a pair of Function...End Function statements, as in the example shown in Figure 13-8. There, you see the results of converting the macro shown in Figure 13-7 to VBA. (You can really get a sense here of how a macro is nothing more than VBA code that you create by filling in the blanks in action arguments rather than typing the source code in the VBA Editor.)

Figure 13-8:

Macro converted to VBA code.

Figure 13-8:

Macro converted to VBA code.

Code to copy & paste

To use the converted code, copy everything between the Function...End and Function statements: that is, excluding the Function and End Function statements. Then paste that converted code into any class module or standard module, where you would otherwise have used

DoCmd.RunMacro() to execute the macro.

Quick and easy import/export/link

The truth be told, the bit about creating a macro to import, export, or link to external data holds true for all kinds of transfers between Access and other files. The easiest way to solve any import/export/link problem is to create a macro to do the job. We've barely scratched the surface of all that's possible here.

In a macro, choose any action listed here to create a macro to do some sort of import, export, or link:

1 CopyObject: Copy tables, queries, forms, reports, macros, and modules to the same or a different database (ACCDB file).

i OutputTo: Output an Access table, query, form, report, or module to one of these formats: Excel Workbook (*.xlsx), Excel 97- Excel 2003 Workbook (*.xls), text (*.txt), rich-text (*.rtf), or HTML (*.html, *.htm).

1 SendObject: Send Access an Access table, query, form, report, or module via any e-mail server that supports Microsoft Mail Applications Programming Interface (MAPI).

1 TransferDatabase: Import, link, or export data between two databases. Supports Access, dBase, Paradox, Windows SharePoint Services (WSS), and Open Database Connectivity (ODBC) formats.

1 TransferSpreadsheet: Import, link, or export data between the current Microsoft Access database (ACCDB) or Access project (ADP) and a spreadsheet file. Import, export, or link to an Excel Workbook (*.xls). Import or export with a Lotus 1-2-3 worksheet (.wks). Some actions (linking, importing, and exporting) aren't supported with certain versions. Press F1 for more information on what's supported.

1 TransferText: Import, link, or export a Microsoft Access database (ACCDB) or Access project (ADP) object with a text, HTML, or Word for Windows merge file.

After you choose an action, you can then choose options from the action arguments. The Help text to the right of the arguments describes the current argument. You can press F1 for more information on using the argument.

Create your macro, test it, and make sure that it works. If you just want to use the macro's VBA code, convert the macro to VBA, as described earlier in this chapter. Then copy all the code between the Function and End Function statements. It's a whole lot easier than trying to write the code from scratch!

The macro actions for importing, exporting, and linking correspond directly to various methods of the DoCmd object in VBA. If you're ever in the VBA Editor and need help with a DoCmd statement, click DoCmd in the Members column of the Object Browser, as in Figure 13-9. Then click any method name in the right column and click the Object Browser's Help button. The selected method's syntax appears at the bottom of the Object Browser.

Figure 13-9:

DoCmd methods in the Object Browser.

Figure 13-9:

DoCmd methods in the Object Browser.

Was this article helpful?

0 0

Post a comment