How to import picture file from folder using cell value?

**kaypat** · 02-09-2012, 01:55 PM

Hello,

I am trying to create new excel file where I need to import picture file based on cell vale.

for example: at cell A1 I will input item number (i.e. 12345, 23456, 34567 etc.) and at cell B1 or anywhere in that spreadsheet I need associated picture.

I will have all associated pictures in one folder with picture name same (i.e. 12345, 23456, 34567 etc.): C:\Documents and Settings\User\Desktop\Pictures

How to go from here?

**Leith Ross** · 02-09-2012, 11:10 PM

Hello kaypat,

This macro should get you started. This will import the file using the name in the active cell. If the file is found in the specified folder, it will be inserted with the upper left corner of the picture in the cell to right of the active cell. The macro does not size the picture. It is inserted at full size. Als, the macro expects the picture to have a jpg extension. If your pictures have a different extension, it will need to be changed in the macro code.

Please Login or Register  to view this content.

**kaypat** · 02-13-2012, 11:37 AM

Thanks Leith. I will try this.

Question: In macro instead of ActiveCell, should I put cell location (i.e. A1 or B1)?

If not then, what formula should I put where I want picture?

Thanks again.

**Leith Ross** · 02-13-2012, 05:52 PM

Hello kaypat,

I used ActiveCell only as example. If you have a specific cell in mind, say "A1" with the file path and name, then the code would be as below. This will place the upper left corner of the picture in cell "B1".

Please Login or Register  to view this content.

**kaypat** · 02-14-2012, 01:22 PM

Leith,

When I rum macro using this code I get Run-time error '1004': (I have attached Picture 1)1.JPG

And when I do Debug, it highlight following line in code: (I have attached picture 2) 2.JPG

Set Pic = ActiveSheet.Pictures.Insert(Filepath & ActiveCell)

Thank you for your help!

**Leith Ross** · 02-14-2012, 02:42 PM

Hello kaypat,

It appears that the Picture class has been dropped from Excel starting with Excel 2007. Here is another way that should work.

Please Login or Register  to view this content.

**kaypat** · 02-14-2012, 04:36 PM

Great. This works!
Thank you again!!

I have couple of questions if that's not too much to ask

Can macro re-size picture automatically to fit within certain rows & column or in text-box?
Can this macro run automatic (i.e. as soon as I enter value in cell A1 it imports picture)?
Also, when I ran macro using one cell value (A1) it imported associated pic and when I changed cell value it imported new cell value picture but over old pic (i.e. it did not over-write on old pic or it did not delete old pic before importing new pic)? Is it possible to delete old cell value pic while importing new cell value pic?

**Leith Ross** · 02-14-2012, 04:40 PM

Hello kaypat,

Yes, yes, and yes. Would you like me change the macro to do this? My guess is yes.

**kaypat** · 02-14-2012, 05:20 PM

I would be really grateful if you would do that for me?

**Leith Ross** · 02-15-2012, 10:16 PM

Hello kaypat,

Here is the revised macro to copy the image from a file into a range of cells. The previous picture will be deleted before the new picture is added.

The attached workbook will import the picture using the contents of cell "A1" on "Sheet1" as the file name. The macro assumes all files are a jpg type so do not add ".jpg" to the file name. The picture range is cells C2 to E14. The picture will be sized automatically to fit the cells in the range.

There are now two macros. One is the Worksheet_Change event for "Sheet1" which checks that you entered a value into cell and calls the import picture macro. The import picture macro then adds the picture from the file to the worksheet and resizes it to fit the range.

Worksheet_Change Event Macro Code

Please Login or Register  to view this content.

Import Picture To Range Macro Code

Please Login or Register  to view this content.

**kaypat** · 02-16-2012, 10:43 AM

Hey Leith,

Your attached file doesn't work for me?

All I did was changed file path to where I have pics located but it does not do anything! What am I doing wrong?

**kaypat** · 02-22-2012, 09:59 AM

Hello Leith,

I was finally able to figure it out.

As when you type cell value at A1 and enter.....macro would run automatically to import picture per your code however when ImportPicture macro runs active cell now is A2 (since I hit enter after typing value)......it did not import picture!

What I did is defined cell value A2 equals A1 and highlighted in white you so don't see it..........with this trick macro does it trick per your code!!

Thank you for your help with this code!

**dlac** · 01-28-2013, 05:57 PM

Hey all,

I'm trying to use this as well - this isn't set up to work for an entire column, is it? Just the first cell?

How would I go about changing it so that all values in A:A pull up an image directly to the right of them?

Thanks for any help you can offer,
-Dan

**arlu1201** · 01-29-2013, 02:12 AM

dlac,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.