Updating the file path of data linked Excel tables in Visio

Link Data to Shapes in Visio (not the Standard edition though) is great for visualizing information, and I often use a multi-worksheet Excel workbook that I create locally, and then wish to re-link to a copy of the workbook elsewhere, or even in Office365. This is a simple task if there are only one or two Excel tables involved, but can become tedious when there are many. So, this blog is presents an semi-automated method of achieving this.

Firstly I have an Excel workbook that I have many worksheets with tables of data on them. I named each worksheet, and used the Format As Table command on each.

Then I used the DATA \ Link Data to Shapes … function in Visio to link to several of these worksheets as External Data recordsets.

I selected the workbook from my local hard drive:

Then I selected the worksheet to link:

Each of my worksheet tables have a unique identifier column, which the Data Selector recognised.

This created my first External Data recordset, from which I dragged and dropped a single row onto the Visio page to test.

I flipped over into VBA (ALT+F11) to interrogate the relevant data recordset properties using the Immediate Window:

This revealed that a Select statement is created

?Visio.ActiveDocument.DataRecordsets(1).CommandString
select * from `Personnel$`

?Visio.ActiveDocument.DataRecordsets(1).DataConnection.FileName
C:\Users\David\Documents\Visio\Data\BeSmart Assets.xlsx

?Visio.ActiveDocument.DataRecordsets(1).DataConnection.ConnectionString
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\David\Documents\Visio\Data\BeSmart Assets.xlsx;Mode=Read;Extended Properties=”HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;”;Jet OLEDB:System database=””;Jet OLEDB:Registry Path=””;Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=””;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don’t Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

I then repeated the actions for several other worksheets in the same workbook.

In fact, if I skipped over the part of the Data Selector wizard where I could amend the list of columns to include and filter the rows to return:

This could have created a CommandString like this:

?Visio.ActiveDocument.DataRecordsets(1).CommandString
select `ID`,`Name`,`Title`,`Department`,`Cost Centre`,`Cost Centre Name`,`Grade`,`Telephone`,`E-Mail`,`Manager`,`Manager Name`,`Workspace`,`Mobile 1 Number 1`,`Call`,`Mail` from `Personnel$`  WHERE `Department` = ‘Marketing’

However, you cannot use the column selections and row filters with Excel workbooks saved to Excel Services, so I am ignoring this.

So, I then wrote a short macro to list all of the DataRecordsets in the active Visio document:

Public Sub LinkAllRecordsets()
Dim drs As DataRecordset
Dim dcn As DataConnection
Dim doc As Document
    Set doc = Visio.ActiveDocument
    For Each drs In doc.DataRecordsets
        Set dcn = drs.DataConnection
        Debug.Print drs.ID, drs.Name, dcn.FileName
    Next drs
End Sub

This simply lists the names and file names:

1            Personnel     C:\Users\David\Documents\Visio\Data\BeSmart Assets.xlsx
2            Buildings     C:\Users\David\Documents\Visio\Data\BeSmart Assets.xlsx
3            Floors        C:\Users\David\Documents\Visio\Data\BeSmart Assets.xlsx
4            Cost Centres  C:\Users\David\Documents\Visio\Data\BeSmart Assets.xlsx

I have a copy of the Excel workbook in an Office365 document library.

So, I used the Configure Refresh action in Visio to change the source of one of the DataRecordsets to this copy of the Excel workbook:

Visio does warn me that this action may break links to the data, but I know that it is an exact copy.

Then, when I run my macro again, I can see that the Personnel DataRecordset is indeed linked to to the copy in the document library of Office365:

1            Personnel     https://bvisualnet.sharepoint.com/Visio Service Training/BeSmart Ltd/BeSmart Assets.xlsx
2            Buildings     C:\Users\David\Documents\Visio\Data\BeSmart Assets.xlsx
3            Floors        C:\Users\David\Documents\Visio\Data\BeSmart Assets.xlsx
4            Cost Centres  C:\Users\David\Documents\Visio\Data\BeSmart Assets.xlsx

I further investigated the CommandString and DataConnection.ConnectionString for the recordset that I had changed, and found that there were just a few changes, as highlighted in yellow:

?Visio.ActiveDocument.DataRecordsets(1).CommandString
SheetName=Personnel; RangeName=A1:AI52;

?Visio.ActiveDocument.DataRecordsets(1).DataConnection.ConnectionString
DataModule=Microsoft.Office.Visio.Server.EcsDataHandler,Microsoft.Office.Visio.Server; Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=https://bvisualnet.sharepoint.com/Visio Service Training/BeSmart Ltd/BeSmart Assets.xlsx;Mode=Read;Extended Properties=”HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;”;Jet OLEDB:System database=””;Jet OLEDB:Registry Path=””;Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=””;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don’t Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

I was a little surprised to see that the RangeName included the full address of the range, but then automatically refreshable Visio documents displayed in the Visio Web Access control in SharePoint pages does not support shape additions or deletions.

I wrote a short macro in Excel to list the worksheets in the active workbook, along with the UsedRange address of each one.

Public Sub ListWorksheets()
Dim wks As Excel.Worksheet
Dim wkb As Excel.Workbook
    Set wkb = Excel.ActiveWorkbook
    For Each wks In wkb.Sheets
        Debug.Print wks.Index, wks.Name, wks.UsedRange.Address
    Next
End Sub

This displayed the following list:

1            Buildings     $A$1:$F$2
2            Floors        $A$1:$G$3
3            Spaces        $A$1:$I$60
4            Personnel     $A$1:$AI$52
5            Cost Centres  $A$1:$D$18
6            Computers     $A$1:$H$10
7            Mobiles       $A$1:$K$8
8            SIM Cards     $A$1:$E$4
9            Monitors      $A$1:$E$5
10           Shapes        $A$1:$A$6
11           Notes         $A$1:$B$28

After a little investigation, along with my friend John Goldsmith ( see http://visualsignals.typepad.co.uk/vislog/ ), it became quite clear that the Data Selector in Visio was only displaying the worksheets where there was some values entered, and references the used range.

Therefore, any code that automates the transition of the DataRecordsets source from a local Excel file to one stored on SharePoint needs to observe the limitations of Excel Services.

I have also made some assumptions in the writing of this code:

1. I will manually change the data source of the active recordset in the External Data window
2. The target Excel workbook is an exact copy of the current workbook
3. Each table starts in the first cell “A1” on each sheet
4. There are no more than 26 columns in each table

So, with those caveats, I can now change the data source of the active recordset, then run my macro, LinkExcelRecordsets, and then all of the other recordsets will be linked to the same target Excel workbook.

This is useful for moving Visio documents and linked Excel workbooks around together, or even sending to other users.

The macro is in the Date Utils.vssm file which can be downloaded and placed into the My Shapes folder, where it is available for opening when required.

You can download the stencil that contains the macro from Data Utils.vssm

The macro just changes the DataConnection.ConnectionString and the CommandString of each other Excel linked DataRecordset in the document.

Please do not blame me if something goes wrong as there is only basic error handling in the code … and always backup your important documents first!