Delete shapes in range

[aiter]

In excel I am trying to delete shapes within a range.  Shapes can be pictures, ole objects etc.

I have got the range successfully selected.  Now the problem is I cannot succeed in getting to these shapes within that selection.

I have tried

$oExcel = _Excel_Open()
; derive NewRange
.
.
.
$NewRange.Select  ; now select that range
; now try to get to the shape selection within that range and delete all the shapes
$ShapeRange = $oExcel.Selection.Shapes.SelectAll
$ShapeRange.delete

The problem seems to the shapes is not a valid reference after selection.

Desperate for help.

Edited September 8, 2016 by aiter
spacing

[water]

Start the macro recorder in Excel, select/delete the shapes and then post the resulting VBA macro. Should then be easy to translate to AutoIt.

[aiter]

The problem is I do not want to select a specific shape, I want to delete all the shapes whatever they in the range I have selected.

if I could iterate through the shapes within selection and delete them one by one, it would help.

I have tried the macro to select one shape and this is what I get

ActiveSheet.Shapes.Range(Array("Picture 1")).Select
Selection.Delete

Problem is I do not know if the shape is a picture.

[aiter]

I suspect that shapes is not selectable within a range. I need to select all the shapes within the active sheet, get the cell range for the shape, see if it intersects the range I have selected then delete it.  VBA is not easily translatable in autoit, at least for me.

[water]

$oShapeRange = $oExcel.Selection.ShapeRange
For $oShape In $oShapeRange
    $oShape.Delete
Next

 

[aiter]

 

"C:\AutoIt\RewDocMerge\aiplay2.au3" (71) : ==> The requested action with this object has failed.:
$oShapeRange = $oExcel.Selection.ShapeRange
$oShapeRange = $oExcel.Selection^ ERROR

So close.

[water]

This link describes how it works the other way round. Checks all shapes and if they intersect with your range then delete them:

https://www.experts-exchange.com/questions/27395289/Excel-VBA-Delete-Shape-Objects-Within-a-Cell-Range.html

[aiter]

I found this page, but to translate the interesect part into autoit is going to be a challenge for me

For Each s In ActiveSheet.Shapes
    If Not Intersect(Range("B9:I25"), s.TopLeftCell) Is Nothing And _
       Not Intersect(Range("B9:I25"), s.BottomRightCell) Is Nothing Then
        s.Delete
    End If
Next s

Thanks for all your help so far.

[water]

First thing you need to do is decide how to handle shapes which are only partly within the selected shape.
Do you want to ignore them or should they be deleted as well?

[aiter]

Delete them.

[aiter]

To give you some background on what I am doing :-

A variable merge which allows switches to be defined in the spreadsheet eg

{{switch-1}}
Text, pictures, anything
{{/switch-1}}

Now, when I find the switch I evaluate whether it is true or false.

If false, it must delete everything within the switch bracketing.

If true, it must leave it (removing the switch references).

This allows logos to be included/excluded amongst other things.

Edited September 8, 2016 by aiter

[aiter]

Success!

$NewRange.Select  ; this is the switch bracketing selection
For $s In $oExcel.ActiveSheet.Shapes
   $b = $s.TopLeftCell
   $a = $oExcel.Intersect($NewRange,$b)
   if IsObj($a) then
        $s.Delete
    EndIf
Next

 

[water]

I fear this is only part of the solution.
If the shape only intersects with the upper right, lower left or lower right side with the range then the code does not work.

[aiter]

It is working for what I need.

See screenshot before and after

[water]

Fine