Help with importing pictures from hyperlinks

[forxigan]

I have an excel table which contains a list of products.
One of the columns contains a list of hyperlinks to each product's picture,
on a certain website.
I need a solution to get/import the actual pictures from the website to my
table, according to each product's hyperlink(i can translate each hyperlink
into a cell with the web adress of the picture, if it helps).

Thanks in advance.

 

[Joel]

Sub test()

For Each cell In Columns("C")
PictName = cell.Hyperlinks.Item(1).Name
If PictName <> "" Then
ActiveSheet.Pictures.Insert Filename:=PictName
End If
Next cell

End Sub

 

[Joel]

Sub test()

For Each cell In Columns("C")
PictName = cell.Hyperlinks.Item(1).Name
If PictName <> "" Then
ActiveSheet.Pictures.Insert Filename:=PictName
End If
Next cell

End Sub

 

[Ron Coderre]

If you'd consider putting the hyperlinked pictures in the cells' comments,
then putting the mouse pointer over the cell would display the associated
picture.

Here's how:

First, add the below procedures to a General Module
(Watch out for text wrap issues)

To run the macros....
1) Select the range of hyperlink cells that reference pictures
2) Run the "ConvertLinksToCommentPics" macro
<tools><macro><macros>.....Select: ConvertLinksToCommentPics...Click: Run

The hyperlinks will be removed and the linked pictures will be inserted into
the cells comments.


Sub ConvertLinksToCommentPics()
Dim cCell As Range
Dim rngSelection As Range
Dim strHLink As String
Dim cComment As Comment

Dim iNewHgt As Integer
Dim iNewWidth As Integer

For Each cCell In Selection
If cCell.Hyperlinks.Count > 0 Then
'The cell contains a hyperlink
With cCell
'Store the hyperlink target
strHLink = .Hyperlinks(1).Address

If strHLink <> "" Then
.Hyperlinks(1).Delete

'If the cell doesn not contain a comment create one
Set cComment = .Comment
If cComment Is Nothing Then
Set cComment = .AddComment(Text:="")
End If

'Build a temporary picture shape to read dimensions from
'then delete the shape containing the picture
InsertPicFromFile _
strFileLoc:=strHLink, _
rDestCells:=[A1], _
blnFitInDestHeight:=False, _
strPicName:="TempPic"

With ActiveSheet.Shapes("TempPic")
iNewHgt = .Height
iNewWidth = .Width
End With

ActiveSheet.Shapes("TempPic").Delete

'Alter the comment to use the picture as the Fill
'and size the shape to the original picture size
With cComment.Shape
.Fill.UserPicture PictureFile:=strHLink
.LockAspectRatio = msoFalse
.Height = iNewHgt
.Width = iNewWidth
End With
End If
End With
End If
Next cCell
End Sub

'******************************
'* InserPicFromFile *
'* Programmer: Ron Coderre *
'* Last Update: 20-SEP-2007 *
'******************************
Sub InsertPicFromFile( _
strFileLoc As String, _
rDestCells As Range, _
blnFitInDestHeight As Boolean, _
strPicName As String)

Dim oNewPic As Shape
Dim shtWS As Worksheet

Set shtWS = rDestCells.Parent

On Error Resume Next
'Delete the named picture (if it already exists)
shtWS.Shapes(strPicName).Delete

On Error Resume Next
With rDestCells
'Create the new picture
'(arbitrarily sized as a square that is the height of the rDestCells)
Set oNewPic = shtWS.Shapes.AddPicture( _
Filename:=strFileLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left + 1, Top:=.Top + 1, _
Width:=.Height - 1, Height:=.Height - 1)

'Maintain original aspect ratio and set to full size
oNewPic.LockAspectRatio = msoTrue
oNewPic.ScaleHeight Factor:=1, RelativeToOriginalSize:=msoTrue
oNewPic.ScaleWidth Factor:=1, RelativeToOriginalSize:=msoTrue

If blnFitInDestHeight = True Then
'Resize the picture to fit in the destination cells
oNewPic.Height = .Height - 1
End If

'Assign the desired name to the picture
oNewPic.Name = strPicName
End With 'rCellDest
End Sub


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[forxigan]

hi,

First of all thanks for the quick response.
This solution could definitely work for me, tho, seeing all the product pics
at once could have some advantages as well.

The problem is, i'm not much familiar with VB and i'm getting an error
window after running the macro:
"Run-time error '-2147024809 (800070057)':
the item with specified name wasn't found."

the line which highlights in the debugger is: With
ActiveSheet.Shapes("TempPic")

Something does happen tho: the hyperlink of the first cell out of several
selected is being converted to text(hyperlink name) and comment is being
added to the cell, but it's blank.

If you'd consider putting the hyperlinked pictures in the cells' comments,
then putting the mouse pointer over the cell would display the associated
picture.

Here's how:

First, add the below procedures to a General Module
(Watch out for text wrap issues)

To run the macros....
1) Select the range of hyperlink cells that reference pictures
2) Run the "ConvertLinksToCommentPics" macro
<tools><macro><macros>.....Select: ConvertLinksToCommentPics...Click: Run

The hyperlinks will be removed and the linked pictures will be inserted into
the cells comments.


Sub ConvertLinksToCommentPics()
Dim cCell As Range
Dim rngSelection As Range
Dim strHLink As String
Dim cComment As Comment

Dim iNewHgt As Integer
Dim iNewWidth As Integer

For Each cCell In Selection
If cCell.Hyperlinks.Count > 0 Then
'The cell contains a hyperlink
With cCell
'Store the hyperlink target
strHLink = .Hyperlinks(1).Address

If strHLink <> "" Then
.Hyperlinks(1).Delete

'If the cell doesn not contain a comment create one
Set cComment = .Comment
If cComment Is Nothing Then
Set cComment = .AddComment(Text:="")
End If

'Build a temporary picture shape to read dimensions from
'then delete the shape containing the picture
InsertPicFromFile _
strFileLoc:=strHLink, _
rDestCells:=[A1], _
blnFitInDestHeight:=False, _
strPicName:="TempPic"

With ActiveSheet.Shapes("TempPic")
iNewHgt = .Height
iNewWidth = .Width
End With

ActiveSheet.Shapes("TempPic").Delete

'Alter the comment to use the picture as the Fill
'and size the shape to the original picture size
With cComment.Shape
.Fill.UserPicture PictureFile:=strHLink
.LockAspectRatio = msoFalse
.Height = iNewHgt
.Width = iNewWidth
End With
End If
End With
End If
Next cCell
End Sub

'******************************
'* InserPicFromFile *
'* Programmer: Ron Coderre *
'* Last Update: 20-SEP-2007 *
'******************************
Sub InsertPicFromFile( _
strFileLoc As String, _
rDestCells As Range, _
blnFitInDestHeight As Boolean, _
strPicName As String)

Dim oNewPic As Shape
Dim shtWS As Worksheet

Set shtWS = rDestCells.Parent

On Error Resume Next
'Delete the named picture (if it already exists)
shtWS.Shapes(strPicName).Delete

On Error Resume Next
With rDestCells
'Create the new picture
'(arbitrarily sized as a square that is the height of the rDestCells)
Set oNewPic = shtWS.Shapes.AddPicture( _
Filename:=strFileLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left + 1, Top:=.Top + 1, _
Width:=.Height - 1, Height:=.Height - 1)

'Maintain original aspect ratio and set to full size
oNewPic.LockAspectRatio = msoTrue
oNewPic.ScaleHeight Factor:=1, RelativeToOriginalSize:=msoTrue
oNewPic.ScaleWidth Factor:=1, RelativeToOriginalSize:=msoTrue

