Visio2000: How to Use the Database Wizard in Microsoft Visio to Link Shape Data to an Excel Worksheet (323451)

MORE INFORMATION

How to Use the Database Wizard to Link Visio Shape Data to an Excel Worksheet

Part 1: Export Visio Data to the Excel Worksheet

1. Start Excel, create a new workbook, and then save it.
2. Make a note of the name and location of the file, and then quit Excel.
3. Start Visio, and then open the drawing that contains the shape data that you want to export.
   
   NOTE: If you do not have a Visio drawing that you can use, create a new Visio drawing that is based on the Block Diagram template. To do this:
   1. On the File menu, point to New, point to Block Diagram, and then click Basic Diagram.
   2. From the Basic Shapes stencil, drag a Square shape to the drawing page.
   3. Right-click the shape, point to Format, and then click Fill.
   4. In the Color box, click the color that you want, and then click OK.
4. On the Tools menu, point to Macros, point to Visio Extras, and then click Database Export.
   
   The Database Export Wizard starts.
5. Click Next.
6. Verify that the drawing that you want to export appears in the Choose a drawing or click Browse to open a drawing box, and then click Next.
7. Specify the shapes in the drawing from which you want to export data, and then click Next.
8. Specify the Shapesheet cells and fields that you want to export, and then click Next.
9. In the Select an ODBC data source to export to list, click Excel Files, and then click Next.
10. In the Select Workbook dialog box, locate and click the Excel file where you want to export the shape data, and then click OK.
11. Under Specify the export table details, type a name in the Table Name box. This is the name of the Excel worksheet to which the shape data is exported.
    
    Verify that ShapeKey appears in the Key Field box and ShapeID appears in the Key Type box. Click Next.
12. Accept the default settings that appear under Specify the export mapping details. Click Next.
13. Click to select the Add export right mouse action to the drawing page check box, and then click Next.
14. Verify that the options that you selected are correct, and then click Finish.

Part 2: Link Visio Data to the Excel Worksheet

1. On the Tools menu, point to Macros, point to Visio Extras, and then click Database Wizard.
   
   The Database Wizard starts.
2. Click Next.
3. Click Link shapes to database records, and then click Next.
4. Click Shape(s) in a drawing, and then click Next.
5. Verify that the drawing that you want to export appears in the Choose a drawing or click Browse to open a drawing box, and then click Next.
6. Under Choose an existing data source or click Create Data Source to define a new data source, click Excel Files, and then click Next.
7. In the Select Workbook dialog box, locate and click the Excel file that you want to use as the data source, and then click OK.
8. Under Object types, click to select the Tables and Views check boxes, and then click Next.
9. Verify that 1 appears in the Number of fields box, and then click Next.
10. Under Choose the primary key field from the database table, click ShapeKey in the Field list, and then click Next.
11. Verify that 1 is selected in the Key value list, and then click Next.
12. Under Choose the events and actions to add to the shape, do the following:
    1. Under Shape Events, click to select the Include an on drop event with the shape check box and Refresh shape on drop.
    2. Click to select all check boxes under Right mouse actions.
    3. Click Next.
13. Under Choose the shape cell to be used for storing the primary key field value, verify that Prop.ShapeKey appears in the Cell box, and then click Next.
14. Click Automatic, and then click Next.
15. Click Finish.
16. Right-click the shape on your drawing page. Note that the following options appear on the shortcut menu that appears:
    • Select Database Record
    • Refresh Shapes Properties
    • Update Database Record
    • Delete Shape and Record
17. Click the option that you want.