Visio users are excited about the data features in Visio 2007.

But as they dig deeper, more and more of them are asking this question:

Can Visio provide an automated way to refresh the attached data every time the diagram is opened, so I don’t have to remember to refresh the data myself?

I want to see the very latest version of the data every time I open the diagram.

Today’s article shows how to accomplish this with just a wee bit of Visio automation code. The article details step-by-step instructions on how to implement this feature. Users are welcome to steal adaptively re-use this code to suit their needs and provide this functionality in their own Visio diagrams.

This is another great guest-post created, written by David Edson. David is a fellow Microsoft Visio Most Valuable Professional (MVP), a one-time co-worker of mine at Visio Corp., and is currently the CTE/Director of Consulting Services at visibility.biz. This article will also be published on EasierWithVisio.

The Wish List Item

With the introduction of Data Connectivity under Visio 2007 Professional Edition, more and more Visio users are beginning to create data-aware / data-connected Visio Diagrammes. From everything from with Network Asset Management Diagrams to Organizational Structure Diagrammes, these data-connected diagrams are becoming mission-critical visualizations of the up-to-the-minute status of the health and profitability of many corporate enterprises.

As the popularity of these data-connected diagrammes increases. So does the request to have these diagrams refresh their view of the data each and every time the Visio diagramme is opened. I’m constantly asked if this is possible without the Visio user remembering to refresh the data manually by selecting Data 4Refresh Data… from the Visio Data menu. Fortunately, the answer is a resounding “YES!!!”, by using just a wee bit of Automation code.

Now… don’t turn running, thinking, “but I don’t know how to code…” The following will walk you through step by step and show you that it’s actually very easy to implement this feature in Visio 2007 Professional edition.

The Solution

The Visio Document Object already has many Events that it is signed up for via the Visio Object Model. Among these events, the “On Document Open” event is obviously included. Each and every time any Visio diagram is opened, internally, Visio checks to see if any automation code is associated with the Open event and, if there is, Visio cheerfully executes that code. What we are going to do is create a few lines of automation code to tell Visio to do the following when the document is opened:

1. We’re going to check to see if the document actually has any data attached to the document.
2. If, and only if, the document has data attached to it, we will obtain a reference to the datasets attached to the diagram and refresh those datasets.
3. We’ll then update those datasets and post a wee message to the user of the document letting them know that we have successfully updated the data.
4. If there are no data records attached we will politely take no action except for informing the user that there were no data records to update
5. Either way we will exit the automation quietly

The Code

Although the automation code that I am going to share with you could be authored in any Microsoft-supported language including Visual Basic for Applications (VBA), Visual Basic 6.0 (VB6), Visual Basic .NET (VB.NET), C++, or C#.NET, I’m going to provide it using VBA, since every user already has VBA in their individual PC, as it is already installed with every copy of Office and Visio.

Step 1 – Opening the Visual Basic for Applications Integrated Development Environment (VBA IDE).

In order to author the code we need to open up the Code Authoring environment behind Visio; VBA. You can either do this via menu selections or keyboard shortcuts:

1. Select Tools > Macro > Visual Basic Editor (Alt+T 4 M 4 V)
2. Press the Alt+F11 keys at the same time

You will be presented with the VBA IDE. You will see, by default a Project Explorer in the Upper-Left-Hand and a Properties Window in the Lower-Left-Hand of the IDE. You will most probably have an empty area at the Right 3/5 of the IDE.

click to view larger image

Step 2 – Open the Document Code Window

Next, Double-Click on the Blue Entry in the Project Explorer that is entitled : “ThisDocument (Drawing1). A new Code Window will open up in the IDE to enable you to author code for your VBA project. There are two drop-down lists at the top of the Code Authoring Window. The drop-down list on the left has an entry in it that is labeled (General). The drop-down list on the right has an entry in it that is labeled (Declarations). Select the drop-down list on the left and choose the entry listed as “Document”.

Note that when you select the Document Object, on the left, the right drop-down automatically is filled with the default event for the Document Object, the “DocumentOpened” event. Additionally VBA created the Sub-Routine Framework for the Document Opened Event. This should look similar to the illustration below:

click to view larger image

Looking at the code window, your cursor should be blinking in the empty line between the line beginning with the word Private, and the line beginning with the word End, This is where we are going to begin writing the automation code. Each and every time the document is opened, any instructions we place in this code subroutine will be executed.

