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!
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you can use
docmd.transferspreadsheet acexport, 10, "QueryName", "C:\folderName\myexcel.xls
to open the excel file
followhyperlink "C:\folderName\myexcel.xls
docmd.transferspreadsheet acexport, 10, "QueryName", "C:\folderName\myexcel.xls
to open the excel file
followhyperlink "C:\folderName\myexcel.xls
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?
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?
what version of office are you using?
if older than 2007, use
docmd.transferspreadsheet acexport, 9, "QueryName", "C:\folderName\myexcel.xls
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
if older than 2007, use
docmd.transferspreadsheet acexport, 9, "QueryName", "C:\folderName\myexcel.xls
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
Rather than using hard-coded numbers, I would use the argument names So:
docmd.transferspreadsheet acexport, acSpreadsheetTypeExcel12Xm
acSpreadsheetTypeExcel12Xm
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.
docmd.transferspreadsheet acexport, acSpreadsheetTypeExcel12Xm
acSpreadsheetTypeExcel12Xm
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.
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial