Import a Query into Excel and Open the Excel file via Access

[hansjhamm]

I have imported the query into Excel and able to save it, but I have
people that complained about having to find the file somewhere....
How would I run this query and NOT save to My Documents, but create a
new Excel file and just open it, leaving the save option to the user?
Also, can you do Excel cell formatting inside the VBA/Access code?

DoCmd.OpenQuery "ISS Open Position Query", acViewNormal, acEdit
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ISS
Open Position Query", "C:\Documents and Settings\Administrator\My
Documents\ISS Open Position Query", True, ""
DoCmd.Close acForm, "HR Reporting"

Thanks,

Hans

 

[Douglas J. Steele]

You can't use TransferSpreadsheet to create an open spreadsheet,
unfortunately,

What you could do, though, is allow them to pick where they want it, and
save it there for them. See http://www.mvps.org/access/api/api0001.htm at
"The Access Web" for how to prompt for a file name.

Once you've saved the spreadsheet, you could use Application.FollowHyperlink
to open it up for them.

 

[hansjhamm]

Douglas,

Hmmm...that's what I was afraid of...think I may try the excel side and
see if someone there knows a another way that I can do this and the
formatting issue....Thanks for your help.


Hans