Insert/format Picture From Folder

Hi,

I am using the code below to insert and resize images in excel. I was wondering is it possible to set up this macro so that I can just provide the directory and then have it so that just a random image is inserted rather than specifying an jpg number. The reason i need this is because the numbers in the folder range from 00000 to upto 20000 however not every folder has the full range of images i.e. Z:\Singapore\Singapore Turf\Singapore Turf Club 2\QEII Cup 2006\QEII Cup 2006_VD\Millenium Copthorne International\TVGI Race Name Text\ may just contain one image and that would be image number 03965.

The other thing I would like to do is to have the name of the las 2 subfolders inserted into the cell above the image so for the example below you would have:

Millenium Copthorne International\TVGI Race Name Text
Picture

Millenium Copthorne International\TVGI Race Name Logo
Picture

Is this possible?

Please Login or Register  to view this content.

Maybe something like this?

Please Login or Register  to view this content.

It crashes at end sub

Please Login or Register  to view this content.

Sorry ... I pulled that out of something I already had and removed code you didn't need.

Along the way, I pulled out a line of code you did need.

Please Login or Register  to view this content.

Add the 1 line of code indicated above.

Now it crashes on "ChDir myDrive".

Just looking at the code how will this help me insert a random image?
Will it not just count how many Jpgs there are in the directory? Or am I missing something?

Probably because you don't have a valid drive name in cell C1.

Just delete these 2 lines of code. I don't think they are necessary anyway.

Please Login or Register  to view this content.

Ok that worked.....but it inserts every image in the folder into the sheet I just want one image and the name of the las 2 sub directories above the image.

bump bump bump

Prehaps simething to search the Min number in the folder and then just select that rather than a random number?

Sorry Vlad, I was trying to earn some money today.

Please Login or Register  to view this content.

No problem we all need to eat (and but expensive useless crap for our significant others like diamonds, hand bags etc....such a waste )

Heres what I have so far it crashes on the "Call TestInsertPictureInRange(.FoundFiles(rndValue)" line I have highlighted it in red below

Please Login or Register  to view this content.

What i have to say cannot be written in Latin font.

There are 2 reasons this fails:
Call TestInsertPictureInRange(.FoundFiles(rndValue)

1. it is missing a parenthesis at the end

2. the name of your subroutine is not "TestInsertPictureInRange"; rather it is "InsertPictureInRange "

I think if you replace that red line with this one

InsertPictureInRange .FoundFiles(rndValue)

it will work. Or, you can try this:
Call InsertPictureInRange(.FoundFiles(rndValue))

Either one should work.

I have tried both and still I get an error on the same line

I see the problem now.

In the code you posted originally, I could not read all the way to the right of the sub call you were making. But, I assumed that this was your code, so you would know how to use it.

My mistake, I should have just looked below at the what the function required for input. Duh! And, I should never make assumptions about what another person knows.

This is the sub's declaration statement, right?
Sub InsertPictureInRange(PictureFileName As String, TargetCells As Range)

So, you need to pass it both the name of the picture file and the range where you want the picture to go.

So, this is what you need to use:
InsertPictureInRange .FoundFiles(rndValue), Range("B5:D10")

or
Call InsertPictureInRange(.FoundFiles(rndValue), Range("B5:D10"))

Yep the only VBA I kow is what I find on the net and stuff kind people like you help me out with...but I'm learning....just VERY slowly


P.S. That worked like a charm and will save me a great deal of time

Is there a way to moify that code to emable me to insert images from multiple directories?

I have tried changing it but was only able to insert multiples of the same image i.e. the first listed directory

Please Login or Register  to view this content.

Change "SearchSubFolders" from False to True

Please Login or Register  to view this content.

I have tried that and it still does not give me one random image from each sub folder, just the same image pasted in multiple times

OK. Did not understand that you wanted one random photo from each subfolder.

Can do this, but not with the FileSearch object (which is used in the code you have now). Will need to use instead the FileSystemObject and some VBScript code. I have this (somewhere) and have posted it to this forum in the past.

Will not have time to work on this until about 10 hours from now. If you want to try finding it yourself, do a search in this forum for FileSystemObject.

I will have a look around thanks

Vlad, I found it. Am working to modify it for your needs, as I now understand them. Back in a few.

OK, here is code that starts in a certain directory, searches all subfolders, if the subfolder has any jpegs in it, returns a random jpeg from the subfolder.

Right now, it is set up only to write the folder and randomly selected file name in cells in a workbook. You will need to merge this into your routine to paste the jpeg into your workbook.

Please Login or Register  to view this content.

Thanks I will have a play around with that will let me know how I go

BTW what your mailing address I think I owe you a bottle of wine for you help

Vlad, I definitely appreciate the thought. But, I am trying to cut back. Also, quite honestly, I learn a lot doing these projects; and I consider that sufficient pay-back.

Hi, I have been trying to merge the codes but with limited VBA skills I havent had much luck. I have changed the data string in column A to be the full jpeg directory (which I am fairly pleased with "one small step for Vlad...). But I dont know how to change the code to tell it to look up the directories listed in column A then call the insertpicture macro to insert the images in ranges C3:F13, C17:F27, C31:F41 and so on (also do I need to list all the ranges of can the macro work out a range pattern and then insert images accordingly.

Sorry to be a pain

Please Login or Register  to view this content.

Vlad, I am happy to help. Would have done more sooner if I thought you needed it, man!

A few questions before I know exactly what to do (some of these might be answered when I look at earlier code you posted; but, ...).

look up the directories listed in column A

What is the first cell in column A that will have a directory listed in it? Is the last cell in column A "fixed" (always the same number of directories listed); or, do I need to determine this? Should I work down the column to the first blank cell? Or, ignore blanks and keep going until the last entry in column A?

One (random) picture from each directory? Or one from each directory and each sub-directory?

insert the images in ranges C3:F13, C17:F27, C31:F41

Continue this diagonal pattern until when? Until we run out of pictures? Until we run out of columns? Or, whichever happens first?

I am using the code you provided to populate column A.

The first cell in Column A is A1. The cells in column A Will be variable so checking for the first blank cell will indicate the last cell with data in it.

It would be one random picture from each sub directory. Just let me explain a bit more, all the directories where pictures need to be inserted from are laready listed in column A (thanks to the macro you provided) these pictures just need to be inserted in to the coplaces on the spread sheet.


The correct places to insert pics are as follows
Insert pic to C3:F13 next pic insert to I3:L13 then C17:F27 followed by I17:L27 and so on till we have one example pic from each folder.

hope I have explained it well

OK. I think I understand now. Sorry about the confusion. When I read:

look up the directories listed in column A

I took that too literally. Better to ask and have you think I might be stupid than for me to run off and do write code that proves I am stupid.

Working ...

I will be back in a few.

This seems to work for me ... this should replace EVERYTHING you have now.

Please Login or Register  to view this content.

If I wasn't married...Thanks alot it works perfectly.