Warning:
Important
The information in this section applies only to the Professional and Premium editions of Visio 2010.
You learned how to add and edit shape data fields. It’s convenient to be
able to do so manually, but it’s also easy to imagine situations in
which you would like to populate your drawings with data from a
spreadsheet or database or other external source.
Prior to Visio 2007, it was
possible to link Visio shapes to external data, but it was rather
cryptic, somewhat confusing, and the procedures often required
programming or at least some technical knowledge.
Visio 2007 Professional changed that, and both the Professional and Premium editions of Visio 2010 include the same data linking facility. In general, there are two steps involved in data linking:
Link the drawing to a data source. Link the data to individual shapes.
In this exercise, you will link the process steps in the Human Resources Process Map drawing to data in an Excel spreadsheet.
In the Excel workbook, examine the data it contains. In particular, notice the column headings.
Data Graphics
In Visio, on the Data tab, in the Display Data group, click the Data Graphics button, and then clear the Apply after Linking Data to Shapes check box. Visio will not apply a data graphic to shapes after you link them to data later in this exercise.
Tip:
Although you are turning data
graphic activation off for this exercise, in many situations, it is
valuable to turn it on so you can immediately see the results of your
data linking operation.
Link Data to Shapes
In Visio, on the Data tab, in the External Data group, click the Link Data to Shapes button. The first page of the Data Selector wizard appears.
Tip:
The default data type on the
first page of the Data Selector wizard is Microsoft Excel Workbook,
which is the one you will use for this exercise. However, notice that
you can link to data stored in Microsoft Access, Microsoft SharePoint,,
Microsoft SQL Server, or almost any other database.
On the next Data Selector wizard page that appears, click the Browse button, and in the resulting file open dialog box, navigate to the HR Process Data Excel workbook. After selecting the correct file, click the Open button. The file name you selected appears in the What Workbook Do You Want To Import box.
Click Next. On
this page of the wizard, you will choose which worksheet contains the
data to which you want to link. The Data Selector wizard usually
defaults to the first worksheet in the workbook (Sheet1$), which is
correct for this exercise.
Select the First row of data contains column headings check box to indicate that the worksheet data includes column headings.
Tip:
It is very helpful if
your Excel workbook contains column headings, and if they match exactly
to the names of the shape data fields in Visio. If the names match,
Visio will know exactly how to map the data from the data source to the
shapes.
Click Next to accept the defaults for both settings on this page of the wizard. On
the Connect To Data page of the wizard you can customize the columns
and rows from the selected worksheet that will be linked. As you can see
in the following graphic, the default is all columns and all rows,
which works for this exercise.
Click Next. On
this page, Visio recommends a field that appears to uniquely identify
each row of data based on its analysis of your data. You can change to a
different field if there is a better choice.
Tip:
You can select more than one field to constitute the unique ID if a single field is not sufficient.
The guesses Visio makes for
the unique ID are generally pretty good. However, you should always
think about the recommendation to determine whether there is a different
field or combination of fields that is a better choice.
If your data does not contain a
unique value for each row, you can click the option at the bottom of
this page of the wizard to signify this, allowing Visio to use the
sequence of the rows to identify them. Although this choice will work
fine for reasonably stable data sets, be aware that using this option
has potentially serious consequences later on if you reorder, add, or
delete columns.
Click Next to display the final page of the wizard.
Click Finish. Visio
now displays the External Data window under the drawing pane. The
External Data window contains one row for each row of data in your
spreadsheet.
Tip:
In this exercise, you
will link to only one Excel worksheet. However, it is possible to link a
single diagram to more than one data source whether the sources are all
of the same type or consist of a mix of databases, spreadsheets, and
SharePoint lists.
Drag data row 101 onto the Log hiring request process shape. As
you drag the data row, notice that the cursor appears to be dragging an
outline of a shape across the page (see the following graphic on the
left). Also, notice that the cursor is accompanied by a plus sign (+).
This is the method Visio uses for letting you know that you are dragging
the data for a specific shape type across the page.
Tip:
If you are dragging a data
row onto a shape that is already on the page, as you are in this
exercise, it doesn’t matter whether the shape under the cursor matches
the target shape. Visio will add the data to the existing shape.
In addition to dragging data
rows onto existing shapes, you can create new shapes by dragging a data
row onto a blank area of the drawing page. To do this, click once on the
desired master in the stencil to select it, and then drag a data row
onto the page. The shape under the cursor will reflect the master you’ve
selected.
As
the cursor moves onto the target shape, the plus sign is replaced by a
linking symbol. In addition, the outline shapes take on a thicker, blue
border (see the graphic on the right).
The end result of the drag and
drop doesn’t change the appearance of the shape on the page but you’ll
notice the addition of a linking symbol at the left end of the top data
row, as shown in the following graphic.
Tip:
If you need to know which row
is linked to which shape, right-click on the row, and then click Linked
Shapes to see the answer. If you need to remove the link between a data
row and a shape, right-click on the row, and then click Unlink.
Open the Shape Data window for the Log hiring request shape. As
you can see, the data from the Excel spreadsheet now resides in the
Visio shape. And even more important, potentially, is that this is a
live link, as you’ll see in the next section.
Drag
the remainder of the data rows onto the shapes on the page. Shape
102–104 are left-to-right in the top row, and shapes 105–108 are left to
right across the bottom row. (The End shape does not have a data row.) The final result is a fully linked set of data rows.
Tip:
The name on the tab at the
bottom of the External Data window is the name of the worksheet in the
linked Excel workbook. If you will be linking a diagram to more than one
worksheet or even to worksheets in more than one workbook, it is useful
to give each worksheet a unique name.
Note:
Save your changes to the HR Process Map with data drawing in Visio and save your changes to the HR Process Data workbook in Excel. Leave both files open if you are continuing to the next exercise.
Tip:
When you populate shapes with
data from the External Data window, Visio matches the external data
column names with the names of the shape data fields in the target
shape. If there are no matching shape data fields, Visio creates new
shape data fields to accommodate the external data.
|