user2322507 user2322507 - 1 year ago 235 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

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 = App.Workbooks.Open(TargetPath) ' code breaks here


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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download