Click OK

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

Using a macro to write the code

If you want to automate the export so that a user can do it with the click of a button, your best bet is to create a macro that uses the OutputTo action to export the data to a file. Here's how:

1. Open the Access database that contains the database to export.

2. In the Other group on the Create tab, click the Macro button.

3. Choose OutputTo as the action argument, and then fill in the action arguments as summarized in Table 13-1.

Press F1 while the cursor is in any action argument for more information on that argument.

4. Close and save the macro.

Table 13-1 Action Arguments

Action Argument

Description

Object Type

Choose the type of object in your database you want to export

(typically table or query).

Object Name

Choose the name of the object you want to export.

Output Format

Choose format, such as HTML or .xlsx (Excel Workbook),

from the list of available options.

Output File

Enter the complete path and filename of the file you want to

create from the exported data.

Auto Start

Choose Yes to have the exported object open automatically,

or choose No to leave the exported object closed.

Template File

(Optional) Available only for HTML exports; specifies the name

of a template file to use for formatting the HTML output.

Encoding

(Optional) Specifies a character set for the exported table.

Leave blank for standard encoding used within the database.

Output Quality Select the quality of the output, optimized for Screen or Print.

Output Quality Select the quality of the output, optimized for Screen or Print.

As an example, Figure 13-7 shows the selections needed to export a table named Products to an HTML page named FirstTest.htm in a folder named C:\SecureData on the current computer.

Figure 13-7:

Macro to export a table to an HTML file.

¡3 ExportProduds Actio

-A OutputTo I

Arguments Comment Ä

¡Table, Products, HTML (*.htm; *.html), C:\SecureData\FirstTest.html, No,, 0, Print Export Products table to HTML 13

r.-ii.-t Table v

feïNÈtff&fôé

Products

Öutüwtförffiifc

HTML - r-rii,*:^';

öitoijtF:!-

' irti.-.iiijFji - '1 . ■ h

KïiiitfS&Trir:

No

F'1'

WeâSffifi

' IIBIPW

Print

Outputs the data in the specified d; object to Microsoft Office Excel [,xls), (,rtf)r MS-DOS Text (,txt), HTML (.ht Snapshot [,snp) format, Press Fl for he

Outputs the data in the specified d; object to Microsoft Office Excel [,xls), (,rtf)r MS-DOS Text (,txt), HTML (.ht Snapshot [,snp) format, Press Fl for he

For this example, we name the macro ExportProducts.

To test the macro, double-click its name in the Navigation pane. If all is well, the data should export without providing any feedback on-screen. To verify that the macro worked, go to the folder in which you placed the exported file and double-click its icon to open it. If the file contains data from the Products table, you're done: You've written the code necessary to export your data.

Because you used a macro to do the export, the code for exporting the data isn't visible, like it would be in VBA. However, you can get around that problem in a couple of ways. You can keep the macro as-is. Then, when you want to export data from some procedure, use the syntax

DoCmd.RunMacro "macroName"

where macroName is the name of the macro to run. For example, after creating the ExportProducts macro in a database, you could add the following statement to any procedure in the current database when you want code to export the query:

DoCmd.RunMacro "[ExportProducts]"

You can convert any macro to VBA code and then copy and paste the code into any VBA procedure. After you copy the converted code to a procedure, you don't need the macro or the DoCmd.RunMacro() statement any more. The code runs just like any code that you typed into the procedure yourself. Here's how to convert a macro to VBA:

Was this article helpful?

0 0

Post a comment