Partager via

FAQs for Visual Studio Tools for Office, Version 2005 Beta 1

  • Article

Here are some Microsoft Visual Studio Tools for the Microsoft Office System, Version 2005 (VSTO 2005) Beta 1 FAQs, courtesy of one of our VSTO technical support specialists at Microsoft.

Please note that these FAQs are pre-release documentation and are subject to change in future releases.

General Information

Q: Which editions of Office 2003 are supported with Visual Studio Tools for Office, Version 2005 Beta 1?
A: The following editions have been tested with Visual Studio Tools for Office, Version 2005 Beta 1:

  • Microsoft Office Professional Edition 2003
  • Microsoft Office Professional Enterprise Edition 2003

Q: Where can I find Visual Studio Tools for Office, Version 2005 Beta 1 sample projects?
A: Download samples for Visual Studio Tools for Office, Version 2005 Beta.

Q: Can I deploy the document and assembly from my built Visual Studio Tools for Office, Version 2005 Beta 1 project to another machine that is running Office 2003?
A: For the code in your assembly to execute, runtime components for Visual Studio Tools for Office, Version 2005 are required. The runtime components are only installed with Microsoft Visual Studio 2005 Beta 1 and are not presently available as a redistributable. Therefore, with Visual Studio Beta 1, your code will execute only if Visual Studio 2005 Beta 1 is installed on the target computer.

Project

Q: Certain Microsoft Office Excel and Microsoft Office Word menu commands are not available when I develop an Office project in Visual Studio.
A: 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 Microsoft Internet Explorer.

Examples of functionality that are not available in the Visual Studio development environment include:

Excel

  • Commands on the Excel Window menu, such as Freeze Panes and Split Window, are not available.
  • Commands on the Excel File menu, such as Print and Print Preview, are not available.
  • Add-ins, such as the Analysis ToolPack Add-in, do not load and are not accessible in the design environment.
  • New query features, like new Web query or new database query, are unavailable.

Word 

  • Commands on the Word Window menu are not available.
  • Commands on the Word File menu, such as Print and Print Preview, are not available.
  • Mail merge functionality is not available.

For more details, see "Office Documents in the Visual Studio Environment Overview" in the Visual Studio 2005 Beta documentation.

Q: When I create or build an Office project, warnings are generated which indicate that some classes in the project are not CLS-compliant.
A: When you create or build an Office project, warning messages will appear stating that the ThisWorkbook, Sheet1, Sheet2, Sheet3 or ThisDocument class is not CLS-compliant because it derives from a class which is not CLS-compliant. You can ignore these warnings. For additional information, see topic “6.3 Warning generated that classes are not CLS-Compliant” in the Microsoft Visual Studio 2005 Beta 1 Readme.

Q: When I choose the Publish command to publish my Office project, nothing happens.
A: The publish feature has not been implemented for Office projects in Visual Studio Tools for Office, Version 2005 Beta 1.

Q: After performing actions to bind data to my workbook (or document), each line of my code in the Sheet_Initialize handler (or the ThisDocument_Initialize handler) is encapsulated in a #ExternalSource block.
A: This is a known problem that is currently being investigated. You can remove the #ExternalSource and #End ExternalSource statements from your Initialize event code without consequence.

Q: Can I open two Excel projects or two Word projects at the same time?
A: If you create an Excel project or a Word project and then add a control to the workbook document in the designer, you cannot open a second instance of Visual Studio and create another project using the same Office application while the first project is still open. For additional information, see topic “9.6 Cannot open simultaneous Excel or Word Projects” in the Microsoft Visual Studio 2005 Beta 1 Readme.

Actions Pane

Q: When I add controls to the actions pane in Word, I receive an error “You are currently viewing this document in multiple windows.”
A: This is a known problem that will be fixed post-Beta 1. To resolve the problem in Beta 1, add code to the Initialize event of ThisDocument to clear the SolutionID property for the SmartDocument object before you add your first control to the actions pane.

' Visual Basic

Me.SmartDocument.SolutionID = ""
Me.ActionsPane.Controls.Add(new Button())

// C#

this.SmartDocument.SolutionID = "";
this.ActionsPane.Controls.Add(new Button());

For additional information, see the topic “6.8 Actions pane fails to load” in the Microsoft Visual Studio 2005 Beta 1 Readme.

