PDA

View Full Version : Shapes



kvb
11-02-2006, 12:29 PM
Hi All,

I need to cycle through 1,000 workbooks and replace a specific shape within those workbooks. However the name of the shape changes from one workbook to another. The shape is always in the same location. Is there some way that I can select a shape based on it's location on the spreadsheet. I have looked at ZOrder, but it is not consistent for me.

I could create a range and select all within the range, but am unfamiliar with the syntax.

This is a company logo that I will remove and place a new one in its place, when I insert the new one, I will give it a specific name, to make the modification easier in the future.

Thanks

Ken

mdmackillop
11-02-2006, 01:23 PM
Hi Ken
Welcome to VBAX
Try the TopLeftCell property
Regards
MD

Sub Macro1()
For Each sh In ActiveSheet.Shapes
If sh.TopLeftCell.Address(0, 0) = "C10" Then
MsgBox sh.Name
End If
Next
End Sub

kvb
11-03-2006, 04:38 AM
MD,

Thanks, it was exactly what I was looking for. :friends:

Ken

mdmackillop
11-03-2006, 04:58 AM
No problem Ken,
Are you OK with cycling through the workbooks? There are some KB items for looping through files in folders and sub-folders, but let us know if you need assistance.
Regards
MD

kvb
11-03-2006, 07:05 AM
MD,

Yes I use the following code to cycle through a list of spreadsheets. Shapes were a new item for me and was not sure how to manipulate them.

Regards
Ken


Sub action_by_list()
Dim ourList As New Collection
Dim i As Integer
Dim CellName As String
Dim keepgoing As Boolean
Dim strDirectory As String
Dim proj As String
' Directory location
'********************************
strDirectory = ""
'********************************
i = 1
keepgoing = True
While keepgoing
CellName = "A" + LTrim(Str(i))
If Excel.Range(CellName).Value = "" Then
keepgoing = False
Else
ourList.Add strDirectory + Excel.Range(CellName).Value
i = i + 1
End If
Wend

For i = 1 To ourList.Count

Excel.Workbooks.Open ourList.Item(i), False, False
' MACRO HERE
Call print_1_a4
Next i
End Sub