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:
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:
The solution is un-tick the Use table names in formulas option on the Formulas tab in the Working with formulas group:
This meant that I was able to define and name ranges without the structured references:
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:
You can read about structured references at http://office.microsoft.com/en-us/excel-help/using-structured-references-with-excel-tables-HA010342999.aspx