Q: How can I control the size and location of the actions pane?
A: The actions pane is a component of the task pane in Excel and Word. The task pane may be accessed through the CommandBar collection.

' Visual Basic

Dim btn As New Button

' Add a button control to the actions pane.
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#

Button btn = new Button();

// Add a button control to the actions pane.
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

Q: Data binding with the local database data source type does not work.
A: Local database functionality is not fully implemented with Visual Studio Tools for Office, Version 2005 Beta 1. As an alternative, it is recommended that you use data binding to Microsoft SQL Server 2000. For additional information, see topic “15.11 Cannot data bind to local database in Excel” in the Microsoft Visual Studio 2005 Beta 1 Readme.

Q: How can I create a master-details view of my data on an Excel worksheet?
A: 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 to NamedRange controls and fields in the details table are complex data-bound to a ListObject. For more information about binding controls in Visual Studio Tools for Office, Version 2005, see "Views and View Controls Overview" in the Visual Studio 2005 Beta 1 documentation.

To create a new Excel project and add a data source:

  1. Create a new Excel project.
  2. On the Data menu, click Add New Data Source. The Data Source Configuration Wizard appears.
  3. Click Next.
  4. Select Database and click Next.
  5. Click New Connection. The Connection Properties dialog box appears.
  6. In the Connection Properties dialog box, specify the server name, select the database Northwind and click OK.
  7. Click Next.
  8. Click Next.
  9. Expand the Tables in the list of database objects.
  10. Select the Categories table and the Products table and click Finish.

Bind fields in the data source to controls on your workbook:

  1. In the Data Sources window, expand the Categories table.
  2. Drag the CategoryID field from the Data Sources window and drop it onto Sheet1!A1.
  3. Drag the CategoryName field from the Data Sources window and drop it onto Sheet1!A2.
  4. Drag the Products table from the Data Sources window and drop it onto Sheet1!A4 to create a bound ListObject named List1.
  5. With List1 still selected, in the Properties window, change the DataSource property to CategoriesDataConnector and the DataMember property to FK_Products_Categories.

Add a button to navigate records in the data source:

  1. Drop a Button control at Sheet1!D1.
  2. Add the following code to the Click event of the Button control:

' Visual Basic

Me.categoriesDataConnector.MoveNext()

// C#

this.categoriesDataConnector.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 that the Products records in the list object (details) reflects the current Categories record (master).

Q: 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)?
A: To cache a DataSet, you simply set the CacheInDocument property of the DataSet to True. While a document is loading, you can check the IsCacheInitialized property of the DataHost object to determine whether or not the document contains cached data. If the document contains a cached DataSet, then the cached data set will automatically be used for the data bindings in the document.

To create a new project and add a data source:

  1. Create a new Excel Application or Word Application project.
  2. On the Data menu, click Add New Data Source. The Data Source Configuration Wizard appears.
  3. Click Next.
  4. Select Database and click Next.
  5. Click New Connection. The Connection Properties dialog box appears.
  6. In the Connection Properties dialog, specify the server name, select the database Northwind and click OK.
  7. Click Next.
  8. Click Next.
  9. Expand the Tables in the list of database objects.
  10. Select the Employees table and click Finish.

To bind fields in the data source to view controls:

  1. On the Data menu, click Show Data Sources.
  2. In the Data Sources window, expand the Employees table.
  3. Drag the EmployeeID field from the Data Sources window and drop it onto the Excel worksheet (or the Word document).
  4. Drag the FirstName field from the Data Sources window and drop it onto the Excel worksheet (or the Word document).
  5. Drag the LastName field from the Data Sources window and drop it onto the Excel worksheet (or the Word document).
  6. Select NorthwindDataSet in the component tray.
  7. Set the CacheInDocument property of NorthwindDataSet to True.
  8. Add two button controls to the Excel worksheet (or the Word document).
  9. On the View menu, click Code.
  10. Add the following code to Sheet1.vb (or ThisDocument.vb) to handle the Click events of the Button controls:

' Visual Basic

Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles Button1.Click

Me.EmployeesDataConnector.MovePrevious()

End Sub

Private Sub Button2_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles Button2.Click

Me.EmployeesDataConnector.MoveNext()

End Sub

// C#

private void Button1_Click(object sender, EventArgs e)
{
this.employeesDataConnector.MovePrevious();
}

private void Button2_Click(object sender, EventArgs e)
{
this.employeesDataConnector.MoveNext();
}

   11. Replace the code in the Initialize event with the following:

