Export to excel

Hi All

Does anyone have the code for a command button on a form to export the data from a named query to excel. The query is just a stand alone query and does not relate to anything on the command buttons form. It would be nice to open the excel document immediately after clicking the command button
Derek BrownMDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you can use

docmd.transferspreadsheet acexport, 10, "QueryName", "C:\folderName\myexcel.xlsx", true

to open the excel file

followhyperlink   "C:\folderName\myexcel.xlsx"
Derek BrownMDAuthor Commented:
Hi Ray

I get cannot update. Database or object is read only. and I take it that 10 means windows 10? so each users PC has to be individual code?
Rey Obrero (Capricorn1)Commented:
what version of office are you using?
if older than 2007, use

docmd.transferspreadsheet acexport, 9, "QueryName", "C:\folderName\myexcel.xls", true

 to open the excel file

 followhyperlink   "C:\folderName\myexcel.

<and I take it that 10 means windows 10?> NO, it is the excel file format
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Rather than using hard-coded numbers, I would use the argument names So:

docmd.transferspreadsheet acexport, acSpreadsheetTypeExcel12Xml, "QueryName", "C:\folderName\myexcel.xlsx", true

acSpreadsheetTypeExcel12Xml = 10 so there is no difference except to the reader.

The acSpreadsheetTypeExcel12 option writes a .xlsb file type so even though that would be the obvious choice, it won't work.  MS elected to confuse us yet again.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Derek BrownMDAuthor Commented:
Thanks Pat Isn't that trust settings in Excell a pain in the Ass.

Code works though

Thank you both.
Rey Obrero (Capricorn1)Commented:
Derek BrownMDAuthor Commented:
I still get errors ray File is read only
Opening the file immediately could be a timing issue.  Or it could be that no matter how long you wait, Access isn't going to let go of the Excel file until you close Access.

Try putting a wait loop that waits at least 15 seconds.  If that doesn't resolve the problem, you will probably have to not use the TransferSpreadsheet method.  Instead, you would need to use OLE automation, open Excel, create the spreadsheet, close the spreadsheet, close the object, close Excel.  I can't even guarantee that would work since I have never done this.  Access is like a dog with a bone when it comes to not letting go of external files.
Derek BrownMDAuthor Commented:
I am sorry Pat I meant with Rays reply. Yours worked straight off.  My mistake sorry.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.