Shaun Logan

I have created a simple document-based VSTO 2005 SE solution for Excel. I use ThisWorkbook.SaveCopyAs() to create a duplicate of the solution workbook. The duplicate opens fine. But, if I first delete Sheet1 from the solution and then use SaveCopyAs(), the duplicate fails to open with this message:

"The customization assembly could not be found or could not be loaded. You can still edit and save the document. Contact your administrator or the author of this document for further assistance".

The Details box is empty.

I can delete Sheet2 and I do not see the failure (the duplicate loads fine). I have seen this post: RemoveCusomization modified workbook , but while that may be related, it is kind of the opposite - he is trying to remove the customization, I am trying to preserve it.

This seems like a MSFT bug to me. Thanks in advance for any workarounds or suggestions. Here is the code:

Code Block

public partial class ThisWorkbook

{

private void ThisWorkbook_Startup (object sender, System.EventArgs e)

{

InitToolbar (); // add button toolbar to allow SaveCopyAs to be fired

}

private void ThisWorkbook_Shutdown (object sender, System.EventArgs e)

{

}

private void ToolbarBtn_Click (Office.CommandBarButton ctrl, ref bool cancelDefault)

{

string path = System.IO.Path.GetDirectoryName (this.FullName);

string copyFilename = System.IO.Path.Combine (path, "SaveCopyAsTest-copy.xls");

this.SaveCopyAs (copyFilename);

MessageBox.Show ("Solution copied to: " + copyFilename);

}

private void InitToolbar ()

{

Office.CommandBars commandBars = Application.CommandBars;

Office.CommandBar toolbar =

commandBars.Add ("SaveCopyAsTest", Type.Missing, Type.Missing, true);

toolbar.Position = Office.MsoBarPosition.msoBarTop;

Office.CommandBarButton btn = toolbar.Controls.Add (Office.MsoControlType.msoControlButton,

Type.Missing, Type.Missing, Type.Missing, true) as Office.CommandBarButton;

btn.Caption = "SaveCopyAsTest";

btn.Style = Office.MsoButtonStyle.msoButtonCaption;

btn.Visible = true;

btn.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler (ToolbarBtn_Click);

toolbar.Visible = true;

}

The steps to repro:

1. run the solution.

2. delete Sheet1

3. click the SaveCopyAsTest button in the toolbar

4. Close Excel

5. Open the copied file: SaveCopyAsTest-copy.xls

Observed: "assembly could not be found or loaded" error

Expected: workbook should load w/solution intact

(if you omit step 2 or delete a different worksheet, the workbook & solution load fine)




Re: Visual Studio Tools for Office Excel: SaveCopyAs from doc-based solution after deleting Sheet1, copied workbook fails to load

Ji Zhou C MSFT

Hi Scoober,

Try the codes below. It works in my side. And please pay attention to the bold part.

Code Block

private Office.CommandBars commandBars = null;

private Office.CommandBar toolbar = null;

private Office.CommandBarButton btn = null;

private void ThisWorkbook_Startup(object sender, System.EventArgs e)

{

InitToolbar(); // add button toolbar to allow SaveCopyAs to be fired

MessageBox.Show("Test");

}

private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)

{

}

private void ToolbarBtn_Click(Office.CommandBarButton ctrl, ref bool cancelDefault)

{

SendKeys.Send("^(s)"); //I add this line

string path = System.IO.Path.GetDirectoryName(this.FullName);

string copyFilename = System.IO.Path.Combine(path, "SaveCopyAsTest-copy.xls");

this.SaveCopyAs(copyFilename);

MessageBox.Show("Solution copied to: " + copyFilename);

}

private void InitToolbar()

{

commandBars = Application.CommandBars;

toolbar = commandBars.Add("SaveCopyAsTest", Type.Missing, Type.Missing, true);

toolbar.Position = Office.MsoBarPosition.msoBarTop;

btn = toolbar.Controls.Add(Office.MsoControlType.msoControlButton,

Type.Missing, Type.Missing, Type.Missing, true) as Office.CommandBarButton;

btn.Caption = "SaveCopyAsTest";

btn.Style = Office.MsoButtonStyle.msoButtonCaption;

btn.Visible = true;

btn.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(ToolbarBtn_Click);

toolbar.Visible = true;

}

All we need to do is saving the workbook before copy it via the button. The SaveCopyAs method only save a copy of the work book, but not modify the open workbook in memory. After we delete the sheet1, we should save it then Excel will help us to update the manifest embedded in the file. If we do not do so, the sheet1 is removed, but its corresponding manifest content is still there. This will results that excel cannot find sheet1 according to manifest that should be there with codes to load.

Ps, we should save the workbook manually or use SendKeys via code, but not just use this.Save(). That will not work because it only save the workbooks content without updating the manifest.

Thanks

Ji






Re: Visual Studio Tools for Office Excel: SaveCopyAs from doc-based solution after deleting Sheet1, copied workbook fails to load

Scoober

Thanks, I will try it. Can you tell me, though, is the code-around you suggest localization-friendly That is, in

SendKeys.Send("^(s)");

does the "^(s)" always map to File Save, regardless of the current locale settings and version of Excel I am concerned because I found this note in the doc at: http://msdn2.microsoft.com/en-us/library/ms171548.aspx

"If your application is intended for international use with a variety of keyboards, the use of System.Windows.Forms.SendKeys.Send(System.String) could yield unpredictable results and should be avoided."

My solution is intended for use with different locales, so I need an approach that will work in those different environments. Is there any other way to force the manifest information to be updated, so that the programmatic save of the workbook -- this.Save() -- will have the desired effect






Re: Visual Studio Tools for Office Excel: SaveCopyAs from doc-based solution after deleting Sheet1, copied workbook fails to load

Ji Zhou C MSFT

Hi,

I am positive that this will work in a U.S. Keyboard layout without dependency of locale setting. But another secure way will be prompt and request user to save the workbook first, before save a copy of current workbook.

In the button event handle:

Code Block

If(this.Saved)

{

//Do your action save copy

}

Else

{

MessageBox.Show(Please save the workbook first);

}

Thanks

Ji






Re: Visual Studio Tools for Office Excel: SaveCopyAs from doc-based solution after deleting Sheet1, copied workbook fails to load

Ji Zhou C MSFT

OK.

Another way is to find the Save button and Call its Execute method.

this.Application.CommandBars["Standard"].Controls["&Save"].Execute();

Thanks

Ji






Re: Visual Studio Tools for Office Excel: SaveCopyAs from doc-based solution after deleting Sheet1, copied workbook fails to load

Scoober

Thanks Ji.

re: "request user to save the workbook first"

thanks, but this does not meet my product's functional requirements - the SaveCopyAs needs to run unattended and cannot depend on a explicit user interaction to Save the workbook. (the same code is just a simple test app to illustrate the issue).

re: "this.Application.CommandBars["Standard"].Controls["&Save"].Execute();"

This looks promising. I guess I would probably apply the same approach but use the File menu's Save item, since it would be less likely for the end-user to have removed that item.

But I wonder if there is some locale-independent way to locate the correct CommandBar control I'm guessing that "&Save" as an indexer into the Controls collection is not going to work on a non-EN environment (or even on EN, if the user has renamed the command via Customize).

How about CommandBar.FindControl() which takes an ID - is there a list published someplace that will tell me the unique (unchanging) ID for the File > Save item

Thanks for your patience and assistance.

In the meantime, I have tried this:

Office.CommandBarControl saveCmd = Application.CommandBars.FindControl

(Type.Missing, 0x7532, Type.Missing, Type.Missing);

saveCmd.Execute();

Where I got the 0x7532 ID through inspection. This flashed up the File menu (and it did not appear to solve the underlying problem with the manifest).

Then I tried:

private const int SaveButtonControlId = 0x3; // via empirical inspection

. . .

Office.CommandBarControl saveCmd = this.Application.CommandBars.FindControl

(Office.MsoControlType.msoControlButton, SaveButtonControlId, Type.Missing, Type.Missing);

if (saveCmd != null)

{

saveCmd.Execute ();

}

And this works - saves the workbook, fixes up the manifest, and does not seem to affect the UI (no flashing).

So can I rely on ID=3 to uniquely identify this button






Re: Visual Studio Tools for Office Excel: SaveCopyAs from doc-based solution after deleting Sheet1, copied workbook fails to load

Ji Zhou C MSFT

Scoober wrote:

is there a list published someplace that will tell me the unique (unchanging) ID for the File > Save item

There is not an official published list tells the ID of each button or Menu Item. I think we can write codes to MessageBox the item's caption and ID in a loop. Then we will find the File->Save item's ID is also 3, not 7532. Codes like

foreach (Office.CommandBar cb in excelApp.CommandBars)
{
if (cb.Name == "Worksheet Menu Bar")
{
foreach (Office.CommandBarControl cbc in ((Office.CommandBarPopup)cb.Controls["&File"]).Controls)
{
MessageBox.Show(cbc.Caption + cbc.Id.ToString());
}
}
}

Scoober wrote:

So can I rely on ID=3 to uniquely identify this button

Yes, I think the system built-in ID 3 can be the unique identifier for the Save button.

Thanks

Ji