Retrieving Data with the Transfer Spreadsheet Method

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

It is possible to use the TransferSpreadsheet action to import or export data between the current Microsoft Access database (.mdb) or Access project (.adp) and a spreadsheet file. You can also link the data in a Microsoft Excel spreadsheet to the current Microsoft Access database. With a linked spreadsheet, you can view and edit the spreadsheet data with Microsoft Access while still allowing complete access to the data from your Microsoft Excel spreadsheet program.

The TransferSpreadsheet method carries out the TransferSpreadsheet action in Visual Basic and has the following syntax:

DoCmd.TransferSpreadsheet [transfertype][, spreadsheettype], _ tablename, filename [, hasfieldnames][, range]

The transfertype argument can be one of the following constants: acImport (default setting), acExport, or acLink. These constants define whether data has to be imported, exported, or linked to the database.

The spreadsheettype argument can be one of the following constants:

0 acSpreadsheetTypeExcel3 (default setting)

6 acSpreadsheetTypeExcel4 5 acSpreadsheetTypeExcel5 5 acSpreadsheetTypeExcel7 8 acSpreadsheetTypeExcel8 8 acSpreadsheetTypeExcel9

2 acSpreadsheetTypeLotusWK1

3 acSpreadsheetTypeLotusWK3

7 acSpreadsheetTypeLotusWK4

It is not difficult to guess that the spreadsheettype argument specifies the spreadsheet name and the version number.

The tablename argument is a string expression that specifies the name of the Microsoft Access table you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to. Instead of the table name, you may also specify the name of the Select query whose results you want to export to a spreadsheet.

The hasfieldnames argument is a logical value True (-1) or False (0). True indicates that the first worksheet row contains the field names. False denotes that the first row contains normal data. The default setting is False (no field names in the first row).

The range argument is a string expression that specifies the range of cells or the name of the range in the worksheet. This argument applies only to importing. If you omit the range argument, the entire spreadsheet will be imported. Leave this argument blank if you want to export, unless you need to specify the worksheet name.

The example procedure, ExportData, illustrated below exports data from the Shippers table in the Northwind database to the Shippers.xls spreadsheet using the TransferSpreadsheet method. Notice that this procedure uses Automation to establish a connection to Microsoft Access. After the connection has been established, the Northwind database is opened with the OpenCurrentDatabase method. After running the ExportData procedure, open the C:\Shippers.xls file to view the retrieved data.

' declare at the top of the module Dim objAccess As Access.Application

Sub ExportData()

Set objAccess = CreateObject("Access.Application") objAccess.OpenCurrentDatabase filepath:= _

"C:\Program Files\Microsoft Office\Office\" _ & "Samples\Northwind.mdb" objAccess.DoCmd.TransferSpreadsheet _ TransferType:=acExport, _ SpreadsheetType:=acSpreadsheetTypeExcel10, _ TableName:="Shippers", _ Filename:="C:\Shippers.xls", _ HasFieldNames:=True, _ Range:="Sheet1" objAccess.Quit Set objAccess = Nothing End Sub

Figure 15-12:

The data from a Microsoft Access table can be exported to Excel spreadsheet file using the TransferSpreadsheet method (see ExportData procedure listed above).

Was this article helpful?

+1 0

Post a comment