Help to remove 'Internet Page' garbage from Excel Sheet

[Mulderman]

Hi Guys, I have a couple of Excel sheets onto which some internal intranet pages have been pasted so I can access the data contained within in other workbooks.
I currently use the 'Copy & Paste' solution as I think the tables are produced from an Access database and Web Page data connections dont work in this case. (If there is anotherway to access tabulated data from a web page that wont link to a Web connection - please let me know).

After I have copy and pasted the pages into Excel I am left with a lot of the 'Internet page' garbage, that over time builds up and slows down the workbooks.

Currently I delete it all manually. Some of the items are tiny pictures, but most are 'Control Boxes' that are detailed as an example 'Control 7' as '=EMBED("Forms.HTML:Select.1","")', Control 4 as '=EMBED("Forms.HTML:Hidden.1","")' etc

I have recorded a macro that deletes them, but sometimes the Control boxes have different names so the code ActiveSheet.Shapes("Control 7").Select: Selection.Delete does not always work if the box is named differently.

What I would like is a macro to remove all this garbage form each of my pages, a macro that will delete 'all' the pictures and controls regardless of how many there are (sometimes there might be 3 on the page, sometimes 20 etc.

Many thanks

Mulderman

 

[nigelk]

This will remove all of the shapes in the sheet (change sheet name to suit).

Sub TEST()

Dim sh As Shape

For Each sh In Sheets("Sheet1").Shapes

sh.Delete

Next

End Sub

 

[stemar]

You could try something along the lines of

Sub ClearJunk()

Dim shS As Shape

    For Each shS In ActiveSheet.Shapes
        shS.Delete
    Next shS
    
End Sub

That will clear all shapes in the active (unprotected) worksheet, so use with care!

 

[alvin-chung]

Just a suggestion, after you copy the entire page to Excel (e.g. Sheet1) have you tried copy the data you want from Sheet1 into a new sheet then delete Sheet1?

 

[Mulderman]

Guys, thanks for your help on this, all solutions worked superb and were just what I needed. Thanks for your replys, time and effort !

Cheers

Mulderman

 

[alvin-chung]

Thanks for the feedback, you're most welcome!

 

[stemar]

Thanks for the feedback, you're most welcome!

What he said...!