Trying to open a excel template and rename or save to new location

Question

I get the following error message when I try the following:

   Dim XL As New Microsoft.Office.Interop.Excel.Application

    XL.Visible = True
    XL.Workbooks.Open(XLTemplatePath)
    XL.SaveWorkspace(XLSaveReportPath)
    XL.Workbooks.Close()
    XL.Workbooks.Open(XLSaveReportPath)

"Excel cannot open the file 'ContactReports.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."

What I would like to do is Open a excel file that is the XLTemplatePath and the either rename or save the file at the XLSaveReportPath and then use that renamed/saved file to fill the report out.

I am using Visual Studio 2008 in VB.NET

Answer 1

I have used the following Visual Basic code to work with Microsoft Excel 2003. The key difference (other than Visual Basic 6 vs VB.Net) is that I use the SaveAs method of the Workbook object rather than the SaveWorkspace method.

Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True
Set ExcelDoc = ExcelApp.Workbooks.Open(FileName:=XLTemplatePath, ReadOnly:=True)
ExcelDoc.SaveAs(FileName:=XLSaveReportPath)
ExcelDoc.Close(SaveChanges:=False)
ExcelApp.Workbooks.Open(FileName:=XLSaveReportPath)

Answer 2

The saveworkspace method does not save the file. It save the workspace in the format xlw even though you are naming the file you are saving the workspace in as xls. When you try and open the document you are opening a worspace and you recieve the error.

To have this work correctly you need to get the workbook so you can sabe the workbook instead of the application.

Dim XL As New Microsoft.Office.Interop.Excel.Application
Dim XLWorkbook as new Microsoft.Office.Interop.Excel.Workbook  

XL.Visible = True  
XLWorkbook = XL.Workbooks.Open(XLTemplatePath)  
XLWorkbook.SaveAs(XLSaveReportPath)  
XL.Workbooks.Close()  
XL.Workbooks.Open(XLSaveReportPath)

Should do the trick. Let us know if you have any problems.

Heare are the MSDN refrences for your review:

Application.SaveWorkspace Method

Workbook.SaveAs Method