send query result to Excel and open it

S

Song Su

How to do it in code?

I have a crosstab query and had hard time to create report based on it. I'm
thinking send query result directly to Excel and open that Excel file. How
to do it in code?

Thanks.
 
A

Allen Browne

Use TransferSpreadsheet to export the query to Excel.
Use FollowHyperlink to open the spreadsheet.
 
S

Song Su

Thank you for quick response. Excel created but I cannot open it. My codes
as follows:

stDocName = "tblUnionData_Crosstab"
DoCmd.TransferSpreadsheet acExport, , stDocName, "Book1.xls", False
Application.FollowHyperlink ("Book1.xls")

where did I do wrong?
 
A

Allen Browne

Use the full path, e.g.:
Dim strFile As String
strFile = "C:\MyFolder\Book1.xls"
 
S

Song Su

Excel is created in 'My Documents' folder.
If I specify "C:\Documents and Settings\Song\My Documents\Book1.xls"
as a path, other people cannot run as my name is 'Song' which is in the
path.
I do now know what other users' access right to any other folder but 'My
Documents' folder is always accessible. How to handle this delima?
 
Top