External Shape Data Problem (Visio 2007 + Excel)

[Cameron Wells]

Apologies for the detail contained in this question, but I'm really stumped
and hoping someone can sterr me in the right direction. I'm using Visio 2007
- here goes:

I wish to be able to update a Gantt Chart Shape “Task Bar†from Excel.

I have an Excel worksheet that mimics the fields of the Gantt Chart Shape
“Task Bar†consisting of:

Name, Start Date, End Date, Duration, Percent Complete, Resource, Notes,
Actual Start, Actual End, Actual Duration, Outline Level, User-Defined
Number , User-Defined Duration, User-Defined Text Field 1, User-Defined Text
Field 2, User-Defined Text Field 3, User-Defined Text Field 4 and
User-Defined Text Field 5.

Starting with a Gantt Chart Shape on a clean sheet with the following options:

Task Options: Number of Tasks = 5
Time Units: Major Units = Years
Time Units: Minor Units = Months
Duration Options: Format = Days
Timescale Range: Start Date = 01/01/2007
Timescale Range: End Date = 01/01/2010

I drag a Task Bar Shape from the Gantt Chart Shapes onto the Gantt Chart
anywhere between Jan 2007 and Jan 2010 in row 1 (Task 1).
I then link one of the rows from my External Data window and some, but not
all of the updates Shape Data Values are updated.

Looking at the Shape Data for the Task Bar:

‘Name’ is updated and displayed correctly in the drawing.
‘Start Date’ is updated and displayed correctly in the drawing.
‘End Date’ is not updated and therefore the End Date of the Task Bar is not
displayed correctly (NB that the External Data Window has the updated info,
but not the Shape Data Window)
‘Duration’ is not updated – and I guess automatically calculated from the
start/end date.
‘Percent Complete’ is not updated and therefore not displayed correctly.
‘Resource’ is updated, but not displayed.
‘Notes’ are updated, but are not being displayed
‘Actual Start’ is not updated
‘Actual End’ is not updated.
(I haven’t bothered populating the remaining fields yet)

Could someone please offer some insight into what it is I’m doing wrong –
particularly why I can’t get the ‘End Date’ Shape Data to update from Excel,
so that the Shape position and size is updated too?

I’ve checked, to the best of my ability, that the Excel data is formatted
accordingly (ie, Text is Text, Dates are Dates etc). I’m at a loss and
appreciate any clues to how I can remedy this.

(FWIW, I've sat thru all of the online Visio2007 Tutorials today - and
trolled a bunch of other groups, but to no avail).

Please Help If You Can

 

[John Goldsmith]

Hello Cameron,

I'm afraid I haven't spent a lot of time on this but my guess is that the
coding behind the Gantt Chart addon does some overwriting of cells on drop
(particularly if you drop the shape on the chart itself).

I've just done a quick test with Name, Start Date and End Date and found
that if you select the Task shape in the stencil and then drag an External
Data Row onto the page (but not onto the chart) you can then in a second
move drag the same task shape onto the chart and everything appear to
display correctly.

You don't have to do this one by one but can multiple select rows in the
External Data window, drop them on the page and then drag them onto the
chart (as a multiple selection).

One other thing to be aware of is the need to check that your External Data
column headings match the Shape Data row labels exactly. You can either do
this in your data source or in the External Data window itself by right
clicking in the window and selecting Column settings.

Anyway, let me know if this works for you.

Best regards

John


John Goldsmith
www.visualSignals.co.uk