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.
So, I then used the Link Data to Shapes feature in Visio 2010 and browsed to the workbook to attempt a link…
Sure enough, the first sheet was listed as available to link to in the Data Selector dialog:
The column names and sample values were displayed, as expected:
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 .
A quick gander at the Name Manager dialog shows that this feature has created a named range called Table1:
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:
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:
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:
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:
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:
Consequently, I could link the named range as an External Data Recordset in Visio:
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
Nice detective work, David!
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.
Good point Chris … I love the the Format as Table option, and so I invariably use that first … thus creating the Table references.