Exporting Access Data to an Unformatted Worksheet

If you just need to move a chunk of data from Access to Excel, and you don't need fancy formatting, you can use the Excel command in the Export group on the External Data tab of the Ribbon to export the Access data to a plain, unformatted worksheet. The sample database, based on the Northwind sample database, has a query that links all the data tables, qryNorthwindAll. A query of this type is very useful for doing data exports, because it contains all the data you might want to export. (Figure 3.1 shows this query selected in the Object Bar.)

FIGURE 3.1

Exporting to Excel from a Ribbon command.

FIGURE 3.1

Exporting to Excel from a Ribbon command.

Clicking the Excel command with a database object selected opens the Export dialog, where you can browse for the folder where the worksheet should be saved, and select a worksheet file format. This dialog is shown in Figure 3.2. You can check the "Export data with formatting and layout" selection if desired, but it doesn't make much of a difference when exporting data from tables or queries, and I don't recommend exporting data from forms or reports, because the formatting you need in Excel isn't the same as the formatting you need in an Access form or report.

The Export dialog opened from the Excel command.

The Export dialog opened from the Excel command.

Using qryNorthwindAll as the data source, you get the plain worksheet shown in Figure 3.3.

FIGURE 3.3

An Excel worksheet created from data in the qryNorthwindAll Access query.

FIGURE 3.3

____Using a query as a data source allows you to combine data from multiple tables and also to format data as you want it to appear in the target worksheet, using data type conversion functions such as CDate, CCur, or CStr.

With a few clicks, you can resize the worksheet columns as needed, edit the column headers as needed, and make the column header row bold, and a plain but serviceable worksheet (Figure 3.4) is ready for use.

FIGURE 3.4

The exported worksheet with a little formatting applied manually.

FIGURE 3.4

The exported worksheet with a little formatting applied manually.

Was this article helpful?

0 0

Post a comment