PDA

View Full Version : Solved: Clearing Clip Art



hiflier
03-08-2009, 08:40 PM
I am playing around with writing a program that will print different types of envelopes, filling in addresses and return addresses from mailing lists of different companies. One of the companies' return addresses uses clipart for a logo. I can copy and paste the logo to put it into the return address area, but I can't figure out how to use VBA to erase the clipart. It doesn't have properties or a name that I can find, so I can't clear it from the return address area. How do I do that using VBA? Thanks for your help.

Edited by Aussiebear: Post moved from Excel Help section

Jay Freedman
03-09-2009, 04:55 PM
If you're doing this in Word, you'll have a much easier time if you create a template for each company containing its return address, and its logo if desired, and use that as the merge document for the company's mailings. A good reference for this is http://www.gmayor.com/Alternative_Return_Addresses.htm. (http://www.gmayor.com/Alternative_Return_Addresses.htm)

If you're determined to do it the hard way, insert the logo and then, while it's selected, insert a bookmark around it. Then your code can refer to the bookmark rather than to the clipart itself.

--
Jay

hiflier
03-11-2009, 12:09 AM
Thanks for your response, but it's in Excel, and I'm doing it as much to learn something new as to create a functional program. So I'd rather stay in Excel. Thanks again.

Dave

lucas
03-11-2009, 07:24 AM
You say that you are doing this in Excel? Please let us know as this is the wrong forum.

It would help if you show us the code that you use. You put the clipart in using vba so there has to be a way to identify it but we would be guessing without seeing your code.

The main part we need to see if it is extensive is the part that inserts the clipart.

You can post your workbook for examination here by clicking post reply at the lower left of the last post......scroll down and look for "manage attachments"

hiflier
03-15-2009, 09:18 AM
I started off in the Excel forum, but Aussiebear moved it to Word. I don't know why. My post now has this at the bottom: Edited by Aussiebear: Post moved from Excel Help section.

Anyway, the clip art is sitting at M1. I copy M1:O4 (to copy the return address that goes with the clipart). I paste the whole thing into A1. Everything is fine. But when I try to ClearContents for the range A1:D4, the text is erased but the clipart remains. The Clipart at M1 is named (in the cell name box just above cell A1) "Picture 4". When I copy it to A1, the Clipart is named "Picture xx". The number (xx) increments each time I go through this routine.

I could use ActiveSheet.Shapes("Picture 21").Cut to get rid of it, if I knew the number for certain. I tried to loop through the possible numbers, but it started erasing lots of other shapes as well. I need more control. How do I know what name is going to be assigned to the clipart?

This could be moved back to Excel, but I don't know how. Thanks.
Dave

lucas
03-15-2009, 09:56 AM
Moved to excel forum.

post your workbook.

mdmackillop
03-15-2009, 10:10 AM
How about
For Each sh In ActiveSheet.Shapes
If sh.TopLeftCell.Address = "$A$1" Then
sh.Delete
End If
Next

hiflier
03-15-2009, 03:34 PM
Thanks. I came back to post my workbook and found your post. That worked perfectly. Thanks again.
Dave

mdmackillop
03-15-2009, 03:36 PM
Happy to help.
Remember to mark you threads Solved.
Regards
MD