Inserting image files from a directory specified by text string
Closed
MattRNR
MattRNR
- Posts
- 8
- Registration date
- Sunday April 3, 2016
- Status
- Member
- Last seen
- April 18, 2016
MattRNR
- Posts
- 8
- Registration date
- Sunday April 3, 2016
- Status
- Member
- Last seen
- April 18, 2016
Related:
- Excel vba insert picture from folder based on cell value
- Excel vba insert picture from folder - Best answers
- How to insert picture in excel cell automatically from folder - Best answers
- Insert picture in Excel macro which takes the file name refrence - Forum - Excel
- Inserting image in a particular cell in VBA ✓ - Forum - Excel
- Adding a loop to macro to insert pictures ✓ - Forum - Excel
- Excel Picture Inserting Macro Help - Urgent ✓ - Forum - Excel
- Excel vba insert picture from folder - Forum - Excel
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.
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
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.
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
MattRNR
Apr 7, 2016 at 08:01 AM
- Posts
- 8
- Registration date
- Sunday April 3, 2016
- Status
- Member
- Last seen
- April 18, 2016
Apr 7, 2016 at 08:01 AM
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
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
MattRNR
Apr 7, 2016 at 05:25 PM
- Posts
- 8
- Registration date
- Sunday April 3, 2016
- Status
- Member
- Last seen
- April 18, 2016
Apr 7, 2016 at 05:25 PM
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
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.
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
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
MattRNR
Apr 8, 2016 at 07:48 AM
- Posts
- 8
- Registration date
- Sunday April 3, 2016
- Status
- Member
- Last seen
- April 18, 2016
Apr 8, 2016 at 07:48 AM
G’day RayH
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
Regards
Matt
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
Regards
Matt
MattRNR
Apr 13, 2016 at 09:08 PM
- Posts
- 8
- Registration date
- Sunday April 3, 2016
- Status
- Member
- Last seen
- April 18, 2016
Apr 13, 2016 at 09:08 PM
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
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
RayH
>
MattRNR
Apr 13, 2016 at 09:24 PM
- Posts
- 8
- Registration date
- Sunday April 3, 2016
- Status
- Member
- Last seen
- April 18, 2016
Apr 13, 2016 at 09:24 PM
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.
Upload the file there and post the link it provides here.
MattRNR
Apr 15, 2016 at 02:06 AM
- Posts
- 8
- Registration date
- Sunday April 3, 2016
- Status
- Member
- Last seen
- April 18, 2016
Apr 15, 2016 at 02:06 AM
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’
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’
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.
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.
MattRNR
Apr 18, 2016 at 07:46 AM
- Posts
- 8
- Registration date
- Sunday April 3, 2016
- Status
- Member
- Last seen
- April 18, 2016
Apr 18, 2016 at 07:46 AM
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
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
Apr 6, 2016 at 05:42 AM
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