Macro to delete text 1

[ecugrad]

Not sure how to accomplish this:

We currntley export a report into word from an external application. The report contains up to 200.png check images, it also contains a lot of text that we have to go in and manually delete. All we want is the images of the checks. Is their a Macro we can set-up in either Word or Excel that will delete all text and only leave the images on import or one that we can run once imported.

Thanks for any help...

 

[SkipVought]

Hi,

In Excel...

Sub ClearText()
  dim ws as worksheet
  for each ws in worksheets
    ws.Cells.ClearContents
  next
End sub

Skip,
[sub]
[red][/red]
[/sub]

 

[ecugrad]

Thanks SkipVought, another question, the check images have a back and front. All we need is the front portion of the check. Is there any code we could use to delete every other image from the spreadsheet?

Thanks again...

 

[SkipVought]

sub DeleteEvenIndexShapes()
'odd index - front of check
'even index - back of check
  dim i as integer, ws as worksheets
  for each ws in worksheets
    for i = ws.shapes.count to 2 step -2
      ws.shapes(i).delete
    next
  next 
end sub

Skip,
[sub]
[red][/red]
[/sub]

 

[fumei]

Interesting. It is much more fussy to get the same result in Word. Not that you Excel people would be surprised at that....

I was surprised that

ws.Cells.ClearContents

would not clear the images. But then...doh!...I figured out that all shapes in Excel are Shapes. There seems to be no equivalent InlineShape in Excel. Which makes sense I guess. So clearing the contents of cells, clears contents and leaves the Shapes alone.

If the images in Word are InlineShapes, the equivalent straight-forward clearing of text also removes the images. So you have to check to see if the image is an InlineShape (and therefore included in the paragraph), or a Shape.

It took me four times the code to make it work in Word. It got tricky if a paragraph had text (to be removed) AND an InlineShape image (to be retained).

Not that this is at all contributing to this thread...just a comment.

Gerry
My paintings and sculpture