Import image from path in adjacent cell

[mc12951]

I have been working on the below to bring an image into Column A where the full file path to the .jpg exists in Column B.
Other values may also exist in Column B so I want to check that the path is valid. When it is I want to import the image and when it isn't valid I want to move to the next cell.

I've found that the code works when selecting single cells and even 2 cells, but 3 or more in the range and it fails by generating a 1004 runtime error.

'macro that will insert an image in a selected range based on the path.jpg in the column to the right

Sub InsertPic()

'set some parameters
Dim Pic As String 'file path of pic
Dim myPicture As Picture 'embedded pic
Dim rng As Range 'range over which we will iterate
Dim cl As Range 'iterator
Dim FileName As String 'for checking if file exists

'use the user selected cells as the selection range
Set rng = Selection

'look in the cell to the right for the image value
For Each cl In rng
    Pic = cl.Offset(0, 1)

'set a filename for the image link in the adjacent cell
'check if the filename is valid
'if it isn't valid move down to "NextRow" which is towards the bottom of this macro
   
   On Error GoTo NextRow:
   FileName = VBA.FileSystem.Dir(Pic)
    If FileName <> VBA.Constants.vbNullString Then
    
'if no errors found and the filename is valid then import the image and set dimensions

        Set myPicture = ActiveSheet.Pictures.Insert(Pic)

            With myPicture
                .ShapeRange.LockAspectRatio = msoTrue
                .Height = 150
                '.Width = cl.Width
                .Top = Rows(cl.Row).Top + 4
                .Left = Columns(cl.Column).Left + 4
                cl.RowHeight = myPicture.Height + 4
                'cl.ColumnWidth = myPicture.Width + 4
                LinkToFile = msoFalse
                SaveWithDocument = msoTrue
            End With
        
Else
    End If

NextRow:
'move to the next cell
Next

End Sub

It feels like I'm very close to the solution but have spent a good few hours trying to get to a point where it's error free and have failed. Any help really appreciated!!

[snb]

Any sample file too.

[mc12951]

Any sample file too.

I don’t have a file handy but this should give you an idea of the format. I want users to be able to select cells in column A and run the macro to bring images from Column B into the cell.

Column A where I want the picture	Column B File path	Notes
	\\data\folder\example.jpg	This image exists so I want to import into column A
		Blank cell in column B so do nothing
		Blank cell in column B so do nothing
	\\data\folder\example_2.jpg	This file doesn’t exist in the folder so do nothing
		Blank cell in column B so do nothing

Thanks!

[snb]

You are drifting away from a solution as long as I don't see a sample Excel file.

[georgiboy]

Give the below a try, it looks at the filepaths in column B:

Sub test()    
    Dim img As Picture
    Dim rng As Range
    Dim rCell As Range, iRng As Range
    
    ' the below sets the range to all of the file paths that exist in column B
    Set rng = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
    
    On Error Resume Next ' will help us ignore images that do not exist, switch errors off
        For Each rCell In rng.Cells ' loop through the range set above
            Set img = ActiveSheet.Pictures.Insert(rCell.Value) ' looking at the filepath in column B
            Set iRng = rCell.Offset(, -1) ' set a range to the cell where the image is to be placed
            With img
               .Left = iRng.Left
               .Top = iRng.Top
               .Width = iRng.Width
               .Height = iRng.Height
               .Placement = xlMoveAndSize ' how the image is placed (floating, move and size etc...)
               .PrintObject = True ' yes or no to print the object
            End With
            Set img = Nothing ' clear the image in case the next image is missing
        Next rCell
    On Error GoTo 0 ' turn errors back on
End Sub

[Aussiebear]

Being able to receive assistance is clearly graded ( in terms of usefulness) up the following:

1.an example file, ( Very good)
2.a distinct explanation ( sometimes good but misses critical points)
3.an image ( poor as it lacks the real detail, as images don't expand well enough to show the real detail)
4. rambling text ( most people will simply pass it by)

its your approach but in Post #3 you haven't really helped anyone to "want" to assist you. Being precise as possible is an advantage.

with that in mind, how is VBA meant to read a JPG image in a cell?

[mc12951]

Give the below a try, it looks at the filepaths in column B:

Sub test()    
    Dim img As Picture
    Dim rng As Range
    Dim rCell As Range, iRng As Range
    
    ' the below sets the range to all of the file paths that exist in column B
    Set rng = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
    
    On Error Resume Next ' will help us ignore images that do not exist, switch errors off
        For Each rCell In rng.Cells ' loop through the range set above
            Set img = ActiveSheet.Pictures.Insert(rCell.Value) ' looking at the filepath in column B
            Set iRng = rCell.Offset(, -1) ' set a range to the cell where the image is to be placed
            With img
               .Left = iRng.Left
               .Top = iRng.Top
               .Width = iRng.Width
               .Height = iRng.Height
               .Placement = xlMoveAndSize ' how the image is placed (floating, move and size etc...)
               .PrintObject = True ' yes or no to print the object
            End With
            Set img = Nothing ' clear the image in case the next image is missing
        Next rCell
    On Error GoTo 0 ' turn errors back on
End Sub

Thank you so much! This has really helped me out.

I've adapted it slightly below to use a user selected range for "Set Rng". Eg they select there they want images to land and then it checks the neighbouring cell for the values. I've not been able to re-work the SpecialCells element into the user selected range. Is this important?

Thanks again!!

Sub insertpic()
    Dim img As Picture
    Dim rng As Range
    Dim rCell As Range, iRng As Range
    
    ' the below sets the range to all of the file paths that exist in column B
    Set rng = Selection.Offset(0, 1).SpecialCells(xlCellTypeConstant)
    
    
    On Error Resume Next ' will help us ignore images that do not exist, switch errors off
        For Each rCell In rng.Cells ' loop through the range set above
            Set img = ActiveSheet.Pictures.Insert(rCell.Value) ' looking at the filepath in column B
            Set iRng = rCell.Offset(, -1) ' set a range to the cell where the image is to be placed
            With img
               .Left = iRng.Left + 4
               .Top = iRng.Top + 4
               .Width = iRng.Width
               '.Height = iRng.Height
               .Height = 150
               iRng.RowHeight = img.Height + 8
               .ShapeRange.LockAspectRatio = msoTrue
               .Placement = xlMove ' how the image is placed (floating, move and size etc...)
               .PrintObject = True ' yes or no to print the object
                LinkToFile = msoFalse
                SaveWithDocument = msoTrue
            End With
            Set img = Nothing ' clear the image in case the next image is missing
        Next rCell
    On Error GoTo 0 ' turn errors back on
End Sub

[georgiboy]

Glad it helped, the special cells part will skip over the blank cells in the range set. This means it should only loop through the cells in that range that contain a constant. Effectively it should only loop through the cells in the range that contain a filepath.

The attached may help understand the specialcells function.