Exporting a cross tab query to a spreadsheet.

[kkarren]

I have a cross tab query that I would export by code to a spreadsheet replacing the existing spreadsheet each time I use the command button. Then have the spreadsheet show on the screen.

 

[JPeters]

Function ExportOpenXls()

Dim gsSpreadSheet As String
Dim successful

gsSpreadSheet = "C:\MyPath\MyXls.xls"

DoCmd.TransferSpreadsheet acExport, 8, "tblMyTableName", gsSpreadSheet, True, ""

successful = Shell("C:\Program Files\Microsoft Office\Office\excel.exe " & _
Chr$(34) & gsSpreadSheet, vbMaximizedFocus)

End Function

The XLS file will be an Excel 97 spreadsheet with FieldName headings. Change your spreadsheet path to the file you are exporting to. Change the table or query name of "tblMyTableName" to your recordsource.

-Josh
------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------