If blnFitInDestHeight = True Then
'Resize the picture to fit in the destination cells
oNewPic.Height = .Height - 1
End If

'Assign the desired name to the picture
oNewPic.Name = strPicName
End With 'rCellDest
End Sub


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

I have an excel table which contains a list of products.
One of the columns contains a list of hyperlinks to each product's
picture,
on a certain website.
I need a solution to get/import the actual pictures from the website to my
table, according to each product's hyperlink(i can translate each
hyperlink
into a cell with the web adress of the picture, if it helps).

Thanks in advance.

 

[forxigan]

hi and thanks for the response.

I'm not much familiar with VB and i'm getting and error while running the
macro: "Run-time error '1004' - Insert method of Picture class failed"
the highlighted line in the debugger is:
ActiveSheet.Pictures.Insert Filename:=PictName

Could you please suggest what i'm doing wrong here?
Thanks in advance.

 

[Ron Coderre]

Hmmmm.....Using an empy workbook, I created some hyperlinks and followed the
instructions I posted. The macro ran without incident and the pictures were
displayed in comment boxes.

I'm guessing the issue lies with the links.
Are they created using <insert><hyperlink>?
What kinds of pictures do they link to? (jpeg, bmp, gif, something else)

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

hi,

First of all thanks for the quick response.
This solution could definitely work for me, tho, seeing all the product
pics
at once could have some advantages as well.

The problem is, i'm not much familiar with VB and i'm getting an error
window after running the macro:
"Run-time error '-2147024809 (800070057)':
the item with specified name wasn't found."

the line which highlights in the debugger is: With
ActiveSheet.Shapes("TempPic")

Something does happen tho: the hyperlink of the first cell out of several
selected is being converted to text(hyperlink name) and comment is being
added to the cell, but it's blank.

If you'd consider putting the hyperlinked pictures in the cells'
comments,
then putting the mouse pointer over the cell would display the associated
picture.

Here's how:

First, add the below procedures to a General Module
(Watch out for text wrap issues)

To run the macros....
1) Select the range of hyperlink cells that reference pictures
2) Run the "ConvertLinksToCommentPics" macro
<tools><macro><macros>.....Select: ConvertLinksToCommentPics...Click:
Run

The hyperlinks will be removed and the linked pictures will be inserted
into
the cells comments.


Sub ConvertLinksToCommentPics()
Dim cCell As Range
Dim rngSelection As Range
Dim strHLink As String
Dim cComment As Comment

Dim iNewHgt As Integer
Dim iNewWidth As Integer

For Each cCell In Selection
If cCell.Hyperlinks.Count > 0 Then
'The cell contains a hyperlink
With cCell
'Store the hyperlink target
strHLink = .Hyperlinks(1).Address

If strHLink <> "" Then
.Hyperlinks(1).Delete

'If the cell doesn not contain a comment create one
Set cComment = .Comment
If cComment Is Nothing Then
Set cComment = .AddComment(Text:="")
End If

'Build a temporary picture shape to read dimensions from
'then delete the shape containing the picture
InsertPicFromFile _
strFileLoc:=strHLink, _
rDestCells:=[A1], _
blnFitInDestHeight:=False, _
strPicName:="TempPic"

With ActiveSheet.Shapes("TempPic")
iNewHgt = .Height
iNewWidth = .Width
End With

ActiveSheet.Shapes("TempPic").Delete

'Alter the comment to use the picture as the Fill
'and size the shape to the original picture size
With cComment.Shape
.Fill.UserPicture PictureFile:=strHLink
.LockAspectRatio = msoFalse
.Height = iNewHgt
.Width = iNewWidth
End With
End If
End With
End If
Next cCell
End Sub

'******************************
'* InserPicFromFile *
'* Programmer: Ron Coderre *
'* Last Update: 20-SEP-2007 *
'******************************
Sub InsertPicFromFile( _
strFileLoc As String, _
rDestCells As Range, _
blnFitInDestHeight As Boolean, _
strPicName As String)

Dim oNewPic As Shape
Dim shtWS As Worksheet

Set shtWS = rDestCells.Parent

On Error Resume Next
'Delete the named picture (if it already exists)
shtWS.Shapes(strPicName).Delete

On Error Resume Next
With rDestCells
'Create the new picture
'(arbitrarily sized as a square that is the height of the
rDestCells)
Set oNewPic = shtWS.Shapes.AddPicture( _
Filename:=strFileLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left + 1, Top:=.Top + 1, _
Width:=.Height - 1, Height:=.Height - 1)

'Maintain original aspect ratio and set to full size
oNewPic.LockAspectRatio = msoTrue
oNewPic.ScaleHeight Factor:=1, RelativeToOriginalSize:=msoTrue
oNewPic.ScaleWidth Factor:=1, RelativeToOriginalSize:=msoTrue

If blnFitInDestHeight = True Then
'Resize the picture to fit in the destination cells
oNewPic.Height = .Height - 1
End If

'Assign the desired name to the picture
oNewPic.Name = strPicName
End With 'rCellDest
End Sub


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

I have an excel table which contains a list of products.
One of the columns contains a list of hyperlinks to each product's
picture,
on a certain website.
I need a solution to get/import the actual pictures from the website to
my
table, according to each product's hyperlink(i can translate each
hyperlink
into a cell with the web adress of the picture, if it helps).

Thanks in advance.

 

[forxigan]

hi,

You were absolutely right - the problem were in the hyperlinks.
I tried to create a few links myself and the macro worked perfectly, thx.
The original workbook wasn't made by me, so no idea how the hyperlinks were
created there.

Is there an option to auto rebuild them somehow, as we're talking about
thousands of links here?

And another question: could you think of the way to show all the pics in the
workbook and not as cell comments, because i'm not sure the comments solution
will 100% work for me, tho it's still very helpful, thanks.
I know you can't attach a picture into a cell, but they all have the same
width, so i could simply set rows size accordingly, so they would fit in.

Thanks in advance.

Hmmmm.....Using an empy workbook, I created some hyperlinks and followed the
instructions I posted. The macro ran without incident and the pictures were
displayed in comment boxes.

I'm guessing the issue lies with the links.
Are they created using <insert><hyperlink>?
What kinds of pictures do they link to? (jpeg, bmp, gif, something else)

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

hi,

First of all thanks for the quick response.
This solution could definitely work for me, tho, seeing all the product
pics
at once could have some advantages as well.

The problem is, i'm not much familiar with VB and i'm getting an error
window after running the macro:
"Run-time error '-2147024809 (800070057)':
the item with specified name wasn't found."

the line which highlights in the debugger is: With
ActiveSheet.Shapes("TempPic")

Something does happen tho: the hyperlink of the first cell out of several
selected is being converted to text(hyperlink name) and comment is being
added to the cell, but it's blank.

If you'd consider putting the hyperlinked pictures in the cells'
comments,
then putting the mouse pointer over the cell would display the associated
picture.

Here's how:

First, add the below procedures to a General Module
(Watch out for text wrap issues)

To run the macros....
1) Select the range of hyperlink cells that reference pictures
2) Run the "ConvertLinksToCommentPics" macro
<tools><macro><macros>.....Select: ConvertLinksToCommentPics...Click:
Run

The hyperlinks will be removed and the linked pictures will be inserted
into
the cells comments.


Sub ConvertLinksToCommentPics()
Dim cCell As Range
Dim rngSelection As Range
Dim strHLink As String
Dim cComment As Comment

Dim iNewHgt As Integer
Dim iNewWidth As Integer

For Each cCell In Selection
If cCell.Hyperlinks.Count > 0 Then
'The cell contains a hyperlink
With cCell
'Store the hyperlink target
strHLink = .Hyperlinks(1).Address

If strHLink <> "" Then
.Hyperlinks(1).Delete

'If the cell doesn not contain a comment create one
Set cComment = .Comment
If cComment Is Nothing Then
Set cComment = .AddComment(Text:="")
End If

'Build a temporary picture shape to read dimensions from
'then delete the shape containing the picture
InsertPicFromFile _
strFileLoc:=strHLink, _
rDestCells:=[A1], _
blnFitInDestHeight:=False, _
strPicName:="TempPic"

With ActiveSheet.Shapes("TempPic")
iNewHgt = .Height
iNewWidth = .Width
End With

ActiveSheet.Shapes("TempPic").Delete

'Alter the comment to use the picture as the Fill
'and size the shape to the original picture size
With cComment.Shape
.Fill.UserPicture PictureFile:=strHLink
.LockAspectRatio = msoFalse
.Height = iNewHgt
.Width = iNewWidth
End With
End If
End With
End If
Next cCell
End Sub

'******************************
'* InserPicFromFile *
'* Programmer: Ron Coderre *
'* Last Update: 20-SEP-2007 *
'******************************
Sub InsertPicFromFile( _
strFileLoc As String, _
rDestCells As Range, _
blnFitInDestHeight As Boolean, _
strPicName As String)

Dim oNewPic As Shape
Dim shtWS As Worksheet

Set shtWS = rDestCells.Parent

On Error Resume Next
'Delete the named picture (if it already exists)
shtWS.Shapes(strPicName).Delete

On Error Resume Next
With rDestCells
'Create the new picture
'(arbitrarily sized as a square that is the height of the
rDestCells)
Set oNewPic = shtWS.Shapes.AddPicture( _
Filename:=strFileLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left + 1, Top:=.Top + 1, _
Width:=.Height - 1, Height:=.Height - 1)

'Maintain original aspect ratio and set to full size
oNewPic.LockAspectRatio = msoTrue
oNewPic.ScaleHeight Factor:=1, RelativeToOriginalSize:=msoTrue
oNewPic.ScaleWidth Factor:=1, RelativeToOriginalSize:=msoTrue

If blnFitInDestHeight = True Then
'Resize the picture to fit in the destination cells
oNewPic.Height = .Height - 1
End If

'Assign the desired name to the picture
oNewPic.Name = strPicName
End With 'rCellDest
End Sub


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

I have an excel table which contains a list of products.
One of the columns contains a list of hyperlinks to each product's
picture,
on a certain website.
I need a solution to get/import the actual pictures from the website to
my
table, according to each product's hyperlink(i can translate each
hyperlink
into a cell with the web adress of the picture, if it helps).

Thanks in advance.

 

[forxigan]

hi,

You were absolutely right - the problem were in the hyperlinks.
I tried to create a few links myself and the macro worked perfectly, thx.
The original workbook wasn't made by me, so no idea how the hyperlinks were
created there.

Is there an option to auto rebuild them somehow, as we're talking about
thousands of links here?

And another question: could you think of the way to show all the pics in the
workbook and not as cell comments, because i'm not sure the comments solution
will 100% work for me, tho it's still very helpful, thanks.
I know you can't attach a picture into a cell, but they all have the same
width, so i could simply set rows size accordingly, so they would fit in.

Thanks in advance.

Hmmmm.....Using an empy workbook, I created some hyperlinks and followed the
instructions I posted. The macro ran without incident and the pictures were
displayed in comment boxes.

I'm guessing the issue lies with the links.
Are they created using <insert><hyperlink>?
What kinds of pictures do they link to? (jpeg, bmp, gif, something else)

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

hi,

First of all thanks for the quick response.
This solution could definitely work for me, tho, seeing all the product
pics
at once could have some advantages as well.

The problem is, i'm not much familiar with VB and i'm getting an error
window after running the macro:
"Run-time error '-2147024809 (800070057)':
the item with specified name wasn't found."

the line which highlights in the debugger is: With
ActiveSheet.Shapes("TempPic")

Something does happen tho: the hyperlink of the first cell out of several
selected is being converted to text(hyperlink name) and comment is being
added to the cell, but it's blank.

If you'd consider putting the hyperlinked pictures in the cells'
comments,
then putting the mouse pointer over the cell would display the associated
picture.

Here's how:

First, add the below procedures to a General Module
(Watch out for text wrap issues)

To run the macros....
1) Select the range of hyperlink cells that reference pictures
2) Run the "ConvertLinksToCommentPics" macro
<tools><macro><macros>.....Select: ConvertLinksToCommentPics...Click:
Run

The hyperlinks will be removed and the linked pictures will be inserted
into
the cells comments.


Sub ConvertLinksToCommentPics()
Dim cCell As Range
Dim rngSelection As Range
Dim strHLink As String
Dim cComment As Comment

Dim iNewHgt As Integer
Dim iNewWidth As Integer

For Each cCell In Selection
If cCell.Hyperlinks.Count > 0 Then
'The cell contains a hyperlink
With cCell
'Store the hyperlink target
strHLink = .Hyperlinks(1).Address

If strHLink <> "" Then
.Hyperlinks(1).Delete

'If the cell doesn not contain a comment create one
Set cComment = .Comment
If cComment Is Nothing Then
Set cComment = .AddComment(Text:="")
End If

'Build a temporary picture shape to read dimensions from
'then delete the shape containing the picture
InsertPicFromFile _
strFileLoc:=strHLink, _
rDestCells:=[A1], _
blnFitInDestHeight:=False, _
strPicName:="TempPic"

With ActiveSheet.Shapes("TempPic")
iNewHgt = .Height
iNewWidth = .Width
End With

ActiveSheet.Shapes("TempPic").Delete

'Alter the comment to use the picture as the Fill
'and size the shape to the original picture size
With cComment.Shape
.Fill.UserPicture PictureFile:=strHLink
.LockAspectRatio = msoFalse
.Height = iNewHgt
.Width = iNewWidth
End With
End If
End With
End If
Next cCell
End Sub

'******************************
'* InserPicFromFile *
'* Programmer: Ron Coderre *
'* Last Update: 20-SEP-2007 *
'******************************
Sub InsertPicFromFile( _
strFileLoc As String, _
rDestCells As Range, _
blnFitInDestHeight As Boolean, _
strPicName As String)

Dim oNewPic As Shape
Dim shtWS As Worksheet

Set shtWS = rDestCells.Parent

On Error Resume Next
'Delete the named picture (if it already exists)
shtWS.Shapes(strPicName).Delete

On Error Resume Next
With rDestCells
'Create the new picture
'(arbitrarily sized as a square that is the height of the
rDestCells)
Set oNewPic = shtWS.Shapes.AddPicture( _
Filename:=strFileLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left + 1, Top:=.Top + 1, _
Width:=.Height - 1, Height:=.Height - 1)

'Maintain original aspect ratio and set to full size
oNewPic.LockAspectRatio = msoTrue
oNewPic.ScaleHeight Factor:=1, RelativeToOriginalSize:=msoTrue
oNewPic.ScaleWidth Factor:=1, RelativeToOriginalSize:=msoTrue

If blnFitInDestHeight = True Then
'Resize the picture to fit in the destination cells
oNewPic.Height = .Height - 1
End If

'Assign the desired name to the picture
oNewPic.Name = strPicName
End With 'rCellDest
End Sub


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

I have an excel table which contains a list of products.
One of the columns contains a list of hyperlinks to each product's
picture,
on a certain website.
I need a solution to get/import the actual pictures from the website to
my
table, according to each product's hyperlink(i can translate each
hyperlink
into a cell with the web adress of the picture, if it helps).

Thanks in advance.

 

[Ron Coderre]

Rebuild them?...Maybe.

Can you post what you know about them?
Is it a Hyperlink formula? and not a Hyperlink?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

hi,

You were absolutely right - the problem were in the hyperlinks.
I tried to create a few links myself and the macro worked perfectly, thx.
The original workbook wasn't made by me, so no idea how the hyperlinks
were
created there.

Is there an option to auto rebuild them somehow, as we're talking about
thousands of links here?

And another question: could you think of the way to show all the pics in
the
workbook and not as cell comments, because i'm not sure the comments
solution
will 100% work for me, tho it's still very helpful, thanks.
I know you can't attach a picture into a cell, but they all have the same
width, so i could simply set rows size accordingly, so they would fit in.

Thanks in advance.

Hmmmm.....Using an empy workbook, I created some hyperlinks and followed
the
instructions I posted. The macro ran without incident and the pictures
were
displayed in comment boxes.

I'm guessing the issue lies with the links.
Are they created using <insert><hyperlink>?
What kinds of pictures do they link to? (jpeg, bmp, gif, something else)

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

hi,

First of all thanks for the quick response.
This solution could definitely work for me, tho, seeing all the product
pics
at once could have some advantages as well.

The problem is, i'm not much familiar with VB and i'm getting an error
window after running the macro:
"Run-time error '-2147024809 (800070057)':
the item with specified name wasn't found."

the line which highlights in the debugger is: With
ActiveSheet.Shapes("TempPic")

Something does happen tho: the hyperlink of the first cell out of
several
selected is being converted to text(hyperlink name) and comment is
being
added to the cell, but it's blank.


:

If you'd consider putting the hyperlinked pictures in the cells'
comments,
then putting the mouse pointer over the cell would display the
associated
picture.

Here's how:

First, add the below procedures to a General Module
(Watch out for text wrap issues)

To run the macros....
1) Select the range of hyperlink cells that reference pictures
2) Run the "ConvertLinksToCommentPics" macro
<tools><macro><macros>.....Select:
ConvertLinksToCommentPics...Click:
Run

The hyperlinks will be removed and the linked pictures will be
inserted
into
the cells comments.


Sub ConvertLinksToCommentPics()
Dim cCell As Range
Dim rngSelection As Range
Dim strHLink As String
Dim cComment As Comment

Dim iNewHgt As Integer
Dim iNewWidth As Integer

For Each cCell In Selection
If cCell.Hyperlinks.Count > 0 Then
'The cell contains a hyperlink
With cCell
'Store the hyperlink target
strHLink = .Hyperlinks(1).Address

If strHLink <> "" Then
.Hyperlinks(1).Delete

'If the cell doesn not contain a comment create one
Set cComment = .Comment
If cComment Is Nothing Then
Set cComment = .AddComment(Text:="")
End If

'Build a temporary picture shape to read dimensions
from
'then delete the shape containing the picture
InsertPicFromFile _
strFileLoc:=strHLink, _
rDestCells:=[A1], _
blnFitInDestHeight:=False, _
strPicName:="TempPic"

With ActiveSheet.Shapes("TempPic")
iNewHgt = .Height
iNewWidth = .Width
End With

ActiveSheet.Shapes("TempPic").Delete

'Alter the comment to use the picture as the Fill
'and size the shape to the original picture size
With cComment.Shape
.Fill.UserPicture PictureFile:=strHLink
.LockAspectRatio = msoFalse
.Height = iNewHgt
.Width = iNewWidth
End With
End If
End With
End If
Next cCell
End Sub

'******************************
'* InserPicFromFile *
'* Programmer: Ron Coderre *
'* Last Update: 20-SEP-2007 *
'******************************
Sub InsertPicFromFile( _
strFileLoc As String, _
rDestCells As Range, _
blnFitInDestHeight As Boolean, _
strPicName As String)

Dim oNewPic As Shape
Dim shtWS As Worksheet

Set shtWS = rDestCells.Parent

On Error Resume Next
'Delete the named picture (if it already exists)
shtWS.Shapes(strPicName).Delete

On Error Resume Next
With rDestCells
'Create the new picture
'(arbitrarily sized as a square that is the height of the
rDestCells)
Set oNewPic = shtWS.Shapes.AddPicture( _
Filename:=strFileLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left + 1, Top:=.Top + 1, _
Width:=.Height - 1, Height:=.Height - 1)

'Maintain original aspect ratio and set to full size
oNewPic.LockAspectRatio = msoTrue
oNewPic.ScaleHeight Factor:=1, RelativeToOriginalSize:=msoTrue
oNewPic.ScaleWidth Factor:=1, RelativeToOriginalSize:=msoTrue

If blnFitInDestHeight = True Then
'Resize the picture to fit in the destination cells
oNewPic.Height = .Height - 1
End If

'Assign the desired name to the picture
oNewPic.Name = strPicName
End With 'rCellDest
End Sub


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

I have an excel table which contains a list of products.
One of the columns contains a list of hyperlinks to each product's
picture,
on a certain website.
I need a solution to get/import the actual pictures from the website
to
my
table, according to each product's hyperlink(i can translate each
hyperlink
into a cell with the web adress of the picture, if it helps).

Thanks in advance.

 

[forxigan]

They look like usual hyperlinks to me, there is a hyperlink name and the
actual web adress, nothing more.
Thing is, i need to keep the original hyperlink's name as well, as it states
the product catalog number.

Rebuild them?...Maybe.

Can you post what you know about them?
Is it a Hyperlink formula? and not a Hyperlink?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

hi,

You were absolutely right - the problem were in the hyperlinks.
I tried to create a few links myself and the macro worked perfectly, thx.
The original workbook wasn't made by me, so no idea how the hyperlinks
were
created there.

Is there an option to auto rebuild them somehow, as we're talking about
thousands of links here?

And another question: could you think of the way to show all the pics in
the
workbook and not as cell comments, because i'm not sure the comments
solution
will 100% work for me, tho it's still very helpful, thanks.
I know you can't attach a picture into a cell, but they all have the same
width, so i could simply set rows size accordingly, so they would fit in.

Thanks in advance.

Hmmmm.....Using an empy workbook, I created some hyperlinks and followed
the
instructions I posted. The macro ran without incident and the pictures
were
displayed in comment boxes.

I'm guessing the issue lies with the links.
Are they created using <insert><hyperlink>?
What kinds of pictures do they link to? (jpeg, bmp, gif, something else)

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


hi,

First of all thanks for the quick response.
This solution could definitely work for me, tho, seeing all the product
pics
at once could have some advantages as well.

The problem is, i'm not much familiar with VB and i'm getting an error
window after running the macro:
"Run-time error '-2147024809 (800070057)':
the item with specified name wasn't found."

the line which highlights in the debugger is: With
ActiveSheet.Shapes("TempPic")

Something does happen tho: the hyperlink of the first cell out of
several
selected is being converted to text(hyperlink name) and comment is
being
added to the cell, but it's blank.


:

If you'd consider putting the hyperlinked pictures in the cells'
comments,
then putting the mouse pointer over the cell would display the
associated
picture.

Here's how:

First, add the below procedures to a General Module
(Watch out for text wrap issues)

To run the macros....
1) Select the range of hyperlink cells that reference pictures
2) Run the "ConvertLinksToCommentPics" macro
<tools><macro><macros>.....Select:
ConvertLinksToCommentPics...Click:
Run

The hyperlinks will be removed and the linked pictures will be
inserted
into
the cells comments.


Sub ConvertLinksToCommentPics()
Dim cCell As Range
Dim rngSelection As Range
Dim strHLink As String
Dim cComment As Comment

Dim iNewHgt As Integer
Dim iNewWidth As Integer

For Each cCell In Selection
If cCell.Hyperlinks.Count > 0 Then
'The cell contains a hyperlink
With cCell
'Store the hyperlink target
strHLink = .Hyperlinks(1).Address

If strHLink <> "" Then
.Hyperlinks(1).Delete

'If the cell doesn not contain a comment create one
Set cComment = .Comment
If cComment Is Nothing Then
Set cComment = .AddComment(Text:="")
End If

'Build a temporary picture shape to read dimensions
from
'then delete the shape containing the picture
InsertPicFromFile _
strFileLoc:=strHLink, _
rDestCells:=[A1], _
blnFitInDestHeight:=False, _
strPicName:="TempPic"

With ActiveSheet.Shapes("TempPic")
iNewHgt = .Height
iNewWidth = .Width
End With

ActiveSheet.Shapes("TempPic").Delete

'Alter the comment to use the picture as the Fill
'and size the shape to the original picture size
With cComment.Shape
.Fill.UserPicture PictureFile:=strHLink
.LockAspectRatio = msoFalse
.Height = iNewHgt
.Width = iNewWidth
End With
End If
End With
End If
Next cCell
End Sub

'******************************
'* InserPicFromFile *
'* Programmer: Ron Coderre *
'* Last Update: 20-SEP-2007 *
'******************************
Sub InsertPicFromFile( _
strFileLoc As String, _
rDestCells As Range, _
blnFitInDestHeight As Boolean, _
strPicName As String)

Dim oNewPic As Shape
Dim shtWS As Worksheet

Set shtWS = rDestCells.Parent

On Error Resume Next
'Delete the named picture (if it already exists)
shtWS.Shapes(strPicName).Delete

On Error Resume Next
With rDestCells
'Create the new picture
'(arbitrarily sized as a square that is the height of the
rDestCells)
Set oNewPic = shtWS.Shapes.AddPicture( _
Filename:=strFileLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left + 1, Top:=.Top + 1, _
Width:=.Height - 1, Height:=.Height - 1)

'Maintain original aspect ratio and set to full size
oNewPic.LockAspectRatio = msoTrue
oNewPic.ScaleHeight Factor:=1, RelativeToOriginalSize:=msoTrue
oNewPic.ScaleWidth Factor:=1, RelativeToOriginalSize:=msoTrue

If blnFitInDestHeight = True Then
'Resize the picture to fit in the destination cells
oNewPic.Height = .Height - 1
End If

'Assign the desired name to the picture
oNewPic.Name = strPicName
End With 'rCellDest
End Sub


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

I have an excel table which contains a list of products.
One of the columns contains a list of hyperlinks to each product's
picture,
on a certain website.
I need a solution to get/import the actual pictures from the website
to
my
table, according to each product's hyperlink(i can translate each
hyperlink
into a cell with the web adress of the picture, if it helps).

Thanks in advance.

 

[Ron Coderre]

Perhaps an example would help.
Can you post the details of a couple of the hyperlinks
and what you want the end result to be?

Or...you could post a sample file at one of the free file sharing sites:
Some free filehosts that could be used:
http://www.freefilehosting.net/index.cfm
http://cjoint.com/index.php
http://www.savefile.com/index.php

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

They look like usual hyperlinks to me, there is a hyperlink name and the
actual web adress, nothing more.
Thing is, i need to keep the original hyperlink's name as well, as it
states
the product catalog number.

Rebuild them?...Maybe.

Can you post what you know about them?
Is it a Hyperlink formula? and not a Hyperlink?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

hi,

You were absolutely right - the problem were in the hyperlinks.
I tried to create a few links myself and the macro worked perfectly,
thx.
The original workbook wasn't made by me, so no idea how the hyperlinks
were
created there.

Is there an option to auto rebuild them somehow, as we're talking about
thousands of links here?

And another question: could you think of the way to show all the pics
in
the
workbook and not as cell comments, because i'm not sure the comments
solution
will 100% work for me, tho it's still very helpful, thanks.
I know you can't attach a picture into a cell, but they all have the
same
width, so i could simply set rows size accordingly, so they would fit
in.

Thanks in advance.

:

Hmmmm.....Using an empy workbook, I created some hyperlinks and
followed
the
instructions I posted. The macro ran without incident and the
pictures
were
displayed in comment boxes.

I'm guessing the issue lies with the links.
Are they created using <insert><hyperlink>?
What kinds of pictures do they link to? (jpeg, bmp, gif, something
else)

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


hi,

First of all thanks for the quick response.
This solution could definitely work for me, tho, seeing all the
product
pics
at once could have some advantages as well.

The problem is, i'm not much familiar with VB and i'm getting an
error
window after running the macro:
"Run-time error '-2147024809 (800070057)':
the item with specified name wasn't found."

the line which highlights in the debugger is: With
ActiveSheet.Shapes("TempPic")

Something does happen tho: the hyperlink of the first cell out of
several
selected is being converted to text(hyperlink name) and comment is
being
added to the cell, but it's blank.


:

If you'd consider putting the hyperlinked pictures in the cells'
comments,
then putting the mouse pointer over the cell would display the
associated
picture.

Here's how:

First, add the below procedures to a General Module
(Watch out for text wrap issues)

To run the macros....
1) Select the range of hyperlink cells that reference pictures
2) Run the "ConvertLinksToCommentPics" macro
<tools><macro><macros>.....Select:
ConvertLinksToCommentPics...Click:
Run

The hyperlinks will be removed and the linked pictures will be
inserted
into
the cells comments.


Sub ConvertLinksToCommentPics()
Dim cCell As Range
Dim rngSelection As Range
Dim strHLink As String
Dim cComment As Comment

Dim iNewHgt As Integer
Dim iNewWidth As Integer

For Each cCell In Selection
If cCell.Hyperlinks.Count > 0 Then
'The cell contains a hyperlink
With cCell
'Store the hyperlink target
strHLink = .Hyperlinks(1).Address

If strHLink <> "" Then
.Hyperlinks(1).Delete

'If the cell doesn not contain a comment create one
Set cComment = .Comment
If cComment Is Nothing Then
Set cComment = .AddComment(Text:="")
End If

'Build a temporary picture shape to read dimensions
from
'then delete the shape containing the picture
InsertPicFromFile _
strFileLoc:=strHLink, _
rDestCells:=[A1], _
blnFitInDestHeight:=False, _
strPicName:="TempPic"

With ActiveSheet.Shapes("TempPic")
iNewHgt = .Height
iNewWidth = .Width
End With

ActiveSheet.Shapes("TempPic").Delete

'Alter the comment to use the picture as the Fill
'and size the shape to the original picture size
With cComment.Shape
.Fill.UserPicture PictureFile:=strHLink
.LockAspectRatio = msoFalse
.Height = iNewHgt
.Width = iNewWidth
End With
End If
End With
End If
Next cCell
End Sub

'******************************
'* InserPicFromFile *
'* Programmer: Ron Coderre *
'* Last Update: 20-SEP-2007 *
'******************************
Sub InsertPicFromFile( _
strFileLoc As String, _
rDestCells As Range, _
blnFitInDestHeight As Boolean, _
strPicName As String)

Dim oNewPic As Shape
Dim shtWS As Worksheet

Set shtWS = rDestCells.Parent

On Error Resume Next
'Delete the named picture (if it already exists)
shtWS.Shapes(strPicName).Delete

On Error Resume Next
With rDestCells
'Create the new picture
'(arbitrarily sized as a square that is the height of the
rDestCells)
Set oNewPic = shtWS.Shapes.AddPicture( _
Filename:=strFileLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left + 1, Top:=.Top + 1, _
Width:=.Height - 1, Height:=.Height - 1)

'Maintain original aspect ratio and set to full size
oNewPic.LockAspectRatio = msoTrue
oNewPic.ScaleHeight Factor:=1,
RelativeToOriginalSize:=msoTrue
oNewPic.ScaleWidth Factor:=1, RelativeToOriginalSize:=msoTrue

If blnFitInDestHeight = True Then
'Resize the picture to fit in the destination cells
oNewPic.Height = .Height - 1
End If

'Assign the desired name to the picture
oNewPic.Name = strPicName
End With 'rCellDest
End Sub


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

I have an excel table which contains a list of products.
One of the columns contains a list of hyperlinks to each
product's
picture,
on a certain website.
I need a solution to get/import the actual pictures from the
website
to
my
table, according to each product's hyperlink(i can translate each
hyperlink
into a cell with the web adress of the picture, if it helps).

Thanks in advance.

 

[forxigan]

I uploaded a few pic files to http://www.freefilehosting.net, made their
hyperlink list and ran the macro.
I'm getting the same error as before on the first link already.
Thing is, their links doesn't contain an explicit filename in the end of it.
Not sure how your macro works, but it could be the reason as well, i guess.

Anyway, links are:
http://www.freefilehosting.net/show/3c8hm
http://www.freefilehosting.net/show/3c8i0
http://www.freefilehosting.net/show/3c8i1
http://www.freefilehosting.net/show/3c8i2
http://www.freefilehosting.net/show/3c8i3

thanks.

Perhaps an example would help.
Can you post the details of a couple of the hyperlinks
and what you want the end result to be?

Or...you could post a sample file at one of the free file sharing sites:
Some free filehosts that could be used:
http://www.freefilehosting.net/index.cfm
http://cjoint.com/index.php
http://www.savefile.com/index.php

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

They look like usual hyperlinks to me, there is a hyperlink name and the
actual web adress, nothing more.
Thing is, i need to keep the original hyperlink's name as well, as it
states
the product catalog number.

Rebuild them?...Maybe.

Can you post what you know about them?
Is it a Hyperlink formula? and not a Hyperlink?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

hi,

You were absolutely right - the problem were in the hyperlinks.
I tried to create a few links myself and the macro worked perfectly,
thx.
The original workbook wasn't made by me, so no idea how the hyperlinks
were
created there.

Is there an option to auto rebuild them somehow, as we're talking about
thousands of links here?

And another question: could you think of the way to show all the pics
in
the
workbook and not as cell comments, because i'm not sure the comments
solution
will 100% work for me, tho it's still very helpful, thanks.
I know you can't attach a picture into a cell, but they all have the
same
width, so i could simply set rows size accordingly, so they would fit
in.

Thanks in advance.

:

Hmmmm.....Using an empy workbook, I created some hyperlinks and
followed
the
instructions I posted. The macro ran without incident and the
pictures
were
displayed in comment boxes.

I'm guessing the issue lies with the links.
Are they created using <insert><hyperlink>?
What kinds of pictures do they link to? (jpeg, bmp, gif, something
else)

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


hi,

First of all thanks for the quick response.
This solution could definitely work for me, tho, seeing all the
product
pics
at once could have some advantages as well.

The problem is, i'm not much familiar with VB and i'm getting an
error
window after running the macro:
"Run-time error '-2147024809 (800070057)':
the item with specified name wasn't found."

the line which highlights in the debugger is: With
ActiveSheet.Shapes("TempPic")

Something does happen tho: the hyperlink of the first cell out of
several
selected is being converted to text(hyperlink name) and comment is
being
added to the cell, but it's blank.


:

If you'd consider putting the hyperlinked pictures in the cells'
comments,
then putting the mouse pointer over the cell would display the
associated
picture.

Here's how:

First, add the below procedures to a General Module
(Watch out for text wrap issues)

To run the macros....
1) Select the range of hyperlink cells that reference pictures
2) Run the "ConvertLinksToCommentPics" macro
<tools><macro><macros>.....Select:
ConvertLinksToCommentPics...Click:
Run

The hyperlinks will be removed and the linked pictures will be
inserted
into
the cells comments.


Sub ConvertLinksToCommentPics()
Dim cCell As Range
Dim rngSelection As Range
Dim strHLink As String
Dim cComment As Comment

Dim iNewHgt As Integer
Dim iNewWidth As Integer

For Each cCell In Selection
If cCell.Hyperlinks.Count > 0 Then
'The cell contains a hyperlink
With cCell
'Store the hyperlink target
strHLink = .Hyperlinks(1).Address

If strHLink <> "" Then
.Hyperlinks(1).Delete

'If the cell doesn not contain a comment create one
Set cComment = .Comment
If cComment Is Nothing Then
Set cComment = .AddComment(Text:="")
End If

'Build a temporary picture shape to read dimensions
from
'then delete the shape containing the picture
InsertPicFromFile _
strFileLoc:=strHLink, _
rDestCells:=[A1], _
blnFitInDestHeight:=False, _
strPicName:="TempPic"

With ActiveSheet.Shapes("TempPic")
iNewHgt = .Height
iNewWidth = .Width
End With

ActiveSheet.Shapes("TempPic").Delete

'Alter the comment to use the picture as the Fill
'and size the shape to the original picture size
With cComment.Shape
.Fill.UserPicture PictureFile:=strHLink
.LockAspectRatio = msoFalse
.Height = iNewHgt
.Width = iNewWidth
End With
End If
End With
End If
Next cCell
End Sub

'******************************
'* InserPicFromFile *
'* Programmer: Ron Coderre *
'* Last Update: 20-SEP-2007 *
'******************************
Sub InsertPicFromFile( _
strFileLoc As String, _
rDestCells As Range, _
blnFitInDestHeight As Boolean, _
strPicName As String)

Dim oNewPic As Shape
Dim shtWS As Worksheet

Set shtWS = rDestCells.Parent

On Error Resume Next
'Delete the named picture (if it already exists)
shtWS.Shapes(strPicName).Delete

On Error Resume Next
With rDestCells
'Create the new picture
'(arbitrarily sized as a square that is the height of the
rDestCells)
Set oNewPic = shtWS.Shapes.AddPicture( _
Filename:=strFileLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left + 1, Top:=.Top + 1, _
Width:=.Height - 1, Height:=.Height - 1)

'Maintain original aspect ratio and set to full size
oNewPic.LockAspectRatio = msoTrue
oNewPic.ScaleHeight Factor:=1,
RelativeToOriginalSize:=msoTrue
oNewPic.ScaleWidth Factor:=1, RelativeToOriginalSize:=msoTrue

If blnFitInDestHeight = True Then
'Resize the picture to fit in the destination cells
oNewPic.Height = .Height - 1
End If

'Assign the desired name to the picture
oNewPic.Name = strPicName
End With 'rCellDest
End Sub


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

I have an excel table which contains a list of products.
One of the columns contains a list of hyperlinks to each
product's
picture,
on a certain website.
I need a solution to get/import the actual pictures from the
website
to
my
table, according to each product's hyperlink(i can translate each
hyperlink
into a cell with the web adress of the picture, if it helps).

Thanks in advance.

 

[forxigan]

btw, i was searching for similar macros earlier on other sites, and stumbled
upon a few.
Maybe u could use it to understand what i mean:

1. this one inserts a single picture from a hyperlink or local file, but i'm
not sure how to change it to work on range of cells / column:

Sub GetPic()
Application.ScreenUpdating = False
For Each Shape In ActiveSheet.Shapes
If Shape.Type = msoPicture Then
Shape.Delete
End If
Next Shape
Dim PicPath As String
PicPath = Range("b1")
On Error GoTo NoPic
Range("c1").Select
ActiveSheet.Pictures.Insert(PicPath).Select
'Range("A1").Select
Application.ScreenUpdating = True
Exit Sub

NoPic:
MsgBox Prompt:="No Picture Available", _
Title:="Error Retrieving Picture", _
Buttons:=vbOKOnly
End Sub

2. this one suppose to to exactly what i need, but again, it doesn't for
some reason:

Column A is the Item
Column B is the image path
Column C, currently empty, will be the images

Sub InsertPictures()

Dim row As Long

Dim picPath As String

Dim Picture As Object



row = 1



On Error Resume Next



While Cells(row, 1) <> ""

Cells(row, 3).Select



' just guess what type of picture it is: .jpg or .gif

picPath = Cells(row, 2) & Cells(row, 1) & ".gif"

ActiveSheet.Pictures.Insert(picPath).Select

picPath = Cells(row, 2) & Cells(row, 1) & ".jpg"

ActiveSheet.Pictures.Insert(picPath).Select



Set Picture = Selection

'set cell height to picture size

Picture.Top = Picture.TopLeftCell.Top

Picture.Left = Picture.TopLeftCell.Left

Picture.TopLeftCell.EntireRow.RowHeight = Picture.Height



row = row + 1



Wend

End Sub

I uploaded a few pic files to http://www.freefilehosting.net, made their
hyperlink list and ran the macro.
I'm getting the same error as before on the first link already.
Thing is, their links doesn't contain an explicit filename in the end of it.
Not sure how your macro works, but it could be the reason as well, i guess.

Anyway, links are:
http://www.freefilehosting.net/show/3c8hm
http://www.freefilehosting.net/show/3c8i0
http://www.freefilehosting.net/show/3c8i1
http://www.freefilehosting.net/show/3c8i2
http://www.freefilehosting.net/show/3c8i3

thanks.

Perhaps an example would help.
Can you post the details of a couple of the hyperlinks
and what you want the end result to be?

Or...you could post a sample file at one of the free file sharing sites:
Some free filehosts that could be used:
http://www.freefilehosting.net/index.cfm
http://cjoint.com/index.php
http://www.savefile.com/index.php

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

They look like usual hyperlinks to me, there is a hyperlink name and the
actual web adress, nothing more.
Thing is, i need to keep the original hyperlink's name as well, as it
states
the product catalog number.


:

Rebuild them?...Maybe.

Can you post what you know about them?
Is it a Hyperlink formula? and not a Hyperlink?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

hi,

You were absolutely right - the problem were in the hyperlinks.
I tried to create a few links myself and the macro worked perfectly,
thx.
The original workbook wasn't made by me, so no idea how the hyperlinks
were
created there.

Is there an option to auto rebuild them somehow, as we're talking about
thousands of links here?

And another question: could you think of the way to show all the pics
in
the
workbook and not as cell comments, because i'm not sure the comments
solution
will 100% work for me, tho it's still very helpful, thanks.
I know you can't attach a picture into a cell, but they all have the
same
width, so i could simply set rows size accordingly, so they would fit
in.

Thanks in advance.

:

Hmmmm.....Using an empy workbook, I created some hyperlinks and
followed
the
instructions I posted. The macro ran without incident and the
pictures
were
displayed in comment boxes.

I'm guessing the issue lies with the links.
Are they created using <insert><hyperlink>?
What kinds of pictures do they link to? (jpeg, bmp, gif, something
else)

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


hi,

First of all thanks for the quick response.
This solution could definitely work for me, tho, seeing all the
product
pics
at once could have some advantages as well.

The problem is, i'm not much familiar with VB and i'm getting an
error
window after running the macro:
"Run-time error '-2147024809 (800070057)':
the item with specified name wasn't found."

the line which highlights in the debugger is: With
ActiveSheet.Shapes("TempPic")

Something does happen tho: the hyperlink of the first cell out of
several
selected is being converted to text(hyperlink name) and comment is
being
added to the cell, but it's blank.


:

If you'd consider putting the hyperlinked pictures in the cells'
comments,
then putting the mouse pointer over the cell would display the
associated
picture.

Here's how:

First, add the below procedures to a General Module
(Watch out for text wrap issues)

To run the macros....
1) Select the range of hyperlink cells that reference pictures
2) Run the "ConvertLinksToCommentPics" macro
<tools><macro><macros>.....Select:
ConvertLinksToCommentPics...Click:
Run

The hyperlinks will be removed and the linked pictures will be
inserted
into
the cells comments.


Sub ConvertLinksToCommentPics()
Dim cCell As Range
Dim rngSelection As Range
Dim strHLink As String
Dim cComment As Comment

Dim iNewHgt As Integer
Dim iNewWidth As Integer

For Each cCell In Selection
If cCell.Hyperlinks.Count > 0 Then
'The cell contains a hyperlink
With cCell
'Store the hyperlink target
strHLink = .Hyperlinks(1).Address

If strHLink <> "" Then
.Hyperlinks(1).Delete

'If the cell doesn not contain a comment create one
Set cComment = .Comment
If cComment Is Nothing Then
Set cComment = .AddComment(Text:="")
End If

'Build a temporary picture shape to read dimensions
from
'then delete the shape containing the picture
InsertPicFromFile _
strFileLoc:=strHLink, _
rDestCells:=[A1], _
blnFitInDestHeight:=False, _
strPicName:="TempPic"

With ActiveSheet.Shapes("TempPic")
iNewHgt = .Height
iNewWidth = .Width
End With

ActiveSheet.Shapes("TempPic").Delete

'Alter the comment to use the picture as the Fill
'and size the shape to the original picture size
With cComment.Shape
.Fill.UserPicture PictureFile:=strHLink
.LockAspectRatio = msoFalse
.Height = iNewHgt
.Width = iNewWidth
End With
End If
End With
End If
Next cCell
End Sub

'******************************
'* InserPicFromFile *
'* Programmer: Ron Coderre *
'* Last Update: 20-SEP-2007 *
'******************************
Sub InsertPicFromFile( _
strFileLoc As String, _
rDestCells As Range, _
blnFitInDestHeight As Boolean, _
strPicName As String)

Dim oNewPic As Shape
Dim shtWS As Worksheet

Set shtWS = rDestCells.Parent

On Error Resume Next
'Delete the named picture (if it already exists)
shtWS.Shapes(strPicName).Delete

On Error Resume Next
With rDestCells
'Create the new picture
'(arbitrarily sized as a square that is the height of the
rDestCells)
Set oNewPic = shtWS.Shapes.AddPicture( _
Filename:=strFileLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left + 1, Top:=.Top + 1, _
Width:=.Height - 1, Height:=.Height - 1)

'Maintain original aspect ratio and set to full size
oNewPic.LockAspectRatio = msoTrue
oNewPic.ScaleHeight Factor:=1,
RelativeToOriginalSize:=msoTrue
oNewPic.ScaleWidth Factor:=1, RelativeToOriginalSize:=msoTrue

If blnFitInDestHeight = True Then
'Resize the picture to fit in the destination cells
oNewPic.Height = .Height - 1
End If

'Assign the desired name to the picture
oNewPic.Name = strPicName
End With 'rCellDest
End Sub


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

I have an excel table which contains a list of products.
One of the columns contains a list of hyperlinks to each
product's
picture,
on a certain website.
I need a solution to get/import the actual pictures from the
website
to
my
table, according to each product's hyperlink(i can translate each
hyperlink
into a cell with the web adress of the picture, if it helps).

Thanks in advance.

 

[Ron Coderre]

Actually, I was hoping you'd post an Excel file containing some
representative hyperlinks to pictures. That way we could see what
issues you're facing.

....and yes, for the macros to work, the hyperlink must actually reference
an image on the web server, not a web page.

Are your hyperlinks to web pages?
If yes, then that will be an issue.

Example:
Create a hyperlink to the image of Debra Dalgleish's pivot table book:
http://www.contextures.com/images/amazon/21wQWbc4qKL._AA_SL160_.jpeg

If you click on that link, the picture will open in whatever
application your computer associates with the JPEG. On my computer,
it displayed in MS Photo Editor.

The main point is the link is to an image file, not a web page.

If you run the macro against the hyperlink, it will put the image in the
comment box.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

I uploaded a few pic files to http://www.freefilehosting.net, made their
hyperlink list and ran the macro.
I'm getting the same error as before on the first link already.
Thing is, their links doesn't contain an explicit filename in the end of
it.
Not sure how your macro works, but it could be the reason as well, i
guess.

Anyway, links are:
http://www.freefilehosting.net/show/3c8hm
http://www.freefilehosting.net/show/3c8i0
http://www.freefilehosting.net/show/3c8i1
http://www.freefilehosting.net/show/3c8i2
http://www.freefilehosting.net/show/3c8i3

thanks.

Perhaps an example would help.
Can you post the details of a couple of the hyperlinks
and what you want the end result to be?

Or...you could post a sample file at one of the free file sharing sites:
Some free filehosts that could be used:
http://www.freefilehosting.net/index.cfm
http://cjoint.com/index.php
http://www.savefile.com/index.php

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

They look like usual hyperlinks to me, there is a hyperlink name and
the
actual web adress, nothing more.
Thing is, i need to keep the original hyperlink's name as well, as it
states
the product catalog number.


:

Rebuild them?...Maybe.

Can you post what you know about them?
Is it a Hyperlink formula? and not a Hyperlink?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

hi,

You were absolutely right - the problem were in the hyperlinks.
I tried to create a few links myself and the macro worked perfectly,
thx.
The original workbook wasn't made by me, so no idea how the
hyperlinks
were
created there.

Is there an option to auto rebuild them somehow, as we're talking
about
thousands of links here?

And another question: could you think of the way to show all the
pics
in
the
workbook and not as cell comments, because i'm not sure the comments
solution
will 100% work for me, tho it's still very helpful, thanks.
I know you can't attach a picture into a cell, but they all have the
same
width, so i could simply set rows size accordingly, so they would
fit
in.

Thanks in advance.

:

Hmmmm.....Using an empy workbook, I created some hyperlinks and
followed
the
instructions I posted. The macro ran without incident and the
pictures
were
displayed in comment boxes.

I'm guessing the issue lies with the links.
Are they created using <insert><hyperlink>?
What kinds of pictures do they link to? (jpeg, bmp, gif, something
else)

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


hi,

First of all thanks for the quick response.
This solution could definitely work for me, tho, seeing all the
product
pics
at once could have some advantages as well.

The problem is, i'm not much familiar with VB and i'm getting an
error
window after running the macro:
"Run-time error '-2147024809 (800070057)':
the item with specified name wasn't found."

the line which highlights in the debugger is: With
ActiveSheet.Shapes("TempPic")

Something does happen tho: the hyperlink of the first cell out of
several
selected is being converted to text(hyperlink name) and comment
is
being
added to the cell, but it's blank.


:

If you'd consider putting the hyperlinked pictures in the cells'
comments,
then putting the mouse pointer over the cell would display the
associated
picture.

Here's how:

First, add the below procedures to a General Module
(Watch out for text wrap issues)

To run the macros....
1) Select the range of hyperlink cells that reference pictures
2) Run the "ConvertLinksToCommentPics" macro
<tools><macro><macros>.....Select:
ConvertLinksToCommentPics...Click:
Run

The hyperlinks will be removed and the linked pictures will be
inserted
into
the cells comments.


Sub ConvertLinksToCommentPics()
Dim cCell As Range
Dim rngSelection As Range
Dim strHLink As String
Dim cComment As Comment

Dim iNewHgt As Integer
Dim iNewWidth As Integer

For Each cCell In Selection
If cCell.Hyperlinks.Count > 0 Then
'The cell contains a hyperlink
With cCell
'Store the hyperlink target
strHLink = .Hyperlinks(1).Address

If strHLink <> "" Then
.Hyperlinks(1).Delete

'If the cell doesn not contain a comment create
one
Set cComment = .Comment
If cComment Is Nothing Then
Set cComment = .AddComment(Text:="")
End If

'Build a temporary picture shape to read
dimensions
from
'then delete the shape containing the picture
InsertPicFromFile _
strFileLoc:=strHLink, _
rDestCells:=[A1], _
blnFitInDestHeight:=False, _
strPicName:="TempPic"

With ActiveSheet.Shapes("TempPic")
iNewHgt = .Height
iNewWidth = .Width
End With

ActiveSheet.Shapes("TempPic").Delete

'Alter the comment to use the picture as the Fill
'and size the shape to the original picture size
With cComment.Shape
.Fill.UserPicture PictureFile:=strHLink
.LockAspectRatio = msoFalse
.Height = iNewHgt
.Width = iNewWidth
End With
End If
End With
End If
Next cCell
End Sub

'******************************
'* InserPicFromFile *
'* Programmer: Ron Coderre *
'* Last Update: 20-SEP-2007 *
'******************************
Sub InsertPicFromFile( _
strFileLoc As String, _
rDestCells As Range, _
blnFitInDestHeight As Boolean, _
strPicName As String)

Dim oNewPic As Shape
Dim shtWS As Worksheet

Set shtWS = rDestCells.Parent

On Error Resume Next
'Delete the named picture (if it already exists)
shtWS.Shapes(strPicName).Delete

On Error Resume Next
With rDestCells
'Create the new picture
'(arbitrarily sized as a square that is the height of the
rDestCells)
Set oNewPic = shtWS.Shapes.AddPicture( _
Filename:=strFileLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left + 1, Top:=.Top + 1, _
Width:=.Height - 1, Height:=.Height - 1)

'Maintain original aspect ratio and set to full size
oNewPic.LockAspectRatio = msoTrue
oNewPic.ScaleHeight Factor:=1,
RelativeToOriginalSize:=msoTrue
oNewPic.ScaleWidth Factor:=1,
RelativeToOriginalSize:=msoTrue

If blnFitInDestHeight = True Then
'Resize the picture to fit in the destination cells
oNewPic.Height = .Height - 1
End If

'Assign the desired name to the picture
oNewPic.Name = strPicName
End With 'rCellDest
End Sub


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

I have an excel table which contains a list of products.
One of the columns contains a list of hyperlinks to each
product's
picture,
on a certain website.
I need a solution to get/import the actual pictures from the
website
to
my
table, according to each product's hyperlink(i can translate
each
hyperlink
into a cell with the web adress of the picture, if it helps).

Thanks in advance.