Simply Exporting Access Data to Excel

Just as in earlier Office versions, Access offers two ways to do a quick-and-dirty export of table or query data to an Excel worksheet. You can use the Excel button in the Export group of the External Data tab of the Ribbon to export Access data without worrying about formatting, for an Office 2007 user who just wants the data. If you need to create worksheets that can be opened and edited by users running older versions of Office, or using a handheld device such as a BlackBerry, you can use the TransferSpreadsheet method to export data, selecting the desired output worksheet format. This can be useful when you work for an organization that has upgraded its software and you need to send a worksheet with client contact information to a sales representative who has not updated her laptop yet.

For a quick export to the new .xlsx worksheet format, use the Excel button in the Export group of the External Data tab of the new Access Ribbon, as shown in Figure 7.1.

FIGURE 7.1

Exporting a table to an Excel worksheet from the Ribbon.

FIGURE 7.1

Clicking the Excel button opens a dialog where you can browse for the location for saving the worksheet. This dialog has an option for preserving the layout and formatting of the original Access object, which (curiously) is only available if a table or query is selected (see Figure 7.2).

Tables and queries don't have much in the way of formatting and layout, but if you check the "Export data with formatting and layout" checkbox your Excel worksheet will use the same font as the table or query (though not the alternate row shading), and it will show data from a linked table instead of the linking ID field. Figure 7.3 shows two worksheets made from the same table; the top one displays the customers' company name in the CustomerID column (picking it up from the linked table) and uses the Calibri 11 font; the second worksheet displays the CustomerID in that column and uses the Arial 10 font.

FIGURE 7.2

The options when exporting a table to an Excel worksheet.

FIGURE 7.2

The options when exporting a table to an Excel worksheet.

FIGURE 7.3

Excel worksheets exported with and without checking the "Export data with formatting and layout" option on the export dialog.

FIGURE 7.3

The Excel button on the Ribbon (with or without the layout and formatting preserved) is a useful option when you need to create a quick-and-dirty Excel worksheet in Excel 2007 format (the new extension is .xlsx). If you need to export to an older Excel format, but you don't need fancy formatting, you can do an export with a single line of VBA code, using the TransferSpreadsheet method, which has been available since the earliest days of Access.

The TransferSpreadsheet method allows you to select the Excel version for creating your worksheet filled with Access data, so you can create worksheets that will be usable by recipients who have older versions of Office. The procedure listed as follows exports tblContacts to an Excel 97-2003 worksheet called Contacts.xls (the named constant for this worksheet version is acSpreadsheetTypeExcel7):

Public Function TransferToExcel()

On Error GoTo ErrorHandler

Dim strTable As String

Dim strWorksheetPath As String strWorksheetPath = GetWorksheetsPath strWorksheetPath = strWorksheetPath & "Contacts.xls" strTable = "tblContacts" strWorksheetPath = GetWorksheetsPath() strWorksheetPath = strWorksheetPath & "Contacts.xls" Debug.Print "Worksheet path: " & strWorksheetPath

Export table data to a new worksheet:

DoCmd.TransferSpreadsheet transfertype:=acExport, _ spreadsheettype:=acSpreadsheetTypeExcel7, _ TableName:=strTable, FileName:=strWorksheetPath, _ hasfieldnames:=True

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

The exported worksheet looks just like the unformatted worksheet exported from the Ribbon selection, but it is in the older format, as you can see from the "(Compatibility Mode)" after the worksheet name in its title bar (see Figure 7.4).

FIGURE 7.4

An Access table exported to an Excel 97-2003 worksheet.

A

A

B_________

C

â–¡

E

F

G

H

1

1

ContactID

CustomerlD

FirstName

LastName

Salutation

StreetAddress

City

State OrProvin ce

Postal«

2

1

3

Nancy

Davolio

Nancy

507 -20th Ave. E.Apt.2A

Seattle

WA

98122-

3

2

2

Janet

Leverling

Janet

722 Moss Bay Blvd.

Kirkland

WA

r98033

4

3

2

Andrew

Fuller

Andrew

908 W. Capital Way

Tacoma

WA

"98401

5

4

9

Margaret

Peacock

Margaret

4110 Old Redmond Rd.

Redmond

WA

98052-

6

5

22

Steven

Buchanan

Steven

14 Garrett Hill

London

SW1 8

7

6

18

Kenneth

Gould

Kenneth

10988 Barnes WayRoom 317

Youngstown

NV

"11111-

8

7

30

Beatrice

Gleason

Beatrice

11356 Flower Cir. Suite 92.6

Elk Mound

VI

11111-

8

40

Harriette

Harrington

Harriette

2439 Little Creek Blvd.Room 991

Springfield

NC

11111-

10

9

14

Dennis

Saunders

Dennis

9859 Balboa StDept 328

Bridgeport

SC

11111-

11

10

13

Jeffrey

Fry

Jeffrey

6951 57th Blvd.

Willow

NM

11111-

T2

11

9

Kelley

Kelley

5065 Frederick Terr.Suite 577

South Fork

NE

11111-

13

12

107

Theodore

Morrison

Ted

5997 27th Blvd.Bldg. 82

Bitterwater

KS

11111-

14

13

119

Marian

Yates

Marian

8535 Skyline Blvd.Suite 390

Springfield

NJ

11111-

15

14

5

Sean

Christensen

Sean

12887 Cheriy Ln.

Easton

LA

11111-

16

15

139

Aaron

Buckman

Aaron

1363 42nd Terr.Suite 325

Maple Lake

ID

11111-

17

16

97

Sheryl

Gleason

Sheryl

10139 38th Ln.

Springfield

LA

11111-

M 4

M

0 0

Post a comment