Linking one shape at a time to an Excel row

Started by JatinPatel, April 04, 2014, 09:02:39 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

JatinPatel

Hi,

I am trying to link my shapes to an Excel row using LinkToData method.  I have made the connection and i have the DataRecordSet. In Visio, i have defined data for each Shape using visio GUI. All shapes have same data, two columns: "Name" and "Total Cost". Initially these two have empty and zero default values.  When i link using LinkToData method, it is creating two additional columns in each shapes names "F1" and "F2" and assign values from my Excel row.  Original columns, "Name" and "Total Cost" remains empty.  I debug it and found out that the very first row (row zero) has columns "F1" and "F2" and not "Name" and "Total Cost". "Name" and "Total Cost" are on second row (row 1).  Where "F1" and "F2" are coming from? Is this something from Excel? Is there anyway to ignore them? Is there anyway to link each column individually rather then linking the whole row?

I tried recording the macro and noticed the same.

Thanks
Jatin

Yacine

Hi Jatin,
the LinkToData tool looks for the "labels" of the custom properties, not the name of the props themselves.
So as long as these names match the columns of your excel sheet, the tool shall work just fine.
Yacine

JatinPatel

Hi Yacine,

Thanks for the reply. what i observed is that LinkData looks at the label of the Excel Columns, which is in the row(0) from the GetRowData method. Then it sees if that column label is define in the Shape data properties or not. if not then it creates new data properties based on the column label.

I am wondering, why first row(0) has "F1", "F2", and so on as column label, instead of what i have in the row(1)? is there anyway to tell LinkToData method to use row(1) as label?

Thanks
Jatin

Yacine

#3
No, I mean the labels of the custom properties. They must match the labels of the columns in the excel sheet.
If visio does not find the propper labels, it creates new rows, that match.

There are many tools that relay on the labels, instead of the names (eg reports). I suppose, that MS wanted the less advanced users to be able to use the tools without having to access the shapesheet. In my opinion it is rather confusing.
Yacine

JatinPatel

Thanks Yacine again for you reply.

I do have the correct labels in custom properties, which matches with the labels of the columns in the 1st row in the excel sheet. Somehow, it is using F1, F2, etc, which is in row(0) of the row array returned from LinkToData method. I don't know how to set the labels in excel besides putting them on the 1st row.

JatinPatel


Yacine,

when i right click on my shape and select Data -> Show Linked Row, an Excel sheet pops up. Here i see columns being names as "F1", "F2", etc.

Yacine

#6
Jatin,
since Visio has already made connections to F1, F2 columns, you need either to link from a clean file where you haven't linked anything yet, or remove all the data relevant fields in the shapesheets of the page and the shapes to get rid of the F1, F2 fields
Yacine

aledlund

"i see columns being names as "F1", "F2",", this is the ODBC (the database engine that MS uses) effect when the excel sheet  does not have any header (column names) declared in what was brought in from Excel.
short answer your using an incomplete range from Excel.
al