DoCmd.OutputTo in xlsx (Excel 2007+) Format

https://www.devhut.net/2014/08/07/docmd-outputto-in-xlsx-excel-2007-format/


A common technique for exporting data to Excel is to utilize the  DoCmd.OutputTo Method.
Now, previously (pre Office 2007), the basic format to export a query to Excel format was simply:
DoCmd.OutputTo acOutputQuery, "YourQueryName", acFormatXLS, , True
BUT, once you port such a database to a post Office 2007 computer, you start to get it exported as a “Microsoft Excel 5.0/95 Workbook (*.xls)” format and such a file will thus open Excel in compatibility mode possibly given the user compatibility errors/messages when they go to save the file after working with it.
So unless you have to ensure legacy compatibility, it is strongly advisable to update your code to export the data in “Excel Workbook (*.xlsx)” format, the new Excel file format thus eliminating any compatibility issues. Hence, we simply need to change the OutputFormat format variable to a more appropriate format (acFormatXLS -> acFormatXLSX). So your code would thus become:
DoCmd.OutputTo acOutputQuery, "YourQueryName", acFormatXLSX, , True
Alternately, you could also use:
DoCmd.OutputTo acOutputQuery, "YourQueryName", "Excel Workbook (*.xlsx)", , True

Looking to export to a different format?

Nothing could be easier! In the VBA Object browser, go to the Access Contants library and look over the various acFormat**** available to you. Such as:
  • acFormatHTML
  • acFormatPDF
  • acFormatRTF
  • acFormatSNP
  • acFormatTXT
  • acFormatXLS
  • acFormatXLSB
  • acFormatXLSX
  • acFormatPS

Taking Things a Little Further

It wouldn’t be very difficult to create a custom DoCmd.OutputTo function which could determine the version of MS Excel installed and the utilize the more appropriate OutputFormat variable. Then you code would be 100% compatible regardless of what your users have installed!

Are There Any Alternatives

Another approach available to us is simply automate Excel. This is the approach I have adopted. In my early development days, I used the built-in DoCmd.OutputTo command, but have long since abandoned it for my own custom export function: Export2XLS. Why? The custom function approach is fully compatible with whichever version of Excel a user has installed, and gives the programmer far more control over what the final export looks like. You can format Cells/Ranges, apply colors, fonts, …

Comments

Post a Comment

Popular posts from this blog

MS Access: export a query to Excel with Docmd.Output

Image
https://codedocu.com/Details?d=2136&a=8&f=319&l=0 Download: Datei 1:  Access_Export_Query.accdb Datei 2:  Output_Results.xlsx The example shows how to export an Access query to Excel. The query contains current data from a table, which was filtered by a radio button. For this you use the command Docmd.OutputTo in Microsoft Access As DoCmd.OutputTo acOutputQuery,  "qryCars_Sale" , acFormatXLSX, ,  True When executing the OutputTo command, a Save dialog box is automatically displayed which would like to save a document in Excel. DoCmd.OutputTo acOutputQuery,  "qryCars_Sale" , acFormatXLSX, ,  True The acOutputQuery parameter indicates that a query is in the third parameter. The query query is bound to the form that is currently open and uses a radio button. If you want to output the output directly into a named Excel file, then you have to name the Excel file name with the foll path Dim  sFilenam
Read more

Export Access Object to Excel ,PDF, RTF , etc. using DoCmd.OutputTo

Microsoft Access allows us to export access object like table, query ,etc to other formats like Excel, PDF, RTF , etc. Using “DoCmd.OutputTo” To know about DoCmd.OutputTo visit the below link http://msdn.microsoft.com/en-us/library/aa141534(v=office.10).aspx Syntax DoCmd.outputto ObjectType, ObjectName, OutputFormat, Output file, Autostart, Templatefile, encoding, ObjectType – acOutputTable( to output table), acOutputquery(to output query),acoutputreport(to output report),ascendform(to output form), acOutputModule(to output module) ObjectName – Name of table, query, Report etc. OutputFormat – acFormatHTML ,acFormatRTF ,acFormatSNP,acFormatTXT ,acFormatXLS, acFormatXLSX , acFormatPDF , etc. Output file – Complete path of output file eg. C:\ abc.xls  or d:\ abc.pdf  Keep it blank if you want to save at run time. AutoStart Optional Variant Templatefile – The full name, including the path, of the file to use as a template for an HTML file Encoding- Optional Variant  If you are fa
Read more