Jump to content
aiter

Delete shapes in range

Recommended Posts

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 by aiter
spacing

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites
water
$oShapeRange = $oExcel.Selection.ShapeRange
For $oShape In $oShapeRange
    $oShape.Delete
Next

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


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

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


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

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
aiter

Delete them.

Share this post


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

Share this post


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

 

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
aiter

It is working for what I need.

See screenshot before and after

before.PNG

after.PNG

Share this post


Link to post
Share on other sites
water

Fine :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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

×