Ryan Taylor

Hello,

We have created an excel addin that allows the user to create a copy of their excel work into a completely new file without having to save the original changes (using SaveCopyAs(..)). This works really well for xls files, however if the user (using Excel 2003 SP2 with the compatability pack) opens an Excel 2007 workbook (.xlsx) and clicks our addin button, the new file is not created correctly.

Both examples below use the same code:

Example 1 (working): User opens original.xls, makes changes, clicks our addin button that creates a new file with the the content of the workbook. newfile.xls is created and works.

Example 2 (not working): User opens original2007.xlsx, makes changes, clicks our addin button that creates a new file with the contents of the workbook, new2007file.xlsx is created, however, the user is not able to open new2007file.xlsx. this error is shown when the user attempts to open the new file:

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

If I change the file extension to .xls, it will open just fine. If anyone knows how I can continue to use the SaveCopyAs(..) function and keep the file as a .xlsx and have it open, I would be greatly appreciative!

This is Excel 2003 (11.8033.8036) SP2 with the compatibility pack installed.

Thanks,
Ryan


Re: Visual Studio Tools for Office SaveCopyAs not saving xlsx files correctly in excel 2003

Dennis Wallentin

Hi Ryan,

First of all, thanks for taking the time to give a good description here as well as in the other thread.

What You may consider to test is the Microsoft Office Compatiblity Pack which gives Office 2003 the ability to open, edit and save
Office 2007 created files.






Re: Visual Studio Tools for Office SaveCopyAs not saving xlsx files correctly in excel 2003

Ryan Taylor

Hi Dennis,

Thank you for your reply. I can confirm that my Excel (2003 SP2) can indeed save/edit/open office 2007 files (including .xlsx).

I wonder if has to do with the PIA object (I think this is the correct term) for Excel. What I mean is, when I am accessing the FileFormat property of the current book it equals 51 in the situation where my excel file is of the new excel format (excel 2007, .xlsx).

Workbook currentWorkbook = Microsoft.Office.Interop.Excel.Application.ActiveWorkbook;

Just checking what the current value for my workbook reveals 51. To set FileFormat there is an emueration here: Microsoft.Office.Interop.Excel.XlFileFormat that includes all the possible options, however, since I am using office 2003 (excel 2003 specifically), I don't have any entry in the enumeration that will give me 51.

For a list of the possible values for Excel 2003:
http://www.officewiki.org/default.aspx/Excel.XlFileFormat

For a list of the possible values when dealing with Excel 2007:
http://msdn2.microsoft.com/en-us/library/bb241279.aspx

You will notice in the Excel 2007 enumeration, 51 does indeed exist as xlWorkbookDefault.

I don't know if this is my problem or not, but it is one suspicion. Thanks again for help.

Ryan




Re: Visual Studio Tools for Office SaveCopyAs not saving xlsx files correctly in excel 2003

Dennis Wallentin

Ryan,

My first attempt was to open an Excel 2007 file (xlsx) in Excel 2003 and then save it under a different name with the following VBA code:

Sub Save_Workbook()
Dim wbBook As Workbook

Set wbBook = Application.ActiveWorkbook

wbBook.SaveAs Filename:="c:\Dennis.xlsx", FileFormat:=51

End Sub

This procedure works as expected.

My second attempt was to open an Excel 2007 file (xlsx) in Excel 2003 and then save it under different name via a managed add-in VB.NET) with the following code:

Private Sub cbBtn_Click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean) Handles cbBtn.Click

Dim wbBook As Excel.Workbook

wbBook = g_xlApp.ActiveWorkbook

wbBook.SaveAs(Filename:="c:\XL.xlsx", FileFormat:=51)

End Sub

This procedure works also as expected. I haven't tested it with a VSTO managed add-in but in view of the above it should work as well.

Finally, I opened the above created files in Excel 2003 and also in Excel 2007 without any problems.






Re: Visual Studio Tools for Office SaveCopyAs not saving xlsx files correctly in excel 2003

Ryan Taylor

Hi Dennis,

