Export to excel

Derek Brown
Derek Brown used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
you can use

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

to open the excel file

followhyperlink   "C:\folderName\myexcel.xlsx"

Author

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?
Top Expert 2016

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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017
Commented:
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.

Author

Commented:
Thanks Pat Isn't that trust settings in Excell a pain in the Ass.

Code works though

Thank you both.
Top Expert 2016

Commented:
:-0

Author

Commented:
I still get errors ray File is read only
Distinguished Expert 2017

Commented:
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.

Author

Commented:
I am sorry Pat I meant with Rays reply. Yours worked straight off.  My mistake sorry.

Derek

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial