Jump to content
Jewtus

Deleting ambiguous image excel

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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

 


√-1 2^3 ∑ π, and it was delicious!

How to get your question answered on this forum!

Share this post


Link to post
Share on other sites
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 by Jewtus

Share this post


Link to post
Share on other sites
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 by JLogan3o13
  • Like 1

√-1 2^3 ∑ π, and it was delicious!

How to get your question answered on this forum!

Share this post


Link to post
Share on other sites
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 by Jewtus

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×