Link to home
Start Free TrialLog in
Avatar of MikeJW
MikeJW

asked on

vb net -> Excel How to group worksheet shapes

There is an easy way of grouping a few named shapes but how do I group hundreds of shapes when the number of shapes varies?
Here is my code :  VS2010 Office 2010
oDrawing is the worksheet with the drawing/shapes
'Group shapes in oDrawing
    Public Sub GroupShapes()
        'This works using shape names
        Dim Grp As Microsoft.Office.Interop.Excel.Shape = oDrawing.Shapes.Range({"S1", "S2", "S3"}).Group()
        Grp.Name = "myGroup1"

        'Unfortunately my drawing has 140 -> 200 shapes with various names.  The list varies.
        'How do I pass all shapes to oDrawing.Shapes.Range({....}).Group()
        Dim shp As Microsoft.Office.Interop.Excel.Shape
        Dim shpList As New ArrayList()      'Try passing an array to oDrawing.Shapes.Range({....}).Group()
        Dim ShpString As String = ""        'Try passing a string to oDrawing.Shapes.Range({....}).Group()

        Console.WriteLine("Number of shapes = " & oDrawing.Shapes.Count)
        'Build an array of shape names, build a string with correct syntax
        For Each shp In oDrawing.Shapes
            Console.WriteLine(shp.Name & vbTab & shp.Top & vbTab & shp.Left)
            shpList.Add(shp.Name)
            ShpString = ShpString & shp.Name & Chr(34) & ", " & Chr(34)
        Next

        'Trim the string properly
        ShpString = ShpString.Substring(0, ShpString.Length - 3)
        Console.WriteLine(ShpString)
        '*** This does not work. Syntax is ok
        oDrawing.Shapes.Range({shpList}).Group()

        '*** This does not work either.  Syntax is ok
        oDrawing.Shapes.Range({ShpString}).Group()

        '*** Alternative method by selecting all shapes?
        Dim Replace As Boolean = True
        For Each shp In oDrawing.Shapes
            shp.Select(Replace)
            Replace = False
        Next
        'Now what do I do?
        'At this point Excel is frozen with all shapes selected!

    End Sub
Avatar of Norie
Norie

Mike

In VBA you can use Selection.Group to group all the shapes but I'm not sure how that would work in VB.Net.

It might be worth a try.

I can't test it myself right now but can check in the morning.

PS Have you tried removing the {} in the examples using shpList and ShpString?
Avatar of MikeJW

ASKER

Thanks but I am still not able to group. In VB6 I could use:-
      oDrawing.Shapes.SelectAll
      With oExcel.Selection
         .ShapeRange.Group.Select
         Set s = oExcel.ActiveSheet.Shapes(1)
         s.Name = "MyGroup2"
      End With
VB net:  Selection does not have this or your suggestion Selection.Group
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MikeJW

ASKER

Thanks imnorie
This works very well and I can be selective when grouping, as all my shapes are named when added to the drawing.
I am very pleased with your solution.