2

I've an Excel file with a bunch of columns, one of which is "ImageURL", which, of course, displays unique URLs as text.

How could I set it up such that those images are also depicted within another column?

I've used the following macro, but I get a "Invalid outside procedure" compile error.

Dim url_column As Range
Dim image_column As Range

Set url_column = Worksheets(1).UsedRange.Columns("C")
Set image_column = Worksheets(1).UsedRange.Columns("N")

Dim i As Long
For i = 1 To url_column.Cells.Count

  With image_column.Worksheet.Pictures.Insert(url_column.Cells(i).Value)
    .Left = image_column.Cells(i).Left
    .Top = image_column.Cells(i).Top
    image_column.Cells(i).EntireRow.RowHeight = .Height
  End With

Next

I am, unfortunately, new to VBA, so perhaps, I've not set it up correctly?

Community
  • 1
  • 1
oxo
  • 4,343
  • 10
  • 32
  • 35
  • You can't embed an image in a cell, you can have a image object reference the cell though... SO are your expectations that such that the URL would be in A1 and the related image would be in B1? If so, Your expectations can't be met with native excel. http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=3&ved=0CEUQFjAC&url=http%3A%2F%2Fchannel9.msdn.com%2FForums%2FTechOff%2F260021-Excel-2003-Cant-Insert-Picture-Into-Cell-&ei=YLLXTpvBKqiqiQKl3oypCg&usg=AFQjCNHyZnUtVzN4tCPWR_2244dtQG004Q&sig2=K6VqyS-g2HhjS7BfMjyQzw – xQbert Dec 01 '11 at 17:04
  • "have a image object reference the cell though" <- can you expand on this? This might be a useful alternative. – oxo Dec 01 '11 at 17:07

2 Answers2

2

Ok, this may sound pretty basic (no pun intended), but based on the limited information you made available, I think that the cause of your problem is that you just pasted those statements in your code module and didn't put them inside a procedure. That will certainly give you an "Invalid outside procedure" compile-time error.

You have to put stuff inside a procedure -- either a Sub or a Function. This case calls for a Sub. Try this:

Sub PlaceImageInCell()

    Dim url_column As Range
    Dim image_column As Range
    Set url_column = Worksheets(1).UsedRange.Columns("A")
    Set image_column = Worksheets(1).UsedRange.Columns("B")

    Dim i As Long
    For i = 1 To url_column.Cells.Count
      With image_column.Worksheet.Pictures.Insert(url_column.Cells(i).Value)
        .Left = image_column.Cells(i).Left
        .Top = image_column.Cells(i).Top
        image_column.Cells(i).EntireRow.RowHeight = .Height
      End With
    Next

End Sub
Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
1

.Pictures.Insert(stuff) doesn't work in XL 2007 - and I've seen suggestions to use *.Shapes.AddPicture() instead.

Problem is that it requires a String for the filePath and I'm not familiar enough with VBA to make this work.

Sub InsertImage()
    Dim urlColumn As Range
    Dim imgColumn As Range
    Dim fp as String
    Set urlColumn = Worksheets(1).UsedRange.Columns("A")
    Set imgColumn = Worksheets(1).UsedRange.Columns("B")

    Dim i As Long
    For i = 2 To urlColumn.Cells.Count
        With imgColumn.Worksheet.Shapes.AddPicture(fp, msoTrue, msoTrue, 1, 1, 12, 12)
        End With
    Next
End Sub

The end result is the following Error: Compile Error: Object Required

Sam
  • 487
  • 1
  • 10
  • 25