Inserting image files from a directory specified by text string

[Closed]
Report
Posts
8
Registration date
Sunday April 3, 2016
Status
Member
Last seen
April 18, 2016
-
Posts
8
Registration date
Sunday April 3, 2016
Status
Member
Last seen
April 18, 2016
-
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
Related:

4 replies

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
Posts
8
Registration date
Sunday April 3, 2016
Status
Member
Last seen
April 18, 2016

G’day RayH

Alas I can’t seem to get this to work, I have paste the code into the ‘this workbook’ excel object and then run the macro however I receive an error box with the number 400 in it.

I’m certain this would work if I had any idea what I was doing, have you any idea what exceptionally obvious thing I am doing wrong?

Could I perhaps amend my initial question slightly?

If I were, alternatively, to have the full ‘picture path’ name in one cell (say A1) as well as a destination cell (say B1) both specified in a code, could this code be used to paste the image corresponding to that path in that destination cell?

It may be worth mentioning I'm designing a system to make price tags and I want to use this code to take the barcode image from a file of barcode images and past it directly into the template on the sheet

Cheers for any advice you can provide

Regards
Matt
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
Posts
8
Registration date
Sunday April 3, 2016
Status
Member
Last seen
April 18, 2016

Yep no worries RayH

So, I placed this new code in the ‘This Workbook’ object, run the macro (by hitting macro’s and then running “ThisWorkbook.insertpictures”) and get the following error “Error Number: 1004 – Unable to get the insert property of the Picture class”

Does this make any sense?

Regards

Matt
I get that error when the path and file cannot be found

Did you put the 3 char extension in there? e.g. "mypicture.jpg" rather than just "mypicture"

or
the spreadsheet cells are locked or locked but unable to 'edit objects'
Posts
8
Registration date
Sunday April 3, 2016
Status
Member
Last seen
April 18, 2016
> RayH
Ah that works perfectly mate

Figures it would be something so small and simple that I would have overlooked.

Thanks for pointing that out I never would have noticed it.

Have you any thoughts on my amended question? I realise now it's what I should have asked in the first place...

Would appreciate any advice on this

I will make a point of remembering the file extension this time.

Regards

Matt
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
Posts
8
Registration date
Sunday April 3, 2016
Status
Member
Last seen
April 18, 2016
> RayH
G'day RayH and ac3mark

My apologies for taking so long to respond had a couple of days working out bush and haven’t had the time/internet to look at it.

Forgive my ignorance but how do I attach a file to this forum? I can't seem to see a paperclip symbol anywhere, otherwise I'd have attached a file to begin with.

Appreciate you taking the time mate,

Regards
MattRNR
>
Posts
8
Registration date
Sunday April 3, 2016
Status
Member
Last seen
April 18, 2016

You cannot attach a file to the forum but you can upload it to a file sharing site like http://www.speedyshare.com
Upload the file there and post the link it provides here.
Posts
8
Registration date
Sunday April 3, 2016
Status
Member
Last seen
April 18, 2016
> RayH
G’day RayH

Where has this speedy share website been all my life? This is going to be an absolute life saver thank you for putting me onto it.

Assuming I’ve done this properly a copy of my spreadsheet (having highlighted the relevant components to the barcodes addition in red) should be here: https://authentification.site/K6p2h/Project-Eomer-with-Automatic-Barcodes-V1-140416.xlsm

You’re the expert mate, if hardcoding is bad practice I can work around it. To this end I’ve add a few columns with the destination cell references in them (which I will hide when the spreadsheet is finished).

If I can understand how to set up a macro to insert one picture from the folder into the specified destination cell on the given template I should be able to do it for all the others as well as any future templates that may be needed.

Really appreciate you taking the time to help me on this one.

Regards
MattRNR

P.S. I will account for the need for vertical barcodes on T2 by adding vertical barcode images to the source file and adding a v to the end of the file name so it will find the vertical images (hopefully this will be easy).

P.S. P.S. The password for the database sheet is ‘Vesper’, the password for the VBA code is ‘Eomer’
Blocked Profile
@rayH - this is why I don't help if there is no code to correct. Change order after failed change after failed change! I commend you for your efforts!
Thanks ac3mark, I enjoy a challenge.
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.
Posts
8
Registration date
Sunday April 3, 2016
Status
Member
Last seen
April 18, 2016

Sorry should have clarified all that;

Yes the B01, B02 and B10 etc. are the file name of the barcode picture.

Yes T2 will need a different picture I will simply modify the picture names with a v (i.e. Bv01) later to refer to a different file name for vertical barcodes.

I did combine the clear functions at one point but to be honest I found it infinitely easier to use separate clear functions for each thing, probably not best practice either but for some reason I found it easier…

I was looking to do something similar with the barcodes hence the revised post, i.e. to have a separate code for each ‘insert picture’ action for each template.

But again you’re the expert, if that’s not best practice and too much of a hassle no worries…

Barcode examples should be here: http://speedy.sh/Wn5kP/Barcode-examples.zip

If there’s any further information needed please let me know

Thank you again for your help

Regards

MattRNR