Save a file in .xlsx format in vb

Question

I am using the latest Microsoft.Office.Interop.Excel dll

The system currently saves the file in .xls format.
I have used the following code but I am getting an error.

App = CreateObject("Excel.Application")

xlsTemplate= App.Workbooks.Open(TemplateFile) ' template is in .xls format

TargetPath = Some target path.xlsx

Excel.Workbook.SaveCopyAs(TargetPath)

Excel.Workbook = App.Workbooks.Open(TargetPath) ' code breaks here

SomeFunction(Excel.Workbook)

ExcelBook.SaveAs(ExcelBook.FullName,   Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook,  System.Reflection.Missing.Value, System.Reflection.Missing.Value, False, False, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, True, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value)
ExcelBook.Saved = True

Error:Excel cannot open the file '.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

Answer 1

I am responding to this thread as current answers were unclear to me - which forced me to do more research.

Changing the extension in the name does not change the file format. To save the excel document in your desired format use the below parameters within your Microsoft.Office.Interop.Excel to save your workbook (VB.NET):

wb.SaveAs("C:\MyFolder\MyWorkbook.xlsx",XlFileFormat.xlOpenXMLWorkbook)

You can modify the second parameter (File Format) to specify what version you want to save the document.

• XlFileFormat.xlOpenXMLWorkbook = xlOpenXMLWorkbook (without macro's in 2007-2013, xlsx)
• XlFileFormat.xlOpenXMLWorkbookMacroEnabled = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2013, xlsm)
• XlFileFormat.xlExcel12 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro's, xlsb)
• XlFileFormat.xlExcel8 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)

Answer 2

Changing the extension doesn't automatically change the format of the file. You're just misnaming a .xls file as .xlsx. When you try to open the file again, Excel is expecting a .xlsx file, but getting a file with the .xls format. SaveCopyAs doesn't give the option to change the file format. Try SaveAs and use the FileFormat argument to specify that you want a .xlsx file.