Umit Unal
asked on
visio gantt chart auto update with excel data
Hello
I want to link my excel data to gantt chart shape in visio to update values in excel and reflect in gantt chart accordingly.
After I import my excel data into visio, i am trying to link this data (row data) to my gantt chart shape by drag-drop or through menu data link option. But updated data is being reflected in shape data but not being reflected in gantt chart frame itself.
all column names in gantt chart and in excel data are same and i refer (task #) column as ref. column to match data.
but when I link relevant data row to each object in same task line in gantt chart frame, start dates are being updated for example, but not finish date or duration or task name being updated even though i drag and drop onto each cells in same task line in gantt chart
When i click on task name for example , i can see updated data in shape data window as it is linked already to my excel data, but that value or text does not show up in the drawing (gantt chart)
When i try this data linking on other shapes than gantt chart (timeline block e.g.) i dont get such issue and once it is updated in shape data, it is being reflected in drawing too.
Can you assist on that?
Thanks
I want to link my excel data to gantt chart shape in visio to update values in excel and reflect in gantt chart accordingly.
After I import my excel data into visio, i am trying to link this data (row data) to my gantt chart shape by drag-drop or through menu data link option. But updated data is being reflected in shape data but not being reflected in gantt chart frame itself.
all column names in gantt chart and in excel data are same and i refer (task #) column as ref. column to match data.
but when I link relevant data row to each object in same task line in gantt chart frame, start dates are being updated for example, but not finish date or duration or task name being updated even though i drag and drop onto each cells in same task line in gantt chart
When i click on task name for example , i can see updated data in shape data window as it is linked already to my excel data, but that value or text does not show up in the drawing (gantt chart)
When i try this data linking on other shapes than gantt chart (timeline block e.g.) i dont get such issue and once it is updated in shape data, it is being reflected in drawing too.
Can you assist on that?
Thanks
ASKER
Thanks scott For your assist
Please find attached sample files i tried to link for your review
As you noted, gantt chart programme coding might override on excel data link somehow which makes using gantt chart for big volume of data inefficient as it will need manual data input for each shape in gantt chart
Regards
Umit
visio-test--for-gantt-chart.vsdx
test-data-for-gantt-chart.xls
Please find attached sample files i tried to link for your review
As you noted, gantt chart programme coding might override on excel data link somehow which makes using gantt chart for big volume of data inefficient as it will need manual data input for each shape in gantt chart
Regards
Umit
visio-test--for-gantt-chart.vsdx
test-data-for-gantt-chart.xls
Sorry for the delay in responding. I've spent some time experimenting this morning and while I can improve things a bit, there are several remaining issues.
I started by changing the field names in Excel: the main reason you didn't see correct values and behavior in the Gantt Chart shapes after data linking was because the field names in the Excel workbook and the names in the Gantt Chart shapes are not the same. If the imported field names in the External Data window don't match existing field names, Visio creates new fields when you link a row to a shape. And, of course, the Gantt Chart code can't know about new field names so it ignores them.
I've attached a modified version of your workbook to show what's required for the field names; the highlighted column names are the ones that I changed to match the field names in the Visio Shape Data window. (NOTE: the first tab, Sheet1, contains the updated header row; you should link to this tab. The second tab shows both the old names and new names for easy comparison.)
The good news is that renamed fields match up properly with the Gantt Chart shape data fields. The bad news is that the Gantt Chart add-in apparently acts on the Duration and End Data fields in ways that aren't clear. Assuming this might be a data type issue, I tried a number of different settings but didn't succeed in getting everything to work correctly. You might try deleting the Duration column from Excel and setting the End Data manually to see whether the Gantt Chart add-in behaves better if it only has one of the two to deal with.
test-data-for-gantt-chart_UPDATED-FI.xls
I started by changing the field names in Excel: the main reason you didn't see correct values and behavior in the Gantt Chart shapes after data linking was because the field names in the Excel workbook and the names in the Gantt Chart shapes are not the same. If the imported field names in the External Data window don't match existing field names, Visio creates new fields when you link a row to a shape. And, of course, the Gantt Chart code can't know about new field names so it ignores them.
I've attached a modified version of your workbook to show what's required for the field names; the highlighted column names are the ones that I changed to match the field names in the Visio Shape Data window. (NOTE: the first tab, Sheet1, contains the updated header row; you should link to this tab. The second tab shows both the old names and new names for easy comparison.)
The good news is that renamed fields match up properly with the Gantt Chart shape data fields. The bad news is that the Gantt Chart add-in apparently acts on the Duration and End Data fields in ways that aren't clear. Assuming this might be a data type issue, I tried a number of different settings but didn't succeed in getting everything to work correctly. You might try deleting the Duration column from Excel and setting the End Data manually to see whether the Gantt Chart add-in behaves better if it only has one of the two to deal with.
test-data-for-gantt-chart_UPDATED-FI.xls
ASKER
Thanks Scott for your help
As you noted, seems exact match is needed on names, i had expected an ID column can be used to link which is more stable than name column as names are tend to change on my case
Regards
Umit
As you noted, seems exact match is needed on names, i had expected an ID column can be used to link which is more stable than name column as names are tend to change on my case
Regards
Umit
You can use an ID instead of a name to match employees to their managers. However, it's important to realize that there are two levels of matching going on.
The first is the column names in Excel vs. the Shape Data field names -- those must match or Visio will add new shape data fields with any column names from Excel that don't match. This match doesn't have anything to do with building the org chart itself, but is important for connecting the Excel data to the right fields in Visio.
Second, regardless whether you choose an ID or a name, the values in the "Reports To" column must match values in the Employee ID (or name). This is the match that lets the Wizard connect the org chart shapes.
If your question has been answered, please close the question at your convenience.
The first is the column names in Excel vs. the Shape Data field names -- those must match or Visio will add new shape data fields with any column names from Excel that don't match. This match doesn't have anything to do with building the org chart itself, but is important for connecting the Excel data to the right fields in Visio.
Second, regardless whether you choose an ID or a name, the values in the "Reports To" column must match values in the Employee ID (or name). This is the match that lets the Wizard connect the org chart shapes.
If your question has been answered, please close the question at your convenience.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Can you post an example of the both the Visio diagram that doesn't behave properly and the corresponding Excel data? A simple example with just a few rows will do. Please don't include any confidential data.