Thank you for continuing to work with me on this issue. Actually, the SaveAs works without a problem. We have 2 sub routines that get executed and one of them does indeed do the 'SaveAs' and it works perfectly. The other situation is when we want to do a SaveCopyAs(..), which doesnt seem to work as expected.

Example Code for SaveAs (which works):

Code Snippet

Workbook currentBook = Excel.Application.ActiveWorkbook;

object missing = Type.Missing;

currentBook.SaveAs(c:\newfile.xlsx, missing, pword, pword, missing, missing,

Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing,

missing, missing, missing);

// Here we could specify file format if we needed to, right after file path, but it works without it

Example Code for SaveCopyAs (which doesnt work):

Code Snippet

currentBook.SaveCopyAs(c:\newfile.xlsx);

// Note, in SaveCopyAs(...) you cannot specify file format, only the path to the new file

The latter will save the file as an xlsx but it will not open unless I change the extension to .xls manually, after the file has been created. An interesting development is that when i do this test on an Excel 2007 machine, it will indeed save it as xlsx and it will open it with problem!

Thanks again,

Ryan





Re: Visual Studio Tools for Office SaveCopyAs not saving xlsx files correctly in excel 2003

Dennis Wallentin

Ryan,

I apologize for being unclear on some central aspects in this thread. First I didn't read Your first post which pointed out that the compatibility package already was in use. Second, my previously post was only half but not really targeting the issue.

I can repeat the issue when using SaveCopyAs, i e I get the same error, no matter if Excel 2003 is installed side by side with Excel 2007 or as standalone.

This behavior should be considered as a bug unless MSFT has other information available.






Re: Visual Studio Tools for Office SaveCopyAs not saving xlsx files correctly in excel 2003

Misha Shneerson - MSFT

Excel team has confirmed it is a bug and it is now logged in their DB.

The response from Excel:

Yep, definitely a bug here¡­opened

The workbook saved isn¡¯t really corrupted, its a biff8(.xls) workbook with the .xlsx extension, when the user tries to open it the converter fails since it expects a new file format .xlsx workbook and not a biff8(.xls) workbook.






Re: Visual Studio Tools for Office SaveCopyAs not saving xlsx files correctly in excel 2003

Ryan Taylor

Thank you guys for helping me with this problem. Do you know of any work around in the meantime We really like the ability to create a copy of the current workbook without having to save the original.

Thanks again,
Ryan




Re: Visual Studio Tools for Office SaveCopyAs not saving xlsx files correctly in excel 2003

Misha Shneerson - MSFT

I do not know other workarounds besides using SaveAs.




Re: Visual Studio Tools for Office SaveCopyAs not saving xlsx files correctly in excel 2003

lb83

I am currently getting a similar error when I have a .xls file that is password protected. I then do 'save as' .xlsx and I get an error

"the document was saved successfully but Excel cannot open it because of a sharing violation." - noone else is using the file btw

Then when I try to open the .xlsx file I get the error:

"Excel cannot open the file <filename>.xlsx" becasue the format or file extension is not valid. verify the file has not been corrupted and that the file extension matches the format of the file."

I am also seeing similar cases of this for Word and PowerPoint (though wording is different). I am running on XP with Office 2007. If I also created the file on a machine with XP Office 2003 with compatibility pack I get the same results as discribed above.





Re: Visual Studio Tools for Office SaveCopyAs not saving xlsx files correctly in excel 2003

EliotWilson

I am having the exact same problem opening a protected file with Office 2007 on Vista.

In my case, it's either a password protected file I created or one I've received from another user.
I cannot open the Exchange 2007 or DPM storage calculator.

I've tried repairing the install and installing all updates, but no luck.

Anyone else had this problem

Thanks,
Eliot




Re: Visual Studio Tools for Office SaveCopyAs not saving xlsx files correctly in excel 2003

BrigitteM

Hi,

I can confirm the same behaviour for Office 2007 on XPSP2.

I create a password protected file in 2003-compatibility mode which opens, saves and reopens fine.

As soon as I use "save as" to convert the file to xlsx I get the message

"Excel cannot open the file 'filename.xlsx' because the file format for the file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

Is there any solution for this issue in the meantime

ciao

Brigitte