Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Replace multiple pics in Excel 1

Status
Not open for further replies.

HVtech

IS-IT--Management
Jul 1, 2010
71
NL
Hi,
I have a sheet with mutiple (same) pictures on it, so when this sheet is printed, the picture (kind of logo) sits on top of every page.
Now I want to change those pictures with a new logo with a macro.
I tried this piece of code I found, but it's nit quite what I need.
Also , i have to run it a few times before all old pics are deleted.
The big problem I think is, the placement of the pics.
You can iagine that every 50 lines (or so) this pic is present and needs to be replaced at the same position.
Hope someone can help me.
This is the code what I tried:
Code:
Sub NieuwLogo()
Dim vs As Worksheet
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
    For i = 1 To ws.Shapes.Count
    If ws.Shapes(i).Name = "Group 28" Then
        ws.Shapes(i).Delete
        ws.Pictures.Insert ("Z:\Thea\logo-oranje.png")
    'With ws.Shapes(ws.Shapes.Count)
    With ws.Shapes(ws.Shapes)
        .Top = ws.Range("A1").Top
        .Left = ws.Range("A1").Left
        .LockAspectRatio = msoFalse
        .Height = 150
        .Width = 150
    End With
    Else
    End If
Next i
Next ws
End Sub

Office 2010
 
Hi,

You really need only Zone, if you use the rows to repeat at the top feature.



Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
You're talking chinese.. don't understand what you mean.. sorry

Office 2010
 
Ok, inserted the header pic, recorded as a macro, working fine.
One problem to go, the removal of the old pics, where the first is namen "Picture 29", all others are named "Group 28"
Can you assist me on that?
Than I can combine all parts code to one.
Thanks !

Office 2010
 
Deletes all shapes in ActiveSheet....

Code:
Dim sp as shape

For each sp in ActiveSheet.shapes
   Sp.delete
Next

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
O sh*t... I forgot.. there is one "shape" that needs to remain there, is called "Grafiek 38"
Sorry Skip.. can this be excluded from the Sp.delete routine (which is working fine btw..)

Office 2013
 
Code:
Dim sp as shape

For each sp in ActiveSheet.shapes
   Select case sp.name
      Case "your name here","add another if required to exclude"
      Case else 
          Sp.delete
   End select
Next

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Thanks a lot, Skip!
Great and fast help!!


Office 2013
 
To show appreciation of the help received, click on:[blue]

Like this post?
Star it!
[/blue]

This also shows other users that this was a helpful post.


Have fun.

---- Andy
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top