Microsoft Office Forums defining master shape properties by import data from excel spreadsheet

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-29-2019, 06:58 AM
Robert Edenborough Robert Edenborough is offline defining master shape properties by import data from excel spreadsheet Windows XP defining master shape properties by import data from excel spreadsheet Office 2013
Novice
defining master shape properties by import data from excel spreadsheet
 
Join Date: Mar 2019
Posts: 2
Robert Edenborough is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 04-01-2019, 08:17 PM
Guessed's Avatar
Guessed Guessed is offline defining master shape properties by import data from excel spreadsheet Windows 10 defining master shape properties by import data from excel spreadsheet Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,187
Guessed has a spectacular aura aboutGuessed has a spectacular aura aboutGuessed has a spectacular aura about
Default

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
Reply With Quote
  #3  
Old 04-01-2019, 11:18 PM
Robert Edenborough Robert Edenborough is offline defining master shape properties by import data from excel spreadsheet Windows XP defining master shape properties by import data from excel spreadsheet Office 2013
Novice
defining master shape properties by import data from excel spreadsheet
 
Join Date: Mar 2019
Posts: 2
Robert Edenborough is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 04-01-2019, 11:31 PM
Guessed's Avatar
Guessed Guessed is offline defining master shape properties by import data from excel spreadsheet Windows 10 defining master shape properties by import data from excel spreadsheet Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,187
Guessed has a spectacular aura aboutGuessed has a spectacular aura aboutGuessed has a spectacular aura about
Default

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
Reply With Quote
Reply

Tags
excel import, properties, shape

Thread Tools
Display Modes


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
defining master shape properties by import data from excel spreadsheet Import data from Excel into Word sb003848 Word 1 11-04-2014 06:30 PM
defining master shape properties by import data from excel spreadsheet 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


All times are GMT -7. The time now is 10:16 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft