Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default VBA to delete link to inserted image

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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default VBA to delete link to inserted image

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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default VBA to delete link to inserted image

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


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 30
Default VBA to delete link to inserted image

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




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default VBA to delete link to inserted image


"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


  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default VBA to delete link to inserted image

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do i select an inserted image. SpeeD Excel Programming 1 February 9th 09 04:32 PM
How do i select an inserted image. SpeeD Excel Programming 6 February 9th 09 01:07 PM
How do i select an inserted image. SpeeD Excel Programming 1 February 9th 09 12:40 PM
How do i select an inserted image. SpeeD Excel Programming 1 February 9th 09 12:40 PM
How do I see a .ESP image (logo) that is inserted in a worksheet? SandiVH Excel Discussion (Misc queries) 1 November 12th 05 04:13 PM


All times are GMT +1. The time now is 08:34 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"