• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

bVisual

  • Home
  • Services
    • How Visio smartness can help your business
    • Visio visual in Power BI
    • Visio Consulting Services
    • Visio Bureau Services
    • Visio Training and Support Services
  • Products
    • Visio Shape Report Converter
    • SS Plus
    • LayerManager
    • visViewer
    • Metro Icons
    • Rules Tools for Visio
    • The Visio 2010 Sessions App
    • Multi-Language Text for Visio
    • Document Imager for Visio
    • multiSelect for Visio
    • pdSelect for Visio
  • Case Studies
    • Case studies overview
    • Visualizing Construction Project Schedules
    • Visio Online Business Process Mapping
    • Nexans Visio Template
    • CNEE Projects, WorldCom
    • Chase Manhattan Bank
  • News
    • Recent news
    • News archive
  • Resources
    • Articles➡
      • ShapeSheet Functions A-Z
      • Comparing Visio for the Web and Desktop
      • Customising Visio Shapes for the Web App
      • Key differences between the Visio desktop and web apps
      • Using the Visio Data Visualizer in Excel
      • Using Visio in Teams
      • Creating Visio Tabs and Apps for Teams with SharePoint Framework (SPFx)
      • Designing Power Automate Flows with Microsoft Visio
      • Innovative uses of Visio Lists
    • Webcasts ➡
      • Visio in Organizations
      • My session and other Visio sessions at MSIgnite 2019
      • Power up your Visio diagrams
      • Vision up your Visio diagrams
      • The Visio 2010 MVP Sessions
    • Visio Web Learning Resources
    • Books➡
      • Mastering Data Visualization with Microsoft Visio
      • Microsoft Visio Business Process Diagramming and Validation
      • Visualizing Information with Microsoft Visio
  • Blog
    • Browse blog articles
    • Visio Power BI articles
    • Visio for Web articles
    • A history of messaging and encryption
  • About us
    • About bVisual
    • Testimonials
    • Bio of David Parker
    • Contact Us
    • Website Privacy Policy
    • Website terms and conditions
    • Ariba Network
You are here: Home / Visio / Visio 2010 / Updating the file path of data linked Excel tables in Visio

Published on March 13, 2015 by David Parker

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.

image

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

image

I selected the workbook from my local hard drive:

image

Then I selected the worksheet to link:

image

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

image

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

image

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

image

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.

image

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:

image

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.

image

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:

image

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

image

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.

image

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!

Related

Filed Under: Excel, External Data, VBA, Visio 2010, Visio 2013, Visio Services Tagged With: Excel, Link Data to Shapes, Visio

About David Parker

David Parker has 25 years' experience of providing data visualization solutions to companies around the globe. He is a Microsoft MVP and Visio expert.

Reader Interactions

Comments

  1. Iulyan says

    December 7, 2020 at 4:29 pm

    the link doesn’t work. Really interested to see it.

    Reply
    • David Parker says

      December 7, 2020 at 4:47 pm

      OK, I have updated the link … Microsoft changed the URLs for OneDrive 🙁

      Reply
  2. Ryan says

    November 12, 2022 at 5:40 am

    Hi, I’m new to Visio VBA. Am keen to use this code on a project I’m working on but am unclear what line in the code to change with the new XL file location\name?

    Hope you can please assist

    Reply
    • David Parker says

      January 3, 2023 at 9:14 am

      If you click Configure Refresh… from the right mouse menu of the External Data window, then you can click Change Data Source from the dialog that pops up.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

  • LinkedIn
  • Twitter

Recent Posts

  • Update to LayerManager add-in for non-English users
  • Pushing Data Visualizer in Visio beyond its limits
  • Pushing Data Visualizer in Visio to the limits!
  • Teams Tuesday Podcast Recording about Visio
  • Linking Data to Visio Shapes in Code

Categories

Tags

Accessibility Add-Ins Connectors Containers Data Export Data Graphics Data Import Data Visualizer Educational Excel GraphDatabase Hyperlinks Icon Sets JavaScript Layers Legend Link Data to Shapes Lists MSIgnite MVP Office365 Org Chart PowerApps PowerBI PowerQuery Processes Shape Data Shape Design ShapeSheet ShapeSheet Functions SharePoint 2013 SQL Teams Themes Validation VBA Video Visio Visio 2007 Visio 2013 Visio for the Web Visio Online Visio Services Visio Viewer Webinar

Footer

bVisual Profile

The UK-based independent Visio consultancy with a worldwide reach. We have over 25 years experience of providing data visualization solutions to companies around the globe.

Learn more about bVisual

  • LinkedIn
  • Twitter

Search this website

Recent posts

  • Update to LayerManager add-in for non-English users
  • Pushing Data Visualizer in Visio beyond its limits
  • Pushing Data Visualizer in Visio to the limits!
  • Teams Tuesday Podcast Recording about Visio
  • Linking Data to Visio Shapes in Code

Copyright © 2023 · Executive Pro on Genesis Framework · WordPress · Log in