Working with Data Programmatically in Visio 2007

  • Article

Summary:   Learn how to work with data in Microsoft Office Visio 2007 to connect to an existing data source, link the data in the data source, and display the linked data graphically.

Office Visual How To

Applies to:   2007 Microsoft Office System, Microsoft Office Visio 2007, Microsoft Office Excel 2007

Saul Candib, Microsoft Corporation

September 2007

Overview

Working with data in Microsoft Office Visio 2007 is a four-step process:

  1. Connecting to an existing data source.

  2. Linking the data in the data source to new or existing shapes in a Visio drawing.

  3. Displaying the linked data graphically.

  4. Refreshing linked data.

This how-to article and the associated video show you how to do the first three steps programmatically. They show you how to connect to data in a simple Microsoft Office Excel 2007 worksheet, how to create new shapes and simultaneously link the data to them in a Visio drawing, and then how to display the data associated with the shapes graphically.

The fourth step, refreshing linked data, is beyond the scope of this article.

 See It Video

Watch the Video

Length: 09:30 | Size: 8.0 MB | Type: WMV file

Code It | Read It | Explore It

Code It

Creating an Excel Worksheet to Use as a Data Source

In this example, you create a data source to connect to by creating a simple Excel worksheet that displays sales by calendar quarter.

To create an Excel worksheet

  1. Open Excel 2007, and in the blank worksheet, enter the data shown in Figure 1.

    Figure 1. Sales data in an Excel worksheet

    Sales data worksheet

  2. Double-click the Sheet1 worksheet tab, name the worksheet Sales by Region, and then save the workbook to a location on your computer with the file name Sales Data.xlsx.

NoteNote

If you create the new worksheet in Excel 2003 or earlier, name the file Sales Data.xls.

Opening a New Visio Drawing and the Visual Basic Editor

Next, you open a new drawing in Visio and the built-in Visual Basic Editor, where you can enter the code.

This example uses a custom Visio template that already contains the finished drawing that you will create programmatically. You download the template, open a drawing based on it, examine the drawing, and then delete its contents to provide a blank slate for recreating the drawing programmatically.

The reason for using this complicated process is that the drawing you create from the downloaded template will contain the data graphic that you eventually apply. You can create data graphics programmatically, but doing so is beyond the scope of this article.

To open a new Visio drawing based on a template

  1. On the Office Online Templates site, download the Quarterly Sales by Region template, which opens as a Visio template file (.vst) in Visio 2007.

  2. Save the template file as a Visio drawing (.vsd) file, and name it Sales Data.vsd.

  3. Examine the finished drawing to see the drawing that you will create programmatically.

  4. When you have finished, click anywhere on the drawing page, press CTRL+A to select everything on the page, and then press DELETE.

  5. In the External Data window, right-click the Regional Sales Data tab, and then click Remove.

  6. When you are prompted to confirm the deletion, click Yes, and then close the External Data window.

    You reopen the External Data window programmatically later.

  7. Press ALT+F11 to open the Visual Basic Editor.

Connecting to a Data Source Programmatically

You connect to the Excel data source programmatically by using the Microsoft Visual Basic for Applications (VBA) COM object model that is built into Visio.

Connecting to data programmatically in Visio means adding a new DataRecordset object to the DataRecordsets collection of the Visio Document object. To do so, use the Add method of the DataRecordsets collection, passing it the path to the Excel worksheet, the query you want to execute on the worksheet data, and the name you want to assign the new data recordset in the External Data window in the Visio user interface (UI).

To add a data recordset

  1. In the Visual Basic Editor, in the ThisDocument project, in the (General) section, add the following code.

    Public Sub AddDataRecordset()
    Dim strConnection As String
    Dim strCommand As String
    Dim vsoDataRecordset As Visio.DataRecordset
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                       & "User ID=Admin;" _
                       & "Data Source=C:\your path\Sales Data.xlsx;" _
                       & "Mode=Read;" _
                       & "Extended Properties=""HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;"";" _
                       & "Jet OLEDB:Engine Type=35;"
    strCommand = "Select * from [Sales by Region$]"
    Set vsoDataRecordset = ActiveDocument.DataRecordsets.Add(strConnection, strCommand, 0, "Regional Sales Data")
End Sub

  2. Substitute the folder path to the Excel data source on your computer for the expression your path in the code. If you saved your Excel workbook somewhere other than on your C drive, change the drive letter in the code accordingly.

  3. Run the AddDataRecordset procedure.

NoteNote

The connection string in this code works for Excel 2007. If you are using Excel 2003 or earlier, you must determine an appropriate connection string for your version. A good way to do that is to make a test connection to your Excel workbook in the Visio UI and record your actions in the Visio Macro Recorder. By examining the resulting macro in the Visual Basic Editor, you can view and copy the connection string that Visio uses. For more information about using the Macro Recorder, see Record a macro to automate tasks or generate code. For more information about connecting to data in the Visio UI, see Overview of data connection features in Microsoft Office Visio 2007.

Linking Data to Shapes in a Visio Drawing

The next step is to link the data to shapes in the Visio drawing. You start with a blank drawing, and then you use the DropManyLinkedU method of the Page object both to create the shapes you want to link to the data and to perform the linking.

The DropManyLinkedU method takes several parameters, including an array of shapes to create, an array of coordinates that specify where in the drawing to drop the shapes, the ID of the data recordset from which to get the data to link to the shapes, and an array of the IDs of the rows in the data recordset to link to each shape.

The DropManyLinkedU method also takes a Boolean that specifies whether to apply an existing data graphic to the new shapes. In this example, you pass False for that parameter, because you apply the data graphics separately in the next step so that what you are doing is clearer.

Finally, as an out-parameter, the DropManyLinkedU method takes an empty array of Long values that holds the IDs of the new shapes it creates when the method returns. The method itself returns a Long that indicates how many shapes were created.

  • Add the following code into your VBA project and run the LinkDataToNewShapes procedure.

    Public Sub LinkDataToNewShapes()

    Dim avarObjects(0 To 3) As Variant
    Dim adblXYs(0 To 7) As Double
    Dim alngDataRowIDs(0 To 3) As Long
    Dim alngShapeIDs() As Long
    Dim vsoDataRecordset As Visio.DataRecordset
    Dim intRecordsetCount As Integer
    Dim lngReturned As Long
    Dim intCounter As Integer
    Dim intObjectNumber As Integer

    intRecordsetCount = Visio.ActiveDocument.DataRecordsets.Count
    Set vsoDataRecordset = Visio.ActiveDocument.DataRecordsets(intRecordsetCount)

    For intObjectNumber = 0 To 3

        Set avarObjects(intObjectNumber) = Visio.Documents("Basic_U.VSS").Masters("Rectangle")

    Next

    adblXYs(0) = 4
    adblXYs(1) = 8
    adblXYs(2) = 4
    adblXYs(3) = 6
    adblXYs(4) = 4
    adblXYs(5) = 4
    adblXYs(6) = 4
    adblXYs(7) = 2

    alngDataRowIDs(0) = 1
    alngDataRowIDs(1) = 2
    alngDataRowIDs(2) = 3
    alngDataRowIDs(3) = 4

    lngReturned = ActivePage.DropManyLinkedU(avarObjects, adblXYs, vsoDataRecordset.ID, alngDataRowIDs, False, alngShapeIDs)
    Debug.Print lngReturned

    For intCounter = 0 To lngReturned - 1
        Debug.Print alngShapeIDs(intCounter)
    Next

End Sub

Before you move on to applying data graphic to the shapes, display the External Data window to view the linked data in table form.

To display the External Data window

  1. Add the following code to your VBA project.

    Public Sub DisplayExternalDataWindow()
    Application.ActiveWindow.Windows.ItemFromID(visWinIDExternalData).Visible = True
End Sub

  2. Run the DisplayExternalDataWindow procedure.

Displaying Linked Data Graphically

The following example shows how to apply an existing data graphic to the shapes in the drawing that are linked to data. The example selects all the shapes in the active window, and then applies the "Sales Data" data graphic to the selected shapes. This data graphic is included it in the template you downloaded and was created in the Visio UI.

Before running the following code, display the Data Graphics task pane in the Visio UI (on the View menu, click Task Pane, and then in the list of task panes, click Data Graphics). Then point to the data graphic to make sure its name is "Sales Data." If it is not, change the name in the code, or right-click the data graphic and click Rename to rename it.

To display linked data graphically

  1. Add the following code to your VBA project.

    Public Sub ApplyDataGraphic()
    Dim vsoSelection As Visio.Selection
    ActiveWindow.SelectAll
    Set vsoSelection = ActiveWindow.Selection
    vsoSelection.DataGraphic = ActiveDocument.Masters("Sales Data")
End Sub

  2. Run the ApplyDataGraphic procedure.

Adding a Title

You finish your drawing by adding a title. Drop a title block shape from the Borders and Titles stencil onto the page, and then change its text.

To add a title

  1. Add the following code to your VBA project.

    Public Sub InsertTitleAndShapeText()

    Dim vsoShape As Visio.Shape
    Dim vsoCharacters As Visio.Characters

    Set vsoShape = ActivePage.Drop(Application.Documents.Item("BORDER_U.VSS").Masters.ItemU("Title block classic"), 4.25, 9.875)
    Set vsoCharacters = ActivePage.Shapes.ItemFromID(vsoShape.ID + 1).Characters
    vsoCharacters.Begin = 0
    vsoCharacters.End = 27
    vsoCharacters.Text = "Quarterly Sales by Region"

End Sub

  2. Run the InsertTitleAndShapeText procedure.

Read It

Visio 2007 exposes an application programming interface (API) for connecting your drawing to data and for linking shapes in the drawing to data. With this API you can perform programmatically most of the same tasks that you can perform by using the commands on the new Data menu in the Visio UI. The following new objects and their members are included in this API:

  • DataRecordsets collection

  • DataRecordset object

  • DataConnection object

  • DataRecordsetChangedEvent object

  • DataColumns collection

  • DataColumn object

Connecting to a Data Source

Each Visio Document object has a DataRecordsets collection, which is empty until you connect to a data source. To connect a Visio document to a data source, you add a DataRecordset object to the DataRecordsets collection of the document. A DataRecordset object has a DataColumns collection of DataColumn objects, each of which is mapped to a corresponding column (field) in the data source.

Data sources that you can connect a Visio document to include Microsoft Office Excel worksheets, Microsoft Office Access databases, Microsoft SQL Server databases, Microsoft Office SharePoint lists, and other OLE DB or ODBC data sources, such as Oracle databases.

You can also connect to data by using an XML file that conforms to the ActiveX Data Objects (ADO) XML schema as the data source.

Linking Shapes to Data

After you connect your Visio drawing to an external data source, you can programmatically link the shapes in the drawing to data from that source. You can link one or more shapes to a single row of data in a data recordset, or to multiple rows of data in different data recordsets. However, you cannot link shapes to multiple rows of data in the same recordset.

You can link existing shapes to data, one shape at a time or as a group; or, you can create shapes and link them to data simultaneously, as shown in this how-to article.

Displaying Linked Data Graphically

After you link shapes to data, you can display that data graphically by adding data graphics to shapes. Visio exposes a limited API for working with data graphics, including the following objects and their members:

  • Master object of type visTypeDataGraphic

  • GraphicItems collection

  • GraphicItem object

Although you can create data graphics programmatically, the best practice is to first create the data graphics you want in the UI, and then apply them programmatically.

Refreshing Linked Data

When there are changes to the data in the data source to which your drawing is connected, you can update the data in your Visio drawing to reflect those changes. You can specify that Visio update data automatically at a specified interval, or you can update data from your code in response to a user action or to an event.

For more information about the data-connectivity API and about working with data, see the Visio 2007 Software Development Kit. Or, in Visio 2007, on the Help menu, click Developer Reference.

Explore It