Linking Excel Ranges to Visio Shapes

I have often linked Excel worksheets and ranges to Visio over the years. I used the Database Wizard initially, but have been using the Link Data to Shapes feature since Visio 2007. I had noticed that I sometimes didn’t get a full list of the available ranges when trying to link in Visio, so I decided to investigate why.

Firstly, I created a sample Excel 2010 worksheet with a header row, and three rows of data in Sheet1.

SNAGHTML1c9f99

So, I then used the Link Data to Shapes feature in Visio 2010 and browsed to the workbook to attempt a link…

image

Sure enough, the first sheet was listed as available to link to in the Data Selector dialog:

image

The column names and sample values were displayed, as expected:

image

I was content that this would work, so I cancelled the dialog, and returned to Excel, where I used the Format as Table button on the table that I had just .

image

A quick gander at the Name Manager dialog shows that this feature has created a named range called Table1:

image

If I then create a named range that covers the same cells as this table, then the default behaviour of Excel 2010 is to create a reference to the underlying table:

image

So, I created a range named Names by selecting the cells in Excel, and I then ended up with a table and a named range that refers to that table:

image

Unfortunately, neither of these ranges appear in Visio when I attempt to link to them using the Data Selector. The answer is to either edit the Refers To for Names to use the cell references rather than the table reference:

image

Alternatively, you can untick the Use table names in formulas Excel option before creating the named range. This will have the same effect as editing the Refers To manually:

image

Now that the Names range is not using the new Table feature in Excel 2010, it becomes visible in the Data Connector dialog in Visio:

image

Consequently, I could link the named range as an External Data Recordset in Visio:

image

Let’s hope that this bug is fixed in the next version of Visio…

Finally, here are some useful articles about named ranges and tables in Excel:

http://blogs.office.com/b/microsoft-excel/archive/2010/01/25/table-improvements-in-excel-2010.aspx

http://office.microsoft.com/en-us/excel-help/define-and-use-names-in-formulas-HA010147120.aspx

http://office.microsoft.com/en-us/excel-help/using-structured-references-with-excel-tables-HA010155686.aspx

http://www.excel-2010.com/tables-in-excel-2010/

3 Responses to “Linking Excel Ranges to Visio Shapes”

  1. Scott Helmers Says:

    Nice detective work, David!

  2. Chris Roth Says:

    Just to clarify: If you create a named range *first*, then it works, but if you name a formatted-as-table range, then it doesn’t show up properly without your fix. At least that’s what I am seeing.

  3. davidjpp Says:

    Good point Chris … I love the the Format as Table option, and so I invariably use that first … thus creating the Table references.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

davecra.wordpress.com/

Microsoft Office Development, and more...

PowerShell.Amsterdam

Automate, Accelerate, Accurate

johnvisiomvp

Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

Nilsandrey's Weblog

Just another WordPress.com weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Shapes, Stencils, Drawings Templates, Tutorials, Tips & Developer Info for Microsoft Visio

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: