Importing ExportingLinking to Anything

As you might know, you export data from Access to a variety of formats. You can do so interactively (without code). Here's how.

1. In the database window, click the table, query, or other object that you want to export.

2. Choose FileOExport from the Access menu bar.

3. In the Export dialog box that appears, choose a document type from the Save As Type drop-down list.

4. Navigate to a folder, enter a filename for the exported data, and click the Export button.

Using a macro to write the code

If you want to automate the exportation 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. Click Macros in the database window, and then click the New 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 wish to

export (typically table or query).

Object Name

Choose the name of the object you wish to export.

Output Format

Choose format, such as HTML or .wks (worksheet), from the

list of available options.

Output File

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

create from the exported data.

(continued)

Table 13-1 (continued)

Action Argument

Description

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.

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

Figure 13-7:

Macro to export a query to an HTML file.

Figure 13-7:

Macro to export a query to an HTML file.

For this example, I name the macro ExportCustLookup.

To test the macro, click its name in the database window and then click the Run button in the database window's toolbar. If all is well, the data should export without providing any feedback onscreen. To verify that the macro worked, go to the folder in which you placed the exported file and doubleclick its icon to open it. If the file is right, you're done: You've written the code necessary to export your data.

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

DoCmd.RunMacro "macroName"

where macroName is the name of the macro to run. For example, after creating the ExportCustLookup 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 "[ExportC ustLookup]"

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 won't need the macro anymore nor the DoCmd.RunMacro() statement. The code will run just like any code that you typed into the procedure yourself. Here's how to convert a macro to VBA:

1. Click Macros in the database window, and then click the macro that you want to convert.

2. Choose ToolsOMacroOConvert Macros to Visual Basic.

3. To convert without adding error-trapping to the exported code, clear (uncheck) the Add Error Handling to Generated Functions check box.

4. Click Convert, and then click OK when the conversion is finished.

To get to your converted code, click Modules in the database window. The converted macro will be 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 will be 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.)

To use the converted code, copy everything between the Function...End 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

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. I'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:

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

i OutputTo: Output an Access table, query, form, report, module, data access page) to an Excel 98 (*.xls), MS-DOS text (*.txt), rich-text (*.rtf), HTML (*.html), Active Server Pages (*.asp), or Internet Information Server (IIS) formats (*.htx, *.idv format).

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

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

I TransferSpreadsheet: Import, link, or export data between the current Microsoft Access database (MDB) or Access project (ADP) and a spreadsheet file. Import, export, or link to any Excel worksheet (*.xls). Import or export with any Lotus 1-2-3 worksheet (.wks).

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

Converted macro

Figure 13-8:

Macro converted to VBA code.

Converted macro

Figure 13-8:

Macro converted to VBA code.

Code to copy/paste

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 out, 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.

In Chapter 14, you can read about yet another way to reach outside Access to external programs: Automation, which works with all the major programs in Microsoft Office.

DoCmd

Figure 13-9:

DoCmd methods in the Object Browser.

GoCmd

Search R

Q(g3ia

GoCmd

Search R

Figure 13-9:

DoCmd methods in the Object Browser.

Library

1 Class

1 Member

1

Access

0 DoCmd

¡A

m. Access

© Application

& DoCmd

H

Classes

Members of'DoCmd'

0|DoCmd

I At

OutputTo

A

0 Document

Printout

0 Documer

tltem

Quit

S Doc urne ntLibraryVer

Rename

ÏÎJ Doc ume ntLibraryVer

RepaintObject

0 DocumentPropertiei__

0 DocumentProperty

Requery

Restore

US Documents

-¿■^ RunCommand

DriverPromptEnum

Run Macro

¿P EdltModeEnum

Run SQL

EditModeEnum

^ Save

O ErrObject

SelectObject

Error

& SendObject

0 Error

SetMenultem

0 Errors

SetWarnings

S Errors

ShüwAIIRecords

=P ErrorValueEnum

ShowToolbar

¿P EventReasonEnum

»» TransferDatabase

if EventStatusEnum

-Ä TransferSpreadsheet

IT EXCEPINFO

TransferSQLDatabase

¿S Exception

m

TransferText

V

Class DoCmd Member of Access

Class DoCmd Member of Access

DoCmd methods

0 0

Post a comment