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.E xcel.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.E xcel.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(ShpStrin g)
'*** This does not work. Syntax is ok
oDrawing.Shapes.Range({shp List}).Gro up()
'*** This does not work either. Syntax is ok
oDrawing.Shapes.Range({Shp String}).G roup()
'*** 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
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.E
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({...
Dim shp As Microsoft.Office.Interop.E
Dim shpList As New ArrayList() 'Try passing an array to oDrawing.Shapes.Range({...
Dim ShpString As String = "" 'Try passing a string to oDrawing.Shapes.Range({...
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
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(ShpStrin
'*** This does not work. Syntax is ok
oDrawing.Shapes.Range({shp
'*** This does not work either. Syntax is ok
oDrawing.Shapes.Range({Shp
'*** 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
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
oDrawing.Shapes.SelectAll
With oExcel.Selection
.ShapeRange.Group.Select
Set s = oExcel.ActiveSheet.Shapes(
s.Name = "MyGroup2"
End With
VB net: Selection does not have this or your suggestion Selection.Group
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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?