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

Groups > microsoft.public.excel.programming > #109400

Re: VBA to delete link to inserted image

Show all headers | View raw

"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 

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web