insert image URL images into CELLS

[excel_2009]

hi excel gurus,

I was wondering if it would be possible to insert image urls as images into cells in excel? if so how?

thank you

 

[excel_2009]

Hi Andrew,

That is perfect, however can i adjust the coding so that there is a range? but the range of rows will vary on any given day but the image url will always be in the same column?

i hope that makes sense?

 

[Andrew Poulsom]

Yes, of course:

Sub Test()
    Dim Rng As Range
    Dim Cell As Range
    Dim Pic As Picture
    Application.ScreenUpdating = False
    Set Rng = Range("C1:C" & Range("c" & Rows.Count).End(xlUp).Row)
    For Each Cell In Rng
        With Cell
            Set Pic = .Parent.Pictures.Insert(.Value)
            With .Offset(, -1)
                Pic.Top = .Top
                Pic.Left = .Left
                Pic.Height = .Height
                Pic.Width = .Width
            End With
        End With
    Next Cell
    Application.ScreenUpdating = True
End Sub

 

[excel_2009]

Hi Andrew,

I've just executed the coding however I get the following error :

Run-time error 1004

Unable to get the Insert property of the Pictures class

and it highlights this line when i debug:

Set Pic = .Parent.Pictures.Insert(.Value)

 

[Andrew Poulsom]

It worked when I just tried it. Are you sure the URL is valid?

 

[excel_2009]

Hi Andrew,

I am using 2007 would that make a difference? the image urls are working fine in the browser

 

[excel_2009]

Oddly enough,

I restarted my P.C and now its working fine?!

Very strange, THank you Andrew you have been extremely helpful

 

[excel_2009]

Hi Andrew,

I have one last question regarding this topic, some image urls are broken thereby causing the runtime error, is it possible to ignore those errors?

i've adapated the coding to the following:

 Sub IMAGE()
   

On Error Resume Next


  Dim Rng As Range
    Dim Cell As Range
    Dim Pic As Picture
    
    Application.ScreenUpdating = False
    Columns("J:J").ColumnWidth = 21.29
    
    Set Rng = Range("K2:K" & Range("k" & Rows.Count).End(xlUp).Row)
    For Each Cell In Rng
        With Cell
            Set Pic = .Parent.Pictures.Insert(.Value)
            With .Offset(, -1)
                Pic.Top = .Top
                Pic.Left = .Left
                Pic.Height = .Height
                Pic.Width = .Width
            End With
        End With
    Next Cell
    
    On Error GoTo 0
    
    Rows("2:11").RowHeight = 120
    Application.ScreenUpdating = True




End Sub

However if there is a broken URL in cell K8 then the image of cell K7 shows in its place in J8 which is really bizarre?? any idea on how this could be fixed?

Thank you

 

[Andrew Poulsom]

Maybe try:

Sub IMAGE()
    Dim Rng As Range
    Dim Cell As Range
    Dim Pic As Picture
    Application.ScreenUpdating = False
    Columns("J:J").ColumnWidth = 21.29
    Set Rng = Range("K2:K" & Range("k" & Rows.Count).End(xlUp).Row)
    For Each Cell In Rng
        With Cell
            On Error Resume Next
            Set Pic = .Parent.Pictures.Insert(.Value)
            If Err <> 0 Then
                Err.Clear
            Else
                With .Offset(, -1)
                    Pic.Top = .Top
                    Pic.Left = .Left
                    Pic.Height = .Height
                    Pic.Width = .Width
                End With
            End If
            On Error GoTo 0
        End With
    Next Cell
    Rows("2:11").RowHeight = 120
    Application.ScreenUpdating = True
End Sub

 

[excel_2009]

Hi Andrew,

That is the definition of perfect!!! you have been extremely helpful, you truly are an asset to the excel community

On the offchance, do you know how to adapt this line so that its flexible:

Rows("2:30").RowHeight = 120

i.e the number of rows on any given day changes, there might be 10 rows, 100, 1000 etc

Is that possible?

Thank you so much!!!!!!!!!!!

 

[Andrew Poulsom]

Is it?

Rng.EntireRow.RowHeight = 120