Deleting ambiguous image excel

[Jewtus]

I am trying to have my script delete a specific image, but unfortunately, every time a new image is added to the file, the index goes up and the name changes so I can't hard code a name. This is what I'm doing and it works until I insert a new image:

$wkbkFixer=$oExcel.WorkBooks.Open (@ScriptDir&"\01-Current\"&$aFullPaths[$z])
            $sheetFix=$wkbkFixer.Worksheets("OperationalRisk")
            MsgBox(0,"Unknown","Excel file doesn't appear to do anything...")
            $sheetFix.Unprotect('password')
            $sheetFix.Shapes('Picture 6').Delete
            #cs
            Name of object found by adding macro on image click. Use the following macro
             CallingShapeName = ActiveSheet.Shapes(Application.Caller).Name
             msgbox CallingShapeName
            #ce
            $sheetFix.Protect('password')
            $wkbkFixer.Save
            $wkbkFixer.Close

I'm wondering if there is a way to delete all the shapes except the ones I specify to handle when new images are added. (basically I'm trying to keep the header and footer images but delete the rest). Anyone know if there is a way of doing this?

[JLogan3o13]

This seems to work for me:

#include <Excel.au3>

$oApp = _Excel_Open()
$oWorkbook = _Excel_BookOpen($oApp, @DesktopDir & "\Pics.xlsx")
$oSheet = $oWorkbook.WorkSheets("Sheet1")

    For $i = 1 To 10
        If $i <> 2 Then $oSheet.Shapes('Picture ' & $i).Delete
    Next

 

[Jewtus]

I originally tried something similar, but the problem is, if you delete picture 6 and insert a new one, there is no longer a picture 6 object but there is a picture 7 (every time this happens the number goes up) which causes this error:

"C:\FullyAuto.au3" (152) : ==> The requested action with this object has failed.:
If $i <> 2 Then $sheetFix.Shapes('Picture ' & $i).Delete
If $i <> 2 Then $sheetFix^ ERROR

Basically I need to check if the object exists and then delete it if it does without crashing the script

Edited June 10, 2015 by Jewtus

[JLogan3o13]

That's odd, when I tested just now it did not crash my script, even though I only had three pictures and not 10. What version of Excel are you using, out of curiosity?

What if you do something like this first?

$oCount = $oSheet.Shapes.Count

to get the count and then 

For $i = 1 To $oCount
        ConsoleWrite($oSheet.Shapes($i).Name & @CRLF)
    Next

to get the name of each pic?

Edited June 10, 2015 by JLogan3o13

[Jewtus]

I use excel 2010. But I like your idea. Then I can use the name to do an if statement and delete it!

 

That is exactly what I needed

$wkbkFixer=$oExcel.WorkBooks.Open (@ScriptDir&"\01-Current\"&$aFullPaths[$z])
            $sheetFix=$wkbkFixer.Worksheets("OperationalRisk")
            $sheetFix.Unprotect('password')
;~          #cs
;~          Name of object not to delete found by adding macro on image click. Use the following macro
;~           CallingShapeName = ActiveSheet.Shapes(Application.Caller).Name
;~           msgbox CallingShapeName
;~          #ce
            $oCount = $sheetFix.Shapes.Count
            For $i = 1 To $oCount -1
                $name=$sheetFix.Shapes($i).Name
                If $name <> 'Picture 7' AND $name <> 'Picture 17' Then $sheetFix.Shapes($i).Delete ; If it is not these images then delete the image
            Next
            $sheetFix.Protect('password')
            $wkbkFixer.Save
            $wkbkFixer.Close

 

Edited June 10, 2015 by Jewtus

[JLogan3o13]

Glad to be of help