aiter

Delete shapes in range

15 posts in this topic

#1 ·  Posted (edited)

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



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 (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - 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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

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
$oShapeRange = $oExcel.Selection.ShapeRange
For $oShape In $oShapeRange
    $oShape.Delete
Next

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - 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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

 

"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

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 (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - 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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

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 (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - 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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#10 ·  Posted

Delete them.

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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

#12 ·  Posted

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

#13 ·  Posted

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 (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - 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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#14 ·  Posted

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

#15 ·  Posted

Fine :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - 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
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