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

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

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

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

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

"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

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