Linking Excel Named Ranges in Visio

I have been using worksheets and ranges in Excel with Visio’s Link Data to Shapes (in the Pro and Premium editions) for several years, but recently encountered a problem with the recently added structured references in Excel. Fortunately, with the help of my colleague John Goldsmith, we found a solution.

Normally, I create tables in Excel worksheets with the column headings in the first row, so I could use the worksheet name in the Link Data to Shapes function in Visio. However, I wanted to have an extra row above the table headers for a recent project, so I wanted to use named ranges, defined with any rows above the table headers omitted. When I went to connect these ranges using the Data Selector in Visio, I found that all of my named ranges, except one (StepIds), were missing from the list of worksheets and ranges:

image

So, we explored the ranges using the Name Manager in Excel, and noticed that all of the missing ranges were using the structured reference notation:

image

The solution is un-tick the Use table names in formulas option on the Formulas tab in the Working with formulas group:

image

This meant that I was able to define and name ranges without the structured references:

image

Now, these newly created named ranges do become selectable with the Data Selector in Visio, and I can thus create my linked External Data recordsets:

image

You can read about structured references at http://office.microsoft.com/en-us/excel-help/using-structured-references-with-excel-tables-HA010342999.aspx

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: