defining master shape properties by import data from excel spreadsheet

Guessed · #1 04-01-2019, 08:17 PM

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

Robert Edenborough · #2 04-01-2019, 11:18 PM

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

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