Graphically Displaying Data from a SharePoint List in Visio 2007

  • Article

Summary: Learn how to graphically display data from a Microsoft SharePoint list in a Microsoft Office Visio 2007 drawing, using the Microsoft Visual Basic for Applications (VBA) object model. The included sample solution is based on VBA. (6 printed pages)

Saul Candib, Microsoft Corporation

Applies to: Microsoft Office Visio 2007, Windows SharePoint Services 3.0, Microsoft Office SharePoint Server 2007

Contents

  • Overview

  • Obtaining the URL and ID of a SharePoint List

  • Displaying Data from a SharePoint List in Visio

  • Adding a Title to the Drawing

  • Conclusion

  • Additional Resources

Overview

Microsoft Office Visio 2007 enables you to display data contained in a spreadsheet, a database, or a Microsoft SharePoint list in a graphical format. Displaying data graphically can make it easier to understand. Visio provides a data-connection wizard, called the Data Selector, in the user interface (UI) that enables you to connect to and display data graphically in a single document. It is also possible to perform the same task programmatically, by using Automation. Automating a task makes it easier to repeat the task and to customize the results. Like other Microsoft Office applications, Visio provides a built-in code-editing environment, the Visual Basic Editor (VBE), which helps you create automated solutions for Visio without leaving the program. This article describes how to use Microsoft Visual Basic for Applications (VBA) code in the VBE window to graphically display data from a Microsoft SharePoint list in a Visio drawing.

Obtaining the URL and ID of a SharePoint List

Before you can connect to the data in a SharePoint list, you must obtain the identification number (ID) of the list, which in turn you pass to Visio in the GetDataFromSharePointList procedure shown later in this article. The ID of a SharePoint list is in the form of a globally unique identifier (GUID), which is a hexadecimal number, 32 characters in length. Hexadecimal characters can be occupied by numerals or by the letters A through F. For a SharePoint list, the 32 characters are divided into five groups, composed of eight, four, four, four, and twelve characters, respectively, and separated by dashes. The entire GUID is surrounded by braces. To determine the ID of the list (assuming that you do not already know it), use the following procedure.

To obtain the URL and ID of a SharePoint list

  1. In your browser, navigate to the SharePoint page that contains the list.

  2. Click Settings, and then click List Settings.

  3. Copy the GUID (everything that follows the equals sign) from the address (URL) box at the top of the page, and paste it into a place where you can easily edit its text (a text file, for example).

  4. Replace %7B at the beginning of the GUID with a left brace ({).

  5. Replace %2D at four places within the GUID with a hyphen (-).

  6. Replace %7D at the end of the GUID with a right brace (}).

    The resulting GUID should be identical to the following one in form (the specific numerals and characters in your GUID will be different): {B42464B6-9758-40E4-BBB2-89B42003E3A8}.

  7. Copy the entire URL of the list page from the address box onto a separate line in the same text file.

  8. Edit the URL by deleting everything below the site level, leaving the rest of the URL intact.

    For example, the original URL might look like this:

    http://office/personal/davidahs/_layouts/listedit.aspx?List=%7BB42464B6%2D9758%2D40E4%2DBBB2%2D89B42003E3A8%7D

    Edit it to look like the following:

    http://office/personal/davidahs/

  9. Save the text file that contains your edited GUID and URL to a location, such as the Desktop, from which you can retrieve it.

Displaying Data from a SharePoint List in Visio

The GetDataFromSharePointList procedure, shown in the following code example, connects to a SharePoint list and imports the data from that list into Visio and displays it in a table in the External Data Window. It then creates a new Visio shape to represent each row of data in the table and displays the data associated with each shape in a data-graphic shape.

Figure 1 shows the SharePoint list that contains the data imported into Visio.

Figure 1. The SharePoint list that contains the data to import into Visio

SharePoint list with data to display

Figure 2 shows the Visio diagram that displays the data contained in the SharePoint list.

Figure 2. A Visio diagram that displays the data from the SharePoint list in Figure 1

Visio drawing displaying data

To get started, declare a Visio DataRecordSet object, which represents the complete set of data to display in Visio. Also, declare an integer to represent the ID of the data recordset in Visio. The ID is required to link shapes on the page to data in the recordset. Use the VBA keyword Dim to declare both the object and the integer. Next, use the Add method of the DataRecordsets collection to add a new data recordset to the collection, and use the Set keyword to assign the object you just created to that new data recordset.

The Add method takes four parameters:

  • ConnectionIDOrString, which in this case is a string that consists of three parts concatenated together: the provider ("WSS," or Windows SharePoint Services), the URL of the SharePoint site that contains the list page (http://office/personal/davidahs), and the list ID, or GUID, as explained earlier in this article.

  • CommandString, "Select * from [Current Tasks];", which is a structured query language (SQL) command that instructs Visio to select all the data in the list named "Current Tasks".

  • AddOptions, which represents the options to apply. In this case, there are no options to apply, so pass 0.

  • Name, a string that represents the name to assign to the data recordset to identify it in the External Data Window in Visio. Pass the value "Current Tasks".

Now, get the ID property value of the DataRecordset object, and assign it to the integer variable declared earlier. You will pass that data recordset ID as the first parameter of the LinkToData method. Then, set the Visible property of the Window object that represents the External Data Window to True, so that it appears in the Visio application window and displays the list data connected to.

Next, repeat the same procedure four times, once for each row of data, with only a minor variation to verify that the shapes created do not overlap. Declare a Shape object, and then drop a Triangle shape on the Visio drawing page at a unique location (the choice of a triangle is arbitrary—you can choose any shape). Assign the Shape object to the new shape, and then use the LinkToData method of the Shape object to link the shape to the data in the row of the data recordset that is specified in the second parameter. Passing True for the third parameter of LinkToData instructs Visio to display the data in a data-graphic shape associated with the triangle shape. The data-graphic shape applied is, by default, the one most recently used in Visio. The particular data graphic used in this article has been customized in the Visio user interface. The graphic displays the information in the task list so that the name and owner of the task are displayed in text and the status of the task is displayed by the color coding of the triangle shape, as shown in the following figure. For more information about customizing data graphics, see Visio Help.

Figure 3 shows the detail of a shape and its data graphic.

Figure 3. Detail of a shape and its data graphic

Detail of data graphic shape

The following VBA code example connects to data in a SharePoint list, imports that data into Visio, and then creates shapes on the Visio page to display the data graphically.

Sub GetDataFromSharePointList()

    Dim vsoDataRecordset As Visio.DataRecordset
    Dim intRecordsetID As Integer

    Set vsoDataRecordset = Application.ActiveDocument.DataRecordsets.Add( _ 
            "PROVIDER=WSS;DATABASE=http://office/personal/davidahs;" _ 
            & "LIST={B42464B6-9758-40E4-BBB2-89B42003E3A8};", _
            "Select * from [Current Tasks];", 0, "Current Tasks")
    intRecordsetID = vsoDataRecordset.ID
    
    Application.ActiveWindow.Windows.ItemFromID(visWinIDExternalData).Visible = True

    Application.ActiveWindow.Activate
    Dim vsoShape1 As Visio.Shape
    Set vsoShape1 = Application.ActiveWindow.Page.Drop(Application.Documents.Item _
         ("BASIC_U.VSS").Masters.ItemU("Triangle"), 2, 3)
    vsoShape1.LinkToData intRecordsetID, 1, True

    Application.ActiveWindow.Activate
    Dim vsoShape2 As Visio.Shape
    Set vsoShape2 = Application.ActiveWindow.Page.Drop(Application.Documents.Item _
         ("BASIC_U.VSS").Masters.ItemU("Triangle"), 2, 8)
    vsoShape2.LinkToData intRecordsetID, 2, True

    Application.ActiveWindow.Activate
    Dim vsoShape3 As Visio.Shape
    Set vsoShape3 = Application.ActiveWindow.Page.Drop(Application.Documents.Item _
         ("BASIC_U.VSS").Masters.ItemU("Triangle"), 6, 3)
    vsoShape3.LinkToData intRecordsetID, 3, True

    Application.ActiveWindow.Activate
    Dim vsoShape4 As Visio.Shape
    Set vsoShape4 = Application.ActiveWindow.Page.Drop(Application.Documents.Item _
         ("BASIC_U.VSS").Masters.ItemU("Triangle"), 6, 8)
    vsoShape4.LinkToData intRecordsetID, 4, True

End Sub

If the data in the SharePoint list changes, you can use the Refresh method of the DataRecordset object to update the data displayed in the drawing. For more information about working with data programmatically in Visio, see About Connecting to Data in Visio.

Adding a Title to the Drawing

The following VBA code example adds a title shape to the Visio drawing, adds text to the title shape, and then formats the text.

Sub AddTitle()

    ' Create the title shape.
    Dim vsoShape As Visio.Shape
    Set vsoShape = Application.ActiveWindow.Page.DrawRectangle _
             (1.75, 10.5, 6.75, 10#)
    vsoShape.TextStyle = "Basic"
    vsoShape.LineStyle = "Text Only"
    vsoShape.FillStyle = "Text Only"

    ' Create the title-shape text.
    Dim vsoCharacters As Visio.Characters
    Set vsoCharacters = vsoShape.Characters
    vsoCharacters.Text = "Current Task Status"
    
    ' Set the title-shape text font size.
    vsoCharacters.CharProps(visCharacterSize) = 30#
    
    ' Make the title-shape text bold.
    vsoCharacters.CharProps(visCharacterStyle) = 17#

    ' Save the file.
    Application.ActiveDocument.Save

End Sub

To run the code examples as Visio macros

  1. In Visio 2007, press ALT+F11 to start the Visual Basic Editor.

  2. In the Project Explorer, double-click the ThisDocument object under Visio Objects.

  3. Copy and paste the preceding code examples (as modified to include the appropriate URL and list ID for your list) into the Code window on the right.

  4. Click anywhere in the code of the first sample.

  5. Press F5 to run the first sample.

  6. Click anywhere in the code of the second sample, and then press F5 to run the second sample.

  7. Close the Visual Basic Editor.

Conclusion

You can use VBA code to programmatically connect a Visio diagram to data in a SharePoint list, create Visio shapes that represent that data, and then display the data graphically in those shapes. Displaying data graphically can make it easier to understand.

Additional Resources

For more information about products and technologies mentioned in this article, see the following resources: