Prev: run report on company intranet and import to excel
Next: DYNAMIC RANGE FOR PIVOT TABLE
From: Liz on 28 Mar 2010 16:37
Hi,
I have seen how you can delete all oleobjects from an active sheet. But I
cannot figure out how to delete an oleobject (embedded file object) from only
the current row or active cell.

For instance, in my worksheet, most rows in columns c have embedded file
objects. I just want a macro to delete the current row's embedded file
object, not all objects in the active sheet.

Is this possible?

Thanks so much,
Liz
From: Chip Pearson on 28 Mar 2010 17:52
Try something like the following:

Dim OleObj As OLEObject
For Each OleObj In Worksheets("Sheet1").OLEObjects
If OleObj.TopLeftCell.Row = ActiveCell.Row Then
OleObj.Delete
End If
Next OleObj

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Sun, 28 Mar 2010 13:37:01 -0700, Liz
<Liz(a)discussions.microsoft.com> wrote:

>Hi,
>I have seen how you can delete all oleobjects from an active sheet. But I
>cannot figure out how to delete an oleobject (embedded file object) from only
>the current row or active cell.
>
>For instance, in my worksheet, most rows in columns c have embedded file
>objects. I just want a macro to delete the current row's embedded file
>object, not all objects in the active sheet.
>
>Is this possible?
>
>Thanks so much,
>Liz
From: OssieMac on 28 Mar 2010 17:57
Hi Liz,

Something like the following might help. You could test both Left and Top
position for an object in an individual cell.

Sub DeleteOleObjsInRow()
Dim oleOBJ As OLEObject
Dim topThisRow As Long
Dim topNextRow As Long

topThisRow = Rows(7).Top
topNextRow = Rows(8).Top

With ActiveSheet
For Each oleOBJ In .OLEObjects
With oleOBJ
If .Top >= topThisRow And .Top <= topNextRow Then
.Delete
End If
End With
Next oleOBJ
End With

End Sub

--
Regards,

OssieMac


"Liz" wrote:

> Hi,
> I have seen how you can delete all oleobjects from an active sheet. But I
> cannot figure out how to delete an oleobject (embedded file object) from only
> the current row or active cell.
>
> For instance, in my worksheet, most rows in columns c have embedded file
> objects. I just want a macro to delete the current row's embedded file
> object, not all objects in the active sheet.
>
> Is this possible?
>
> Thanks so much,
> Liz
From: OssieMac on 28 Mar 2010 18:20
Hi again Liz,

That was poor coding that I posted even though it will work because working
with the active sheet anyway. Assigning the .Top to the variables should have
been inside the With ActiveSheet then it is transportable to code that
specifies the sheet name instead of ActiveSheet.

Sub DeleteOleObjsInRow()
Dim oleOBJ As OLEObject
Dim topThisRow As Long
Dim topNextRow As Long

With ActiveSheet
topThisRow = .Rows(7).Top
topNextRow = .Rows(8).Top
For Each oleOBJ In .OLEObjects
With oleOBJ
If .Top >= topThisRow And .Top <= topNextRow Then
.Delete
End If
End With
Next oleOBJ
End With

End Sub


--
Regards,

OssieMac

From: Liz on 30 Mar 2010 23:04
Thanks so much for your help!!! I wish I had your skill levels!

"Liz" wrote:

> Hi,
> I have seen how you can delete all oleobjects from an active sheet. But I
> cannot figure out how to delete an oleobject (embedded file object) from only
> the current row or active cell.
>
> For instance, in my worksheet, most rows in columns c have embedded file
> objects. I just want a macro to delete the current row's embedded file
> object, not all objects in the active sheet.
>
> Is this possible?
>
> Thanks so much,
> Liz
 | 
Pages: 1
Prev: run report on company intranet and import to excel
Next: DYNAMIC RANGE FOR PIVOT TABLE