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.

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

"forxigan" wrote:

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.

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.

"Joel" wrote:


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

"forxigan" wrote:

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.

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

"forxigan" wrote:

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.

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" wrote in message
...
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.

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.


"Ron Coderre" wrote:

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" wrote in message
...
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.

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)


"forxigan" wrote in message
...
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.


"Ron Coderre" wrote:

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" wrote in message
...
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.

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.

"Ron Coderre" wrote:

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)


"forxigan" wrote in message
...
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.


"Ron Coderre" wrote:

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" wrote in message
...
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.

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)

"forxigan" wrote in message
...
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.

"Ron Coderre" wrote:

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)


"forxigan" wrote in message
...
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.


"Ron Coderre" wrote:

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" wrote in message
...
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.

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.


"Ron Coderre" wrote:

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)


"forxigan" wrote in message
...
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.


"Ron Coderre" wrote:

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" wrote in message
...
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.