XL2002: Cannot Set Text in a Diagram Node Shape Through Visual Basic for Applications (317293)

WORKAROUND

To work around this issue, design your macro to automate Microsoft Word or Microsoft PowerPoint in this manner, to create the diagram in the automated program, and then to copy it into the Excel workbook.

The following sample macro creates an organization chart diagram with four nodes in Word, sets the text for each node, and then copies the diagram to an Excel workbook:

Sub TextShapeAddText()
'/ Dim Integer(s)
   Dim i As Integer
   
'/ Dim Object(s)
   Dim oCurShape As Object
   Dim oCurShapeNode As Object
   Dim oCurDiagNode As Object
   Dim oCurWorkApplObj As Object
 
'/ Create a Word application object.
   Set oCurWorkApplObj = CreateObject("Word.Application")
   
'/ Open a new Word document.
   Workbooks.Add
   oCurWorkApplObj.Documents.Add
   
'/ Add a shape.
   Set oCurShape = oCurWorkApplObj.ActiveDocument.Shapes.AddDiagram _
   (msoDiagramPyramid, 10, 15, 400, 475)

'/ Add a node.
   Set oCurShapeNode = oCurShape.DiagramNode.Children.AddNode
       
'/ Add child nodes.
   For i = 1 To 3
       oCurShapeNode.AddNode
   Next

'/ Add text to the child nodes.
   For i = 1 To 4 'Inserting text in each node
      oCurShapeNode.Diagram.Nodes.Item(i) _
      .TextShape.TextFrame.TextRange.Text = Str(i)
   Next
       
'/ Copy the shape to Excel.
   oCurWorkApplObj.ActiveDocument.Shapes.SelectAll
   oCurWorkApplObj.Selection.Copy
   ActiveSheet.Paste       
       
'/ Quit Word.
   oCurWorkApplObj.Quit saveChanges:=False       

End Sub