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:
Excel vba insert picture from folder based on cell value
Excel vba insert picture from folder - Best answers
Unable to get the insert property of the pictures class - Best answers
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
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
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
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”
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.
That worked well however I was hoping to put the path and destination cell references in the code itself rather than in cells on another sheet that are referenced in the code. Can this code be modified to that end?
The reason for this is that my barcode template sheets have a cell with a lookup to find the path/image from another sheet and a cell ready for the barcode, so I really just need a macro to insert the image based on the text in the path/image cell.
I hope this all makes sense given my limited/non-existent understanding of VBA, please let me know if it doesn’t
Hard-coding data like that is bad practice and inflexible and can become unwieldy very fast.
In case I'm off base here can you post a sample sheet or example of the layout to give me more of an idea.
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.
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.
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’
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
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