+ Reply to Thread
Results 1 to 12 of 12

Insert multiple pictures

  1. #1
    Registered User
    Join Date
    07-27-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    7

    Insert multiple pictures

    Hi everyone,

    This is my first post, I would greatly appreciate some assistance with a particular requirement I have to automatically insert photos into cells in Excel 2007.

    I have searched these forums and others and can't quite find what I am looking for.

    This request may sound a little odd, but its to enable me to print a particular report.

    What I'm looking for is a macro that looks for the filenames on any row in either Columns "A", "B" or "Q", and then inserts the picture into the same cell where the filename was found.

    So if "picture 1" is in cell A15, then I would like picture 1 to be inserted into cell A15. If "picture 27" is in cell Q268, then I would like "picture 27" inserted into cell Q268.There will not be any pictures beyond about row 2000 if that helps.

    I tried to use a loop, but the catch is that most of the cells in these columns will be empty, so just the occasional filename, that I want replaced with its corresponding photo.

    I also would like the ability for the code to specify the size of the pictures (all pictures will be the same size though).

    I have searched these forums and found something that does part of what I need:

    Please Login or Register  to view this content.


    The major thing thats missing from this code is that it requires me to specify the exact cell to look for the filename.

    I found some other code that cycled through a loop, but the problem I found with this was the code stopped when it came to an empty cell in a column.

    I would really appreciate some help, many thanks in advance.


  2. #2
    Registered User
    Join Date
    07-27-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Insert multiple pictures

    Hi,

    Just checking again whether anyone can assist with this?

    Thanks

  3. #3
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303

    Re: Insert multiple pictures

    Can you please put a sample file, so that we can see what kind of loop will be required to put into?

    Thanks,
    Vikas

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Insert multiple pictures

    If you do an Advanced Forum Search - User: Andy Pope, & something like insert image, Andy has posted code specific to Excel 2007 for this
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  5. #5
    Registered User
    Join Date
    07-27-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Insert multiple pictures

    Thanks Vikas, thanks Roy,

    Roy I have searched the forums using every combination of insert picture/image etc and I cannot find something close to what I am looking for. This could be my searching skills, but I did spend quite a lot of time searching.

    I have attached a sample template. There are picture filenames in Column's A, B and Q, but in only a few rows (most rows are empty). In this template I have only added filenames as far down as Row 142, but this will extend to several thousand eventually.

    if it makes it easier to implement, I can add a dummy character in the blank cells between image name so that a loop function works. There would be no corresponding file in the directory for this, but could we not add a command to continue (On error resume ?)

    Many thanks in advance, all assistance appreciated
    Attached Files Attached Files

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Insert multiple pictures

    Here's Andy's post

  7. #7
    Registered User
    Join Date
    07-27-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Insert multiple pictures

    Hi Roy,

    Thanks for the link. Yes I found that post, but I believe that code inserts all pictures in a particular directory into a particular range in Excel. Please correct me if I'm wrong.

    I am looking for code that looks for filenames in a particular range in excel, then goes to a particular directory and inserts the corresponding picture into the same cell in excel.

  8. #8
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303

    Re: Insert multiple pictures

    Hi there!

    Not properly tested, but here is a pointer on what you should do :

    Please Login or Register  to view this content.

    To use FileSystemObject, you will need to include the reference of Microsoft.Scripting.Runtime in your VBA Project. You can do that by going to tools-->References --> Microsoft.Scripting.Runtime.

    HTH,
    Vikas

  9. #9
    Registered User
    Join Date
    07-27-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Insert multiple pictures

    Hi Vikas,

    Thankyou so much for taking the time to help me with this. My VB knowledge is very limited but it does seem to do what I need.

    However I am getting a error in line

    Set colA = myPath & sht.Range("A1").Offset(rwCounter - 1, 0)

    The error is "Object Required"

    Any ideas? does it have something to do with "sht.Range("A1")" I don't understand what this line is doing. Is it just saying start looking for filenames in cell A1, then continue down?

    I did make sure the path to the directory containing the images was correct. I also tried the code with a filename in cell A1.

    Thankyou again for your help, its much appreciated

  10. #10
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303

    Re: Insert multiple pictures

    sht is the object of the worksheet in which you are trying to find the image paths. What i am trying to achieve with this line is to look into cells which are at a certain distance from A1. For example sht.Range("A1").offset(1,0) would give me the cell which is at the same column but a row below the A1, which is indeed A2. Similarly .offset(0,1) would give me B1 and so on.

    The error may be for two reasons in your code. Either sht is not initialized, or rwCounter is 1. Check in the immediate window what are the values coming for these two.

    Thanks,
    Vikas

  11. #11
    Registered User
    Join Date
    09-26-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Insert multiple pictures

    hi i am looking for something similar, i tried this, but it gives error - "user-defined type not defined"

  12. #12
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303

    Re: Insert multiple pictures

    Make sure you have added the reference to Microsoft.Scripting.Runtime.

    Here it shows how to do it : http://excelnoob.blogspot.in/2011/09...ba-editor.html

    Thanks,
    Vikas

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1