In order to export an Access query into an .xls spread sheet and not have the fields truncated by the cell limit I have, (courtesy of EE) a piece of code to export the query into an Excel 2010 spread sheet, open in and save it in .xls format ( I need it in this format so the data can be imported into our website).
This works all fine but with the code below, but I get an error message when I open the newly created .xls spread sheet - (the file you are trying to open is in a different format than specified by the file extension etc.) Any ideas how to solve that.
Private Sub Command2_Click()
DoCmd.OutputTo acOutputQuery, "Tickers for Exporting", acFormatXLSX, "\\nor-gs2-dc01\Redirected\xxxx\Desktop\Tickers for Exporting.xlsx", False
Dim xlObj As Object
Set xlObj = CreateObject("Excel.Application")
xlObj.Workbooks.Open "\\nor-gs2-dc01\Redirected\xxxx\Desktop\Tickers for Exporting.xlsx"
.activeworkbook.SaveAs "\\nor-gs2-dc01\Redirected\xxxx\Desktop\Tickers for Exporting.xls"
Set xlObj = Nothing