Results 1 to 8 of 8
  • Thread Tools
  1. 4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    549
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete a picture with vba (excel xp)

    Is there a way to programmatically in vba delete all the picture on a worksheet in excel?

    I have a picture on an excel sheet that i put on and size with a macro. The problem is that i need to put a different picture on when i run the macro again and i need to delete the old picture using vba. I tried recording a macro but it refers to the picture number like "Picture 3" and this number changes so it won't always be picture 3 when i cut and paste it several times. Anyway, is there code to delete all the picture and make the sheet blank? i can't just delete the sheet and create a new one because the sheet is linked to a slide in powerpoint. thank you for the help.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Delete a picture with vba (excel xp)

    Worksheets("SheetName").DrawingObjects.Delete

    will delete all pictures on the sheet.
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7ąDS

  4. 4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    549
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete a picture with vba (excel xp)

    yep, that's the ticket!

    This is my code

    For I = 1 To 8
    Sheets("Q" & I).Select
    'delete the existing jpg
    ActiveSheet.DrawingObjects.Delete
    ActiveSheet.Pictures.Insert("Cics" & mnumber & "q" & mnumber & "_" & I & ".jpg").Select
    Selection.ShapeRange.Height = 623.25
    Selection.ShapeRange.Width = 482.25
    Selection.ShapeRange.Left = 5
    Selection.ShapeRange.Top = 10
    Next I

    some of the folders only have 3 jpgs and some have 8. How do i bail out if there is only 3. i'm getting an error message because the macro can't find the 4th jpg. thank you for the help

  5. Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Delete a picture with vba (excel xp)

    Something like (insert the red colored code into your code, not tested):

    ActiveSheet.DrawingObjects.Delete
    <font color=red>If Len(Dir("Cics" & mnumber & "q" & mnumber & "_" & I & ".jpg", vbDirectory)) = 0 Then Exit For</font color=red><font color=448800> ' exits the loop if the file does not exist</font color=448800>
    ActiveSheet.Pictures.Insert("Cics" & mnumber & "q" & mnumber & "_" & I & ".jpg").Select
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7ąDS

  6. Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete a picture with vba (excel xp)

    And other controls too ......
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts

    Re: Delete a picture with vba (excel xp)

    Here is an alternative that tests if a shape is a picture before deleting it:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Dim wsh As Worksheet
    Dim i As Long

    Set wsh = ActiveWorkbook.Worksheets("MySheet")
    For i = wsh.Shapes.Count To 1 Step -1
    If wsh.Shapes(i).Type = msoPicture Then
    wsh.Shapes(i).Delete
    End If
    Next i

    Set wsh = Nothing

    <img src=/w3timages/blueline.gif width=33% height=2>

    Note: DrawingObjects has been superseded by Shapes.

  8. Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Delete a picture with vba (excel xp)

    Thanks, Hans. Funny how the macro generator still uses DrawingObjects. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7ąDS

  9. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts

    Re: Delete a picture with vba (excel xp)

    >> Funny how the macro generator still uses DrawingObjects

    Yes, apparently Microsoft doesn't want to change the way the macro recorder works. Similarly, some of the Wizards in Access 2002 still generate Access 95 code...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •