Save in XLSX format instead of Excel 5.0/95 workbook

[Thompyt]

I am using :


DoCmd.OutputTo 3, "Weekly_Summation", acFormatXLS, "C:\Temp\Weekly Summation - " & Format(Date, "dd mmm yyyy") & ".xls", , 0

for an excel output. I have to edit the file and save as an Xlsx format manually as the recipients are unable to save after editing on their computer. How do I save as an XLSX?

Tried
acFormatXLS to acFormatXLSX
".xls" to ".xlsx"

I also Googled it. Nada

Doesn't work

Thanks

Wayne

[alansidman]

I prefer to use the DoCmd.TransferSpreadsheet functionality

Here is a link to MS site on its use. Pay attention to the Spreadsheet Type and Transfer Type sub categories

https://msdn.microsoft.com/en-us/lib.../ff844793.aspx

[Thompyt]

Thanks! After a little tweeking and figuring what to do, I made it work. Much appreciated.

[lowells]

Is there a way to do this for exporting an Access report? DoCmd.TransferSpreadsheet appears to only work for Tables. I currently use DoCmd.OutputTo to export my report but that is to an older (.xls) format.

[Thompyt]

I tried to transfer a report = no go. But my report is base on a query. Transfer the query - it would have the same output as the report.