Inserting image files from a directory specified by text stringClosed

Question

G’day CCM forum

Quick hypothetical; I have a list of image file names in a column on an excel spreadsheet and a cell at the top of the page containing a file directory name.

Is it possible to set up a macro that will automatically insert the corresponding image into the cell containing its file name from the specified file directory? Or into the cell adjacent?

Any thoughts on this or direction to relevant resources would be greatly appreciated

P.S. I’m quite a novice so my knowledge of lingo may be a little off

Regards

MattRNR

RayH · Answer

G'Day Matt

With the Folder path in Cell A1 and the filename's of the pictures in cells A2, A3 etc. this will insert each picture into the adjacent cell B2, B3 etc.

Before to size column B and the rows for each picture before running the macro otherwise scaling doesn't seem to work after the fact.

Sub insertpictures()
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For myRow = 2 To LR
PicturePath = Range("A1").Value & "\" & Range("A" & myRow).Value

With ActiveSheet.Pictures.Insert(PicturePath)
    With .ShapeRange
        .LockAspectRatio = msoTrue
        .Width = Cells(myRow, 2).Width
        .Height = Cells(myRow, 2).Height
    End With
    
    .Left = ActiveSheet.Cells(myRow, 2).Left
    .Top = ActiveSheet.Cells(myRow, 2).Top
    .Placement = xlMoveAndSize
End With
Next myRow

End Sub

RayH · Answer

Before making any changes to the spec lets try and find out why it fails for you.
I've added some lines that may help tell us what the error really is.
I am unable to get the same problem as you.

Let us know the result.

Sub insertpictures()
Dim myRow As Long
Dim LR As Long
Dim PicturePath As String

On Error GoTo errorhandling

LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For myRow = 2 To LR
PicturePath = ActiveSheet.Range("A1").Value & "\" & ActiveSheet.Range("A" & myRow).Value

With ActiveSheet.Pictures.Insert(PicturePath)
    With .ShapeRange
        .LockAspectRatio = msoTrue
        .Width = Cells(myRow, 2).Width
        .Height = Cells(myRow, 2).Height
    End With
    
    .Left = ActiveSheet.Cells(myRow, 2).Left
    .Top = ActiveSheet.Cells(myRow, 2).Top
    .Placement = xlMoveAndSize
End With

Next myRow
Exit Sub

errorhandling:
MsgBox "Error Number:" & Err & " - " & Err.Description

End Sub

RayH · Answer

Glad that worked ok.
I have made some assumptions to come up with this, one of them being that the destination sheet is always the same.

Two sheets, one called "Files" and a second called "Template"

The Files sheet has 2 columns. Path and Dest
In column A (Path) : File Path and File, e.g. "C:\mypictures\picture1.jpg"
In column B (Dest) : The destination, e.g. D3

The Template sheet is whatever you want it to look like.

Sub insertpictures2()
Dim myRow As Long
Dim LR As Long
Dim PicturePath As String
Dim DestAddr As String
Dim rng As Range

On Error GoTo errorhandling

LR = Sheets("Files").Range("A" & Rows.Count).End(xlUp).Row

For myRow = 2 To LR
PicturePath = Sheets("Files").Cells(myRow, 1).Value
DestAddr = Sheets("Files").Cells(myRow, 2).Value
Set rng = Range(DestAddr)

With Sheets("Template").Pictures.Insert(PicturePath)
    With .ShapeRange
        .LockAspectRatio = msoTrue
        .Width = rng.Width
        .Height = rng.Height
    End With

.Left = rng.Left
    .Top = rng.Top
    .Placement = xlMoveAndSize
End With

Next myRow
Exit Sub

errorhandling:
MsgBox "Error Number:" & Err & " - " & Err.Description

End Sub


What this does is put the picture referenced in column A of the Files sheet into the Template sheet in the cell referenced in column B of the Files sheet.

Good Luck

RayH · Answer

Matt,

I have your file and have some comments:

Interesting choice of products. Very random. :)

Are the B01, B02..B10 references to the file name of the bar code picture?

Sheet T1 has a different orientation than T2 or T3. Wont this need a different picture than the others?

The Clear functions could do with cleaning up  a bit (a lot). Since they dont seem to be called from elsewhere they could be combined.

Is it possible for you to ZIP up bar code pictures and upload the file as i have nothing to play with.

Inserting image files from a directory specified by text string

4 replies

Subscribe To Our Newsletter!