Help with VBA Code

Cervesero

New Member
Joined
Jul 9, 2010
Messages
2
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

Well-known Member
Joined
Jul 2, 2008
Messages
994
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

Code:
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

New Member
Joined
Jul 9, 2010
Messages
2
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
 

Forum statistics

Threads
1,081,748
Messages
5,361,041
Members
400,610
Latest member
ebey

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top