Using Transfer Spreadsheet to Create a New Worksheet

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

If you prefer not to use the CopyFromRecordset method, you can also use the TransferSpreadsheet method from the DoCmd object. There are a few distinct advantages to the TransferSpreadsheet method. One advantage is that you can export an entire table to a spreadsheet with one simple command. For example:

'Use Transfer Spreadsheet to create an Excel Spreadsheet

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Skids", i "c:\skids.xls"

This code is all you need to export the Skids table to a spreadsheet called skids.xls to the C drive. This method enables you to export both tables and queries stored in your database. Another advantage to the method is that you don't actually invoke the Excel object model, which requires more code and more overhead, as Excel is loaded into memory.

A noticeable side effect of using the TransferSpreadsheet method is that if you already have a file called skids.xls in the specified location, with the same sheet name and named data range, the preceding code will fail silently. The code runs, but the existing spreadsheet is not replaced by the new spreadsheet. As a workaround, you could change the named range or delete the original sheet, so that the new sheet will be created. Alternatively, it's easy to add some code to check for the existence of the file before this line of code runs, and if so, delete the workbook before creating the new one. That logic can be accomplished with the following code:

Dim strFilePath As String

'Check to see if the file already exists strFilePath = "C:\skids.xls" If (Dir$(strFilePath) <> "") Then

'Delete the file since it already exists Kill strFilePath End If

'Use TransferSpreadsheet to create an Excel Spreadsheet

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Skids", strFilePath

Still, this code seems a little inflexible. Ideally, the user should be able to specify the name and location of the new Excel workbook. Adding a reference to the Microsoft Office 12.0 Object Library enables you to leverage the FileDialog object in your VBA code so that you can reuse four common dialog boxes built into Office: Open File, Save As, File Picker, and Folder Picker. The Save As dialog box would be perfect, except for one thing: It does not allow the dialog box file type filters to be set. So, instead, using the File Picker dialog box and switching the text of the Title and Button properties to Save As will serve the purpose. The following code implements this scenario:

Dim strFilePath As String

'Default Location strFilePath = "C:\skids.xlsx"

'Use the FileDialog to choose the file location With Application.FileDialog(msoFileDialogFilePicker) .Title = "Save As" .ButtonName = "Save As" .AllowMultiSelect = False .Filters.Add "Excel", "*.xlsx; *.xls", 1 .Filters.Add "All Files", "*.*", 2 .InitialFileName = strFilePath

'Show the dialog and if the dialog returns 'True, then create the new Spreadsheet If .Show = True Then 'The user clicked "Save"

strFilePath = .SelectedItems(l) Else 'The user canceled the dialog so exit

MsgBox "Save As canceled! Spreadsheet has not been saved." Exit Sub End If End With

'Check to see if the file already exists

If (Dir$(strFilePath) <> "") Then

'Delete the file since it already exists

Kill strFilePath

End If

'Use TransferSpreadsheet to create an Excel Spreadsheet

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Skids",


In this code, the Application.FileDialog object is chosen by passing the enumeration option corresponding to the desired dialog type. For the File Dialog, the Title and Button text, set the desired strings to the Title and ButtonName properties of the FileDialog object. Additionally, for the default filename and folder path as well as the selection model for the dialog box are set by specifying the desired settings to the InitialFileName and AllowMultiSelect properties. Custom the Filters can be created by calling the Add method on the Filters object and passing the filter string. Finally, the Show method is called to show the dialog box to the user, which returns true if the user clicks the action button for the dialog box (in our case named Save As); otherwise, false is returned if the user chooses Cancel or the Close button on the dialog box. The Show method can be wrapped in an If statement to provide some feedback to the user, should they decide not to save the file.

One other consideration when using the TransferSpreadsheet method is that you do not have the capability to manipulate the look and feel of the spreadsheet when it is created. The new worksheet will be a plain table without any formatting but, fortunately, it will contain column headers. You'll need to decide whether your project requires the formatting and flexibility of the first method or the ease of use of the second method. Both work equally well for their basic task, transferring data between Access and Excel.

Was this article helpful?

+2 -1


Post a comment