Step 3 – Declare the Object Variables

Using the keyboard, enter the following lines of code:

Dim vsoDataRecordset As Visio.DataRecordsetDim intCount As IntegerDim szMessageText As StringDim szBoxTitle As String

These variables are the storage holders for the data and objects that we will use and manipulate to perform the actions within our code.

The Object variable vsoDataRecordset is the Object representing all of the data records that are attached to the document. The integer variable intCount is the integer data type object representing the count of the number of data recordsets we have attached to the document. The string variable szMessageText is the string data type object representing the message we want the user to see when we execute the subroutine. The string variable szBoxTitle is the string data type object representing the Title to be placed in the VBA Message Box that will hold the Message Text we will provide.

Step 4 – Write the Working Executable Code

Using the keyboard, continue by entering the remaining following lines of code:

intCount = ThisDocument.DataRecordsets.CountszBoxTitle = "Data Refresh Result"

If intCount > 0 Then Set vsoDataRecordset = ThisDocument.DataRecordsets(intCount) vsoDataRecordset.Refresh szMessageText = "All Data Records have been successfully refreshed."Else  szMessageText = "I've no Data Attached."End If

MsgBox szMessageText, vbOKOnly + vbInformation, szBoxTitle

The intCount variable holds the value for the total count of the data recordsets that are associated with this document.

The string variable szBoxTitle holds the string value “Data Refresh Result” that will be displayed as the title to the Message Box we will provide as feedback to the user.

The Object Variable vsoDataRecordset holds a reference to the DataRecordsets Object that includes all of the data records that are currently attached to the current document.

The Refresh Method of the DataRecordsets Object does the actual refreshing of all of the attached records.

The string variable szMessageText holds either the string value “All Data Records have been successfully refreshed” or the string value “I’ve no Data Attached.” Depending upon if the count of the recordsets is greater than 0 or not. All of this is structured in the classic If, Then, Else, End If structure.

Finally, a call is made to the MsgBox function, passing it the szMessageText and the szBoxTitle variables as well as telling the function that it should display an Information Icon and provide an OK only Command Button.

When written, the entire subroutine in the On Open Event for the Document Object should look like the following:

click to view larger image

Step 5 – Run the Code against the current diagram with no data attached

Close the VBA IDE by selecting File 4 Close and Return to Visio from the VBA File menu.

From within the Visio diagramming environment, select File 4 Save As… from the Visio File Menu. Save your Visio diagram under any convenient folder and filename; I’ll save mine on my desktop as RefreshOnOpen.vsd.

Next Close the diagram by selecting File 4 Close from the Visio File menu.

Next, reopen the file that you just closed by selecting File from the Visio File Menu and selecting the file from the Most Recently Used File Listing below the File Menu; in my case I’ll re-open the RefreshOnOpen.vsd file.

When the File opens, note that immediately, a Message Box is displayed stating that I do not have any data records attached to this diagram, just as expected.

click to view larger image

Step 6 – Run the Code against the current diagram with data attached

So in order to prove that the code provides the functionality we desire, we first need to attach data to the diagram. You will, of course, be using your own data source and your own records, and your own SmartShape symbols, however I will attach some data to my diagram. Remember that this is a two-step process: first attach the data to the diagram, and then attach the records to the SmartShape symbols. Mine looks like the illustration below:

click to view larger image

Next, Save and Exit the Visio Diagram.

Re-open the diagram as described above. Note that this time the data records are validated and updated and the message box is displayed with the proper updating message as shown below:

click to view larger image

The Conclusion

The key to this functionality is that every Visio diagramme has an event called DocumentOpened that can easily be accessed via automation. Because absolutely every aspect of the Visio environment can be accessed via the Object Model, the DataRecordsets Object can call the Refresh Method and therefore the data records may be refreshed each and every time the diagramme is opened. The If statement ensures that if no data is attached, we will not get automation errors and will be properly notified of the success or failure of the refresh based on the presence of attached data.

There you have it. Not so hard after all, right? Please “Adaptively Reuse” this code, and increase your productivity with Visio.

Thanks, again David!

Download “Data Refresh on Open” s!Aj0wJuswNyXlhnZLniX7CJMw-gtu – Downloaded 3633 times – 103.00 B