Exception thrown when I use Update method
General Information
Which editions of Office 2003 are supported with Visual Studio 2005 Tools for Office, Beta 2?
The following editions have been tested with Visual Studio 2005 Tools for Office, Beta 2:
·Microsoft Office Professional Edition 2003 with Service Pack 1
·Microsoft Office Professional
·Microsoft Office Excel 2003 Standalone with Service Pack 1
·Microsoft Office Word 2003 Standalone with Service Pack 1
Can I deploy the document and assembly from my built Visual Studio 2005 Tools for Office, Beta 2 project to another machine that is running Office 2003?
For the code in your assembly to execute, runtime components for Visual Studio 2005 Tools for Office are required. A redistributable version of the Visual Studio 2005 Tools for Office runtime components is available for download.
http://www.microsoft.com/downloads/details.aspx?FamilyID=de37bfe4-7a5a-4767-bbe1-267418e0f1e7&DisplayLang=en
Does Visual Studio 2005 Tools for Office add any additional functionality to the Microsoft Office Outlook? object model (i.e. help classes, utility components, namespaces, etc…)?
No, Visual Studio 2005 Tools for Office does not add any additional functionality to the Outlook object model. Any coding logic, methodology, and/or workaround you used with a Shared Add-in project will work the same in a Visual Studio Tools for Office – Outlook Add-in.
What are the essential differences between a Visual Studio 2005 Tools for Office Outlook Add-in and a Visual Studio Shared Add-in extensibility project?
·A new Visual Studio Tools for Office – Outlook Add-in project has only 2 methods: “ThisApplication_Startup” (called when your add-in is loaded) and “ThisApplication_Shutdown” (called when you add-in is unloaded).
·Visual Studio 2005 Tools for Office -Outlook add-ins do not require you to build a shim for every add-in that you build for the add-in to be trusted. The Visual Studio 2005 Tools for Office Outlook loader (which loads your add-in) is trusted by Outlook and therefore so is your add-in.
·Add-ins built with Visual Studio 2005 Tools for Office are now unloaded as expected. This resolves the problem of Outlook not completely shutting down because it is waiting for an add-in to unload itself.
·Deployment is the biggest difference between the two project types. Depending on your deployment environment running the produced setup application will not be the apart of the steps. Deploying registry keys and a manifest will be necessary in some scenatios. For more information please review the “Deploying Office Solution” in the “MSDN Library for Visual Studio 2005” under Development Tools and Languages -> Visual Studio -> Visual Studio Tools For Office -> Deploying Office Solutions
Project
Certain Microsoft Office Excel? and Microsoft Office Word menu commands are not available when I develop an Office project in Visual Studio.
When Excel and Word are hosted in the Visual Studio development environment, certain functionality is unavailable; this behavior is consistent with the behavior you will observe when Excel and Word are hosted in other document containers like Internet Explorer. Examples of functionality that are not available in the Visual Studio development environment include:
Excel
·Commands on the ExcelWindow menu, such asFreeze Panes andSplitWindow, are not available.
·Commands on the ExcelFile menu, such asSaveAs andPrint Preview, are not available.
·Add-ins, such as theAnalysis ToolPack add-in, do not load and are not accessible in the design environment.
·New query features, likeNew Web Query orNew Database Query, are unavailable.
Word
·Commands on the WordWindow menu are not available.
·Commands on the WordFile menu, such asPrint andPrint Preview, are not available.
·Mail Merge functionality is not available.
For more details, seeOffice Documents in the Visual Studio Environment Overview in the Visual Studio 2005 Beta documentation.
When I choose the Publish command to publish my Office project, the publish command succeeds but a warning message is displayed.
This is currently under investigation.
When I attempt re-Publish my Office project, Visual Studio hangs or an error message is displayed.
This is currently under investigation, however, you may be able to workaround the problem by shutting down and restarting Visual Studio and then try to Publish the solution.
I have built an Office project and would like to use the assembly from that project with another document. Can this be done?
Yes, using the ServerDocument object’s AddCusomization method you can “attach” a VSTO assembly to another project.
Note:If the VSTO assembly relies on controls being on the document, AddCustomization will fail.Controls on Windows? Forms or on the actions pane should work without a problem, but controls that are located on the document, such as a bookmark or a command button that is inline with text, will not initialize properly and the assembly will fail to load.
Attempting to create a property page in a Visual Studio 2005 Tools for Office - Outlook add-in fails at the line of code Page.Add(…) with the exception message “The operation failed.”However, the same line of code works fine in a Shared Add-in project.
The problem is that Outlook creates property pages using COM. In your IDTExtensibility add-in, the project was set to be registered for COM interop as well to “Make the assembly COM-Visible”.So a typelib was built and registered that included the property page.
In your Visual Studio 2005 Tools for Office - Outlook add-in, the default is to NOT “Register for COM interop” or “Make the assembly COM-Visible” (because Visual Studio 2005 Tools for Office does its own registration). Unfortunately, this means that a typelib is also not registered and thus your property page is not COM-creatable.
To fix this, you can just set your Visual Studio 2005 Tools for Office - Outlook add-in project properties to specify “Register for COM interop” as well as “Make the assembly COM-Visible”.This builds and registers the typelib, and everything works fine.
·The “Register for COM interop” setting can be found by clicking [project name] Properties on the Project menu.Select the “Build” tab. You will find the check box near the bottom of the property page.
·The “Make the assembly COM-Visible” setting can be found by clicking[project name] Properties on the Project menu.Select the Application tab and click the “Assembly Information …” button.You will find the check box near the bottom of the dialog box.
Using Visual Studio 2005 Tools for Office, I created an Outlook add-in and registered for an event handler(i.e. ClickEventHandler or …ItemEventHandler) but it does not consistently fire.
This might happen if the variable that holds the item that you are registering the event to is declared in ThisApplication_Startup, or some other method. As soon as the method is executed, the variable is no longer in scope, and it's eligible for garbage collection. After the garbage collector runs, the event will not fire anymore. To avoid this, declare the variable at the class level instead.
My Visual Studio 2005 Tools for Office Outlook add-in doesn’t run as expected, but I don’t receive an exception either.
When an exception is thrown by a managed add-in, the common language runtime interop layer propagates this back to the host Office application (Outlook in this case) as a failed HRESULT. When Office receives this failed HRESULT from a method call, it just absorbs it and continues. This protects the host. So in short this is by design. If you would prefer to handle the exception yourself, add exception handling to your code to get a chance at handling the exception first.
A Visual Studio 2005 Tools for Office add-in has been hard disabled and afterwards no other Visual Studio 2005 Tools for Office add-ins will load.
This is by design. Visual Studio 2005 Tools for Office does not interfere with Office’s blacklist mechanism, and any Visual Studio 2005 Tools for Office - Outlook add-in that causes the host to crash during startup will blacklist the add-in loader and therefore all other Visual Studio 2005 Tools for Office - Outlook add-ins.
Hard Disabling
Hard disabling occurs when Outlook closes unexpectedly or you stop the debugger while the constructor or the Startup event handler is executing. In this case, Outlook might disable both the add-in and VSTAddin.dll. VSTAddin.dll is used to load add-ins created with Visual Studio 2005 Tools for Office. When hard disabling occurs, all add-ins created by using Visual Studio 2005 Tools for Office are prevented from loading for the current user.
Actions Pane
How can I control the size and location of the actions pane?
The actions pane is a component of the task pane in Excel and Word. The task pane can be accessed through theCommandBar collection.
Visual Basic
Me.SmartDocument.SolutionID = "" btn.Text = "Click Here!" Me.ActionsPane.Controls.Add(btn) Dim bar As Microsoft.Office.Core.CommandBar bar = ThisApplication.CommandBars("Task Pane") bar.Position = Microsoft.Office.Core.MsoBarPosition.msoBarLeft bar.Width = 120 'Or to locate the actions pane horizontally: 'Bar.Position = Microsoft.Office.Core.MsoBarPosition.msoBarBottom 'Bar.Height = 150 |
C#
this.SmartDocument.SolutionID = ""; btn.Text = "Click Here!"; this.ActionsPane.Controls.Add(btn); Office.CommandBar Bar = ThisApplication.CommandBars["Task Pane"]; Bar.Position = Office.MsoBarPosition.msoBarLeft; Bar.Width = 120; // Or to locate the actions pane horizontally: //Bar.Position = Office.MsoBarPosition.msoBarBottom; //Bar.Height = 150; |
Data
How can I create a master-details view of my data on an Excel worksheet?
To create a master-details view, you will need database objects in your data source that have a relation defined. The following steps illustrate how to create a master-details view of the Categories and Products tables in the SQL Server 2000 Northwind sample database. In this example, the Categories table represents the master and the Products table represents the details. Fields in the master table are simple data-bound toNamedRange controls and fields in the details table are complex data-bound to aListObject. For more information about binding controls in Visual Studio 2005 Tools for Office, seeHost Items and Host Controls Overview in the Visual Studio 2005 Beta documentation.
To create a new Excel project and add a data source:
1.Create a new Excel project.
2.On theData menu, clickAdd New Data Source. TheData Source Configuration Wizard appears.
3.SelectDatabase and clickNext.
4.ClickNew Connection. TheConnection Properties dialog box appears.
5.In theConnection Properties dialog box, specify the server name, select the databaseNorthwindand clickOK.
6.ClickNext.
7.ClickNext.
8.Expand theTables in the list of database objects.
9.Select theCategories table and theProducts table and clickFinish.
Bind fields in the data source to controls on your workbook:
1.In theData Sources window, expand theCategories table.
2.Drag theCategoryID field from theData Sources window and drop it onto Sheet1!A1.
3.Drag theCategoryName field from theData Sources window and drop it onto Sheet1!A2.
4.Drag theProducts table from theData Sources window and drop it onto Sheet1!A4 to create a boundListObjectnamed List1.
5.WithList1 still selected, in theProperties window, change theDataSource property toCategoriesBindingSource and theDataMember property toFK_Products_Categories.
Add a button to navigate records in the data source:
1.Drop aButton control at Sheet1!D1.
2.Add the following code to theClick event of theButton control:
Visual Basic
Me.CategoriesBindingSource.MoveNext() |
C#
this.categoriesBindingSource.MoveNext(); |
Now you can build and run the project. Click the button to navigate through the data source. Observe that as you click the button, the Products records in the list object (details) reflect the current Categories record (master).
How do I store a DataSet in the document cache and then use that DataSet with bound controls on my Excel workbook (or Word document)?
To cache aDataSet, you simply set theCacheInDocument property of theDataSet toTrue. While a document is loading, you can check theIsCacheInitialized property of theDataHost object to determine whether or not the document contains cached data. If the document contains a cachedDataSet, then the cachedDataSet will automatically be used for the data bindings in the document.
To create a new project and add a data source:
1.Create a newExcel Application orWord Application project.
2.On theData menu, clickAdd New Data Source. TheData Source Configuration Wizard appears.
3.SelectDatabase and clickNext.
4.ClickNew Connection. TheConnection Properties dialog box appears.
5.In theConnection Properties dialog, specify the server name, select the databaseNorthwindand clickOK.
6.ClickNext.
7.ClickNext.
8.Expand theTables in the list of database objects.
9.Select theEmployees table and clickFinish.
To bind fields in the data source to host controls:
1.On theData menu, clickShow Data Sources.
2.In theData Sources window, expand theEmployees table.
3.Drag theEmployeeID field from theData Sources window and drop it onto the Excel worksheet (or the Word document).
4.Drag theFirstName field from theData Sources window and drop it onto the Excel worksheet (or the Word document).
5.Drag theLastName field from theData Sources window and drop it onto the Excel worksheet (or the Word document).
6.By default the fields that were added in the steps above were added as NamedRange controls to the worksheet. To enable editing of the FirstName and LastName NamedRange controls on the worksheet, follow the steps below:
·Select theFirstName NamedRange on the worksheet.
·In theProperties window, expand theData Bindings property.
·Click on the(Advanced) property and then click the button to display theFormatting and Advanced Data Binding dialog.
·Change theData Source Update Mode for theValue2 property fromNever toOnValidation.
·Click theOK button to apply the changes.
·Repeat these steps for theLastName NamedRange control.
7.SelectNorthwindDataSet in the component tray.
8.Set theModifiers property of theNorthwindDataSet toPublic and then set theCacheInDocument property ofNorthwindDataSet toTrue.
9.Add two button controls to the Excel worksheet (or the Word document).
10.On theView menu, clickCode.
11.Add the following code toSheet1.vb (orThisDocument.vb) to handle theClick events of theButton controls:
Visual Basic
C#
12.Replace the code in theStartup event with the following:
Visual Basic
C#
To test the project:
1.Press the F5 key to build and run the project.
2.Modify theFirstName field of the first record.
3.Click>> to move to the next record.
4.Modify theFirstName field of the second record.
5.Click>> to move to the next record.
6.On theFile menu, clickSave As to save the Excel workbook as Cache.xls(or the Word document asCache.doc).
7.CloseCache.xls(orCache.doc).
8.OpenCache.xls(orCache.doc). Note: The data is loaded from the document cache.
9.Click>> to navigate the records and observe that your changes to the cached data were preserved.
How can I perform updates to a data source when changes are made to bound controls on my Excel workbook or Word document?
When you are working with data that is bound to controls on your workbook or document, understand that you are working with an in-memory copy of the actual data. As you navigate away from a record in your data source, any changes you made to fields in that record will be stored in the in-memory data. You can also save your changes using theEndEdit method of theDataConnector. To send your updates from your in-memoryDataSet to the actual data source, use theUpdate method of the associatedDataTableAdapter.
The sample code below illustrates how you can send updates for controls bound to fields in the Employees table of the SQL Server 2000 Northwind sample database:
Visual Basic
Me.EmployeesBindingSource.EndEdit() 'And send the updates to the data source. Me.EmployeesTableAdapter.Update(Me.NorthwindDataSet.Employees) |
C#
this.employeesBindingSource.EndEdit(); //And send the updates to the data source. this.employeesTableAdapter.Update(this.northwindDataSet.Employees); |
Controls
Certain controls are not available in the Toolbox when I am working with the workbook or document designer.
This is by design.
Certain Windows Forms controls cannot be hosted directly on an Excel workbook or Word document and therefore will not appear in the Toolbox in Visual Studio. Examples include theGroupBox and theDataNavigator controls.
To host these controls on a workbook or document, you can incorporate them into your own user control and then host your user control on the workbook or document. For an example, seeHow do I host a user control on my Excel workbook or Word document?.
How do I host a user control on my Excel workbook or Word document?
You can add user controls to your Excel workbooks or Word documents in much the same way you would add other Windows Forms controls.
1.On theProject menu, clickAddNew Item. TheAdd New Item dialog box appears.
2.In the list of templates, selectUser Control and clickAdd.UserControl1 is added to your project and opens in the designer.
3.From theToolbox, drag aGroupBox control and drop it ontoUserControl1.
4.Resize theGroupBox control to fit inside user control.
5.Add twoRadioButton controls inside theGroupBox control.
6.On theFile menu, clickClose to close the user control designer.
7.On theBuild menu, clickBuild <YourProjectName>.
8.After building the project, you will see a new tab in theToolbox named<YourProjectName> Controlsthat contains UserControl1.
9.DragUserControl1 from theToolbox and drop it onto your document or workbook.
The dropdown list of a ComboBox control on an Excel workbook or Word document does not appear on the first click.
When aComboBox control is on a workbook or document, clicking the dropdown arrow on theComboBox control does not expand the list if theComboBox control does not already have the focus.
This is a known bug.
When I change the BackColor of a control on my workbook (or document) to Transparent, the control does not appear transparent on the workbook (or document).
This problem is currently under investigation. As an alternative, you can change theBackColor of the control to the same color you use on your document.
For example, assume that you have a table with color shading on a Word document and you drop a Windows Forms control onto the document.
To set theBackColor property of the control to match the shading range in the document:
1.Select the shaded range in the Word document.
2.On theFormat menu, clickBorders and Shading and select theShading tab.
3.ClickMore Colors. TheColors dialog box appears.
4.Select theCustom tab. Note theRed,Green andBlue values that represent the color you have selected.
5.ClickCancel to close theColors dialog box and clickCancel to close theBorders and Shading dialog box.
6.Select the Windows Forms control on the document.
7.In theProperties window, change theBackColor property to the red, green and blue values you noted in step 4. You should enter the color property in the format r,g,b (for example250,150,10).
How can I iterate through a collection of controls on an Excel workbook or Word document to set a property for those controls?
Visual Basic
ctrl.Enabled = False End If Next |
C#
if (ctrl != null) { //Set a property on the Windows Forms control. ctrl.Enabled = false; } } |
