• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Inserting pictures in bulk into Excel?

C

cubs610

Member
Hi Everyone... just had this question popped up and I'm thinking the best option is a macro. Love to hear your thoughts/ probable solutions, alternatives... please see italics.

I have a project where 450 pictures are needed to be inserted into their own column, matched to a part number in preceding column. I do not want to hit Insert->Picture and then have to browse through the many system folders in order to insert each individual picture, 450 times! Are there shortcuts/ commands I can use so that Excel will already be pointed to the source directory? Excel keeps reverting back to My Pictures folder, ugh. I know macros are an option but I haven’t had much luck trying to copy any into Visual Basic and apply them

Any thoughts?

Thanks,
Dave
 
Hey Deepak.... Here you go... along with some supplemental text.

"The model number column actually FOLLOWS the picture column, so my mistake in describing that previously. There will be some formatting done after all the pics are inserted. I can select Insert->Picture->Recent Places which does go much faster, as I can search for model numbers from that pane without additional browsing. However, any further streamlining would still be a plus!

Hopefully the attached will suffice – I had to skip other cells due to industry sensitivities since the workbook will be part of a sales initiative."

Thanks,
Dave
 

Attachments

  • Picture_Import.xls
    72.5 KB · Views: 11
Pls check this..

Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If IsEmpty(Target) Or Target.Column <> 1 Then Exit Sub

Dim path As String, pic As String, pname As String

path = "C:\Users\dEEPAK\Desktop\" 'change as req
pic = Trim(Left(Target, InStr(Target, "/") - 1))

pname = path & pic & ".jpg"

With ActiveSheet.Pictures.Insert(pname)
    .Height = 20
    .Width = 30
    .Left = 350
    .Top = (Target.Row - 2) * 35 + 15
End With
Target.Offset(0, 2).Value = pic
End Sub
 
Hey guys.. I'm back, can use a little more help. Please see italicized text and photo...

... I took a screen shot of how the macro looks with my modification to it. Dual monitor set up – on the side you can see the directory that I need to pull the image files from. On the left is Visual Basic. Now, from what I understand, I pasted the macro you sent and just needed to make the code path = source directory for image files. Then I saved it. Then I hit F5 to run the code, but that brings up a menu to select the code to run and no code exists. I thought I set up the macro to apply to the entire workbook. Was I not supposed to Save anything yet, or does a particular cell/ column need to be selected in Excel before inserting the macro in VB?

Thoughts...

Thanks,
Dave


macro_screen-shot.jpg
 
Hi Dave ,

Your pic shows the code inserted in a module ; this should be inserted in the section pertaining to Sheet1. Click on the sheet name , and in the pane on the right copy the code.

Since this procedure is labelled Worksheet_Change , it will work only if it is in a sheet section.

Narayan
 
Hi Everyone.... here is the latest.

Here’s a new screen shot with changing the sheet name. Sheet1 is definitely the one we are trying to apply the macro to.

Also, the web site I have been primarily trying to follow as DIY: http://www.rondebruin.nl/win/code.htm

The screen shot is capturing the point after I have pasted the code and am trying to Run it. If I select Run from menu or use F5, nothing is there to select under Macro Name, so do I just make up a name for the code and Create? I did that and called it PicInsert. But that just gave me a so named Module with no code in it.

Thanks again for your help!

-Dave
 

Attachments

  • Macro-Sheet.jpg
    Macro-Sheet.jpg
    920.1 KB · Views: 6
Hi @Dave
let me clarify about where macro code needs to store.

This code have to be pasted in the a sheet in which you want to insert pictures.

the following sheet change event will auto run the macro as soon as you hit the enter button in col A {A1,A2,A3.....}.
PrivateSub Worksheet_Change......

the follwing line will check that macro will only run when you edit a not empty cell in col A like as A1,A2,A3 & son on......

If IsEmpty(Target) Or Target.Column <> 1 ThenExitSub

If you wish to manually run the macro then paste the following code in standard module like as Module1

After pasting the code in a module close the window pressing Alt+Q then go to sheet & press Alt+F8. A window will come-up to choose macro.
Select & run the same there.

Code: 
Option Explicit

Sub InsertPic()
Dim path As String, pic As String, pname As String
Dim lastrow As Long, r As Range

path = "C:\Users\dEEPAK\Desktop\" 'change as req
lastrow = Range("A" & Rows.Count).End(xlUp).Row

For Each r In Range("A2:A" & lastrow)
    If Not InStr(r.Value, "/") > 1 Then GoTo N
        pic = Trim(Left(r.Value, InStr(r.Value, "/") - 1))
        pname = path & pic & ".jpg"
            With ActiveSheet.Pictures.Insert(pname)
                .Height = 20
                .Width = 30
                .Left = 350
                .Top = (r.Row - 2) * 35 + 15
            End With
    r.Offset(0, 2).Value = pic
N: Next
End Sub


You may also refer herewith attached xl.

When running this macro be sure that you have deleted PrivateSub Worksheet_Change(ByVal Target As Range) from that sheet.
 

Attachments

  • Picture_Import.xls
    87 KB · Views: 9
You must log in or register to reply here.
Back
Top