MsAccess OutputTo Work Around

Seriously, one of the more boneheaded things that Microsoft did to Access 2007 was to remove the ability to output to an *.xls format using the docmd.outputto method. I’ve had to do a little digging to find a suitable replacement, and I came up with the following bit of code. Please: feel free to use it.

‘ Begin Code
‘ Find the path to the user’s desktop

Dim objWSHShell As Object
Dim strSpecialFolderPath
Set objWSHShell = CreateObject(“WScript.Shell”)

specialfolderpath = objWSHShell.SpecialFolders(“Desktop”)
Set objWSHShell = Nothing

‘ Create a String for the Filename and Query User for Filename

Dim filename As String
filename = InputBox(“Please provide a filename – the file will be saved to your desktop…”, “Filename for Export”, “export.xls”)

‘ Validate Filename for a non-null

If filename = “” Then
MsgBox “WARNING: No filename provided. Cannot continue.”, vbOKOnly, “No Filename”
Exit Sub
End If

‘ Execute

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, queryname, specialfolderpath & “\” & filename, True

‘ End Code

What it does is capture the current path to the user’s Desktop, challenge the user for a filename with an Inputbox (inserting a default name for convenience), then it performs a TransferSpreadsheet. If the TransferSpreadsheet were performed on its own, then the only reliable place to put the export would have been the root of C:. Instead, this export always presumes the user’s Desktop without having to open a filesystem object to display a file dialog and capture the new path. It’s relatively easy to use;  maybe you’ll find it useful.

R

Name (required)

Mail (will not be published) (required)

Website