#1
|
|||
|
|||
defining master shape properties by import data from excel spreadsheet
Hi, this is my first post so please be patient!
I am trying to create several new shapes in MS Visio and assign them lots of different properties. Each shape will have properties associated with it like: 1. Document name 2. Document issue status 3. Date but there may be multiple copies of any one particular shape each with different values assigned to it. Typically there are 100+ properties per shape! I can happily create the shapes themselves and put them into my own shape template but the problem comes when I try to define the shape properties. I can put in one property at a time via the 'shape data > define' route but that is laborious when I have in excess of 100 slightly different properties to enter for each shape. Hence I have created a MS Excel sheet with the data (faster to create since can cut'n'paste and do search/replace etc.) What I can't find is an easy way to get the data from the Excel sheet to the Visio shape data panel. https://www.youtube.com/watch?v=SuPzJ5g8Z0E - looked promising but doesn't quite do it. Other things I have found all seem to 'link' the data to the excel file which I don't want to do (since when the shapes are put onto the drawing the user will then modify data associated with each occurrence of the shape which would modify every occurrence of that shape). What I really want to do is use Excel to create the shape data table which I import into Visio and associate with the master shape properties. Thanks |
#2
|
||||
|
||||
Yes, I can see that would be a very slow way to do things.
I haven't tried to do this before and it may be that you need to create a macro to read the excel data table and create all the shape properties at least for one shape. Once this is done, you could use the same macro or maybe use Shape Data Sets to copy the properties from one shape to another. Below is a sample macro where I supply a list of field names and add each one as a property to the selected shape on the page. Code:
Sub AddPropertiesToAShape() Dim arrFields() As String, i As Integer, aShp As Shape, iRow As Integer, sVal As String Dim sel As Visio.Selection Set sel = ActiveWindow.Selection arrFields = Split("Field1,Field2,Field3,Field4", ",") For i = LBound(arrFields) To UBound(arrFields) With sel.PrimaryItem .AddRow visSectionProp, 1, visTagDefault iRow = .RowCount(visSectionProp) - 1 sVal = arrFields(i) .CellsSRC(visSectionProp, iRow, visCustPropsValue).RowNameU = sVal .CellsSRC(visSectionProp, iRow, visCustPropsValue).FormulaForceU = """""" .CellsSRC(visSectionProp, iRow, visCustPropsType).FormulaForceU = "0" End With Next i ' Application.ActiveWindow.Shape.AddRow visSectionProp, 2, visTagDefault ' Application.ActiveWindow.Shape.CellsSRC(visSectionProp, 3, visCustPropsValue).FormulaForceU = "0" ' Application.ActiveWindow.Shape.CellsSRC(visSectionProp, 3, visCustPropsPrompt).FormulaForceU = """""" ' Application.ActiveWindow.Shape.CellsSRC(visSectionProp, 3, visCustPropsLabel).FormulaForceU = """""" ' Application.ActiveWindow.Shape.CellsSRC(visSectionProp, 3, visCustPropsFormat).FormulaForceU = """""" ' Application.ActiveWindow.Shape.CellsSRC(visSectionProp, 3, visCustPropsSortKey).FormulaForceU = """""" ' Application.ActiveWindow.Shape.CellsSRC(visSectionProp, 3, visCustPropsType).FormulaForceU = "0" ' Application.ActiveWindow.Shape.CellsSRC(visSectionProp, 3, visCustPropsInvis).FormulaForceU = "FALSE" ' Application.ActiveWindow.Shape.CellsSRC(visSectionProp, 3, visCustPropsAsk).FormulaForceU = "FALSE" ' Application.ActiveWindow.Shape.CellsSRC(visSectionProp, 3, visCustPropsDataLinked).FormulaForceU = "FALSE" ' Application.ActiveWindow.Shape.CellsSRC(visSectionProp, 3, visCustPropsLangID).FormulaForceU = "3081" ' Application.ActiveWindow.Shape.CellsSRC(visSectionProp, 3, visCustPropsCalendar).FormulaForceU = "0" End Sub
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia Last edited by Guessed; 04-01-2019 at 10:40 PM. Reason: added sample code |
#3
|
|||
|
|||
Hi Andrew,
Thank you for a very quick response. That looks very promising. Will that macro work if the selected shape is a master shape (i.e. I'm editing master shape data/properties). Or alternatively I suppose I could assign the properties to a shape on the drawing and then copy that back to the master stencil. Robert |
#4
|
||||
|
||||
That example is using the selected shape on the page but it could be adapted to apply to a specific master shape. See http://www.visguy.com/2008/02/25/edi...the-right-way/ to start learning about the way to edit master shapes in a stencil
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
Tags |
excel import, properties, shape |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Defining a circle track data in Excel | sjtaloga | Excel | 0 | 04-22-2016 08:12 AM |
Trying to import specific data from one spreadsheet to another, without matching rows | Wynka | Excel | 0 | 11-26-2014 09:33 AM |
Import data from Excel into Word | sb003848 | Word | 1 | 11-04-2014 06:30 PM |
Set shape properties exactly | AndersIII | PowerPoint | 3 | 02-11-2014 07:17 AM |
Import Excel spreadsheet calendar data into Outlook 2010 | davesp | Outlook | 0 | 09-30-2013 09:26 AM |