Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109395 > unrolled thread

VBA to delete link to inserted image

Back to article view | Back to microsoft.public.excel.programming

Contents

  VBA to delete link to inserted image Steve <Steven@pctamers.eu> - 2016-10-10 01:28 -0700
    Re: VBA to delete link to inserted image Claus Busch <claus_busch@t-online.de> - 2016-10-10 10:43 +0200
    Re: VBA to delete link to inserted image isabelle <i@v.invalid> - 2016-10-10 04:49 -0400
      Re: VBA to delete link to inserted image Steve <Steven@pctamers.eu> - 2016-10-10 02:12 -0700
      Re: VBA to delete link to inserted image Steven@pctamers.eu - 2016-10-10 02:19 -0700
    Re: VBA to delete link to inserted image "Peter T" <askformy@gmail.com> - 2016-10-10 11:55 +0100
      Re: VBA to delete link to inserted image Steve <Steven@pctamers.eu> - 2016-10-10 12:57 -0700

#109395 — VBA to delete link to inserted image

I have a worksheet which contains a few hundred pictures which have been inserted with "Link to file." I want to keep the pictures but NOT have them linked. So potentially have vba that copies each picture, then pastes it without the link . . .  but I'm sure there's a better way.

Can anyone help please?

Steve

[toc] | [next] | [standalone]

#109396

H Steve,

Am Mon, 10 Oct 2016 01:28:09 -0700 (PDT) schrieb Steve:

> I have a worksheet which contains a few hundred pictures which have been inserted with "Link to file." I want to keep the pictures but NOT have them linked. So potentially have vba that copies each picture, then pastes it without the link . . .  but I'm sure there's a better way.

try:

Sub Test()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
	'Modify the shape name
    If Left(shp.Name, 7) = "Picture" Then
        shp.Hyperlink.Delete
    End If
Next
End Sub


Regards
Claus B.
-- 
Windows10
Office 2016

[toc] | [prev] | [next] | [standalone]

#109397

hi Steve,

Sub Macro1()
On Error Resume Next ' for those who have no link
For Each sh In ActiveSheet.Shapes
     sh.Hyperlink.Delete
Next
End Sub

isabelle

Le 2016-10-10 à 04:28, Steve a écrit :
> I have a worksheet which contains a few hundred pictures which have been
> inserted with "Link to file." I want to keep the pictures but NOT have them
> linked. So potentially have vba that copies each picture, then pastes it
> without the link . . .  but I'm sure there's a better way.
>
> Can anyone help please?
>
> Steve
>

[toc] | [prev] | [next] | [standalone]

#109398

Hi Isabelle

thanks for the quick response. I was wondering if this will also work if the link is to an inserted object, as these aren't hyperlinks (I don't think they are!?)? 

Steve

On Monday, 10 October 2016 09:49:41 UTC+1, isabelle  wrote:
> hi Steve,
> 
> Sub Macro1()
> On Error Resume Next ' for those who have no link
> For Each sh In ActiveSheet.Shapes
>      sh.Hyperlink.Delete
> Next
> End Sub
> 
> isabelle
> 
> Le 2016-10-10 à 04:28, Steve a écrit :
> > I have a worksheet which contains a few hundred pictures which have been
> > inserted with "Link to file." I want to keep the pictures but NOT have them
> > linked. So potentially have vba that copies each picture, then pastes it
> > without the link . . .  but I'm sure there's a better way.
> >
> > Can anyone help please?
> >
> > Steve
> >

[toc] | [prev] | [next] | [standalone]

#109399

Hi Isabelle 

thanks for the quick response. I was wondering if this will also work if the link is to an inserted object, as the one's I'm dealing with aren't hyperlinks (I don't think they are, at this precise moment I don't have access to them!?)? 


Steve 


On Monday, 10 October 2016 09:49:41 UTC+1, isabelle  wrote:
> hi Steve,
> 
> Sub Macro1()
> On Error Resume Next ' for those who have no link
> For Each sh In ActiveSheet.Shapes
>      sh.Hyperlink.Delete
> Next
> End Sub
> 
> isabelle
> 
> Le 2016-10-10 à 04:28, Steve a écrit :
> > I have a worksheet which contains a few hundred pictures which have been
> > inserted with "Link to file." I want to keep the pictures but NOT have them
> > linked. So potentially have vba that copies each picture, then pastes it
> > without the link . . .  but I'm sure there's a better way.
> >
> > Can anyone help please?
> >
> > Steve
> >

[toc] | [prev] | [next] | [standalone]

#109400

"Steve" <Steven@pctamers.eu> wrote in message

I have a worksheet which contains a few hundred pictures which have been 
inserted with "Link to file." I want to keep the pictures but NOT have them 
linked. So potentially have vba that copies each picture, then pastes it 
without the link . . .  but I'm sure there's a better way.

=======================

Sub test()
Dim pos As Long
Dim s As String
Dim ole As Object
Dim pic As Picture
Dim ws As Worksheet

    Set ws = ActiveSheet
    On Error Resume Next

    For Each ole In ActiveSheet.OLEObjects
        s = ole.SourceName
        If Err Then
            Err.Clear
        Else
            pos = InStr(1, s, "Package|")
            If pos Then
                s = Replace(Mid$(s, 9, Len(s) - 9), "!", "")
                Set pic = ws.Pictures.Insert(s)
                pic.Left = ole.TopLeftCell.Offset(, 3).Left
                pic.Top = ole.TopLeftCell.Top
                If Err = 0 Then
                 '   ole.Delete
                End If
            End If
            s = ""
        End If
    Next

End Sub


Only lightly tested, if it works adapt to suit, not least with more robust 
error handling and positioning of the new pictures

Peter T 

[toc] | [prev] | [next] | [standalone]

#109404

Hi Peter

thanks for the response. I'm going to test this when I get to the actual s/sheet (in the office) later this wekk.

Cheers!

Steve


On Monday, 10 October 2016 11:54:08 UTC+1, Peter T  wrote:
> "Steve" < wrote in message
> 
> I have a worksheet which contains a few hundred pictures which have been 
> inserted with "Link to file." I want to keep the pictures but NOT have them 
> linked. So potentially have vba that copies each picture, then pastes it 
> without the link . . .  but I'm sure there's a better way.
> 
> =======================
> 
> Sub test()
> Dim pos As Long
> Dim s As String
> Dim ole As Object
> Dim pic As Picture
> Dim ws As Worksheet
> 
>     Set ws = ActiveSheet
>     On Error Resume Next
> 
>     For Each ole In ActiveSheet.OLEObjects
>         s = ole.SourceName
>         If Err Then
>             Err.Clear
>         Else
>             pos = InStr(1, s, "Package|")
>             If pos Then
>                 s = Replace(Mid$(s, 9, Len(s) - 9), "!", "")
>                 Set pic = ws.Pictures.Insert(s)
>                 pic.Left = ole.TopLeftCell.Offset(, 3).Left
>                 pic.Top = ole.TopLeftCell.Top
>                 If Err = 0 Then
>                  '   ole.Delete
>                 End If
>             End If
>             s = ""
>         End If
>     Next
> 
> End Sub
> 
> 
> Only lightly tested, if it works adapt to suit, not least with more robust 
> error handling and positioning of the new pictures
> 
> Peter T

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web