user2322507 user2322507 - 3 months ago 81
Vb.net Question

Save a file in .xlsx format in vb

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

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.

Comments