Help with VBA Code

[Cervesero]

I Have a macro that reads excel file names that are stored in a particular location.

The macro is supposed to

• Open each file name specified on each row of predefined column
• Cycle through the number of worksheets in the opened file.
• Remove any shapes (pictures)
• Save the modified file in a new location.

The problem that I am having is that the line of code Selection.Delete is not deleting the images.

Everything Else Works.

Please find code below.

Sub Macro1()


'------------Integers--------------

Dim Number_of_Worksheets As Integer
Dim File_Name_Row As Integer
Dim File_Name_Col As Integer


'------------File System Object --------------
Dim WExcel As Excel.Application, WBook As Excel.Workbook, WSheet As Excel.Worksheet
Dim text As String



File_Name_Col = 1
File_Name_Row = 2


Do While Sheet2.Cells(File_Name_Row, File_Name_Col) <> "" 'Do all tags in sheet1 - TAG


Set WExcel = New Excel.Application
Set WBook = WExcel.Workbooks.Open(Sheet1.Cells(19, 1) & "\" & Sheet2.Cells(File_Name_Row, File_Name_Col))
Set WSheet = WBook.ActiveSheet
'the command below is to see the excel file visibly
WExcel.Visible = True

Number_of_Worksheets = WBook.Sheets.Count



For csht = 1 To Number_of_Worksheets 'worksheet or sheets

WBook.Sheets(csht).Activate
WBook.Sheets(csht).Shapes.SelectAll
Selection.Delete


Next csht

'Save New File
WBook.Activate
WBook.SaveAs Filename:=Sheet1.Cells(21, 1) & "\NEW" & Sheet2.Cells(File_Name_Row, File_Name_Col)
WBook.Close

File_Name_Row = File_Name_Row + 1

Loop



End Sub

 

[sulakvea]

welcome to the board... sorry dont have time to go thru every line but you dont have to select an object to delete it - here is a quick sample on how an object is deleted

ThisWorkbook.Sheets("Sheet1").Activate
ActiveSheet.Shapes("Rectangle 1").Delete

this (edited to suit your needs) goes instead of this part
WBook.Sheets(csht).Activate
WBook.Sheets(csht).Shapes.SelectAll
Selection.Delete

HTH - maybe someone can add smth to this

 

[Cervesero]

Thanks for the quick reply.

The problem with the code suggested, is that the macro does not know the name of the object , ie Each worksheet might have different names for the shape.


ThisWorkbook.Sheets("Sheet1").Activate
ActiveSheet.Shapes("Rectangle 1").Delete</pre>
Cheers