Working with Data Programmatically in Visio 2007
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.
Applies to: 2007 Microsoft Office System, Microsoft Office Visio 2007, Microsoft Office Excel 2007
Saul Candib, Microsoft Corporation
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
Open Excel 2007, and in the blank worksheet, enter the data shown in Figure 1.
Figure 1. Sales data in an Excel worksheet
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.
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
On the Office Online Templates site, download the Quarterly Sales by Region template, which opens as a Visio template file (.vst) in Visio 2007.
Save the template file as a Visio drawing (.vsd) file, and name it Sales Data.vsd.
Examine the finished drawing to see the drawing that you will create programmatically.
When you have finished, click anywhere on the drawing page, press CTRL+A to select everything on the page, and then press DELETE.
In the External Data window, right-click the Regional Sales Data tab, and then click Remove.
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.
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
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
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.
Run the AddDataRecordset procedure.
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.
To link data to shapes
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
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
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
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
Run the InsertTitleAndShapeText procedure.
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:
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
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.