' Visual Basic

If Not (Me.DataHost.IsCacheInitialized) Then
Me.EmployeesTableAdapter.Fill(Me.NorthwindDataSet.Employees)
End If

Button1.Text = "<<"
Button2.Text = ">>"

// C#

if (!this.DataHost.IsCacheInitialized)
{
this.employeesTableAdapter.Fill(this.northwindDataSet.Employees);
}

this.Button1.Text = "<<";
this.Button2.Text = ">>";

To test the project:

  1. Press the F5 key to build and run the project.
  2. Modify the FirstName field of the first record.
  3. Click >> to move to the next record.
  4. Modify the FirstName field of the second record.
  5. Click >> to move to the next record.
  6. On the File menu, click Save As to save the Excel workbook as Cache.xls (or the Word document as Cache.doc).
  7. Close Cache.xls (or Cache.doc).
  8. Open Cache.xls (or Cache.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.

Q: How can I perform updates to a data source when changes are made to bound controls on my Excel workbook or Word document?
A: When you are working with data 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 the EndEdit method of the DataConnector. To send your updates from your in-memory DataSet to the actual data source, use the Update method of the associated DataTableAdapter. 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

' End editing on the current record...
Me.EmployeesDataConnector.EndEdit()

' ...and send the updates to the data source.
Me.EmployeesTableAdapter.Update(Me.NorthwindDataSet.Employees)

// C#

// End editing on the current record...
this.employeesDataConnector.EndEdit();

// ...and send the updates to the data source.
this.employeesTableAdapter.Update(this.northwindDataSet.Employees);

For more information, see "Introduction to DataSet Updates" in the Visual Studio 2005 Beta 1 documentation.

Controls

Q: Certain controls are not available in the Toolbox when I am working with the Workbook or Document designer.
A: 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. Common examples are the GroupBox and the DataNavigator 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, see the next question "How do I host a user control on my Excel workbook or Word document?".

Q: How do I host a user control on my Excel workbook or Word document?
A: 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 the Project menu, click Add New Item. The Add New Item dialog box appears.
  2. In the list of templates, select User Control and click Add. UserControl1 is added to your project and opens in the designer.
  3. From the Toolbox, drag a GroupBox control and drop it onto UserControl1.
  4. Resize the GroupBox control to fit inside user control.
  5. Add two RadioButton controls inside the GroupBox control.
  6. On the File menu, click Close to close the user control designer.
  7. On the Build menu, click Build.
  8. After building the project, you will now see a new tab in the Toolbox named Controls that contains UserControl1.
  9. Drag UserControl1 from the Toolboxand drop it onto your document or workbook.

Q: The dropdown list of a ComboBox control on an Excel workbook or Word document does not appear on the first click. When a ComboBox control is sited on a workbook or document, clicking the dropdown arrow on the ComboBox control does not expand the list if the ComboBox control does not already have the focus.
A: This is a known bug.

Q: 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).
A: This problem is currently under investigation. As an alternative, you can change the BackColor 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 Form control onto the document.

To set the BackColor property of the control to match the shading range in the document:

  1. Select the shaded range in the Word document.
  2. On the Format menu, click Borders and Shading and select the Shading tab.
  3. Click More Colors. The Colors dialog box appears.
  4. Select the Custom tab. Note the Red, Green and Blue values that represent the color you have selected.
  5. Click Cancel to close the Colors dialog box and click Cancel to close the Borders and Shading dialog box.
  6. Select the Windows Form control on the document.
  7. In the Properties window, change the BackColor 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 example 250,150,10).

Q: How can I iterate a collection of controls on an Excel workbook or Word document to set a property for those controls?
A:

' Visual Basic

Dim i As Short

For i = 0 To Me.Controls.Count - 1
Dim ctrl As Windows.Forms.Control ctrl = _
DirectCast(Me.Controls(i), Windows.Forms.Control)
If Not (ctrl Is Nothing) Then
'Set a property on the Windows Form control, like:
ctrl.Enabled = False
End If
Next

// C#

short i;

for (i = 0; i < this.Controls.Count; i++)
{
System.Windows.Forms.Control ctrl;
ctrl = (System.Windows.Forms.Control)this.Controls[i];
if (ctrl != null) {
// Set a property on the Windows Form control, like:
ctrl.Enabled = false;
}
}

-- Paul Cornell

-----
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.

Comments