send query result to Excel and open it

[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.

 

[Allen Browne]

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

 

[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?

 

[Allen Browne]

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

 

[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?

 

[Allen Browne]

Could you use this:
Environ("homepath") & "\Documents"