Loop through objects in Excel 1

[nberryman]

I have a customer that has used Excel to store pictures for their Staff Photo Boards. As you would expect the file sizes are huge so I have creates an Access database to do the job instead.

My question is, is it possible to loop through and save each object (pictures in this case) or will it have to be done by hand.

I don't care what the filenames are as these can be changed as we move on.

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[kevinclark]

Hi Neil

Just a suggestion. As an alternative could you store the pictures files in a directory and then store the path / file name as a string in the database. Add an OLE / picture box to the form and then reference / load the picture as per record selected ? May be even easier to implement and maintain ?

 

[nberryman]

Cheers Kevin, that exactly what I have done and it worked a treat.

Just have to get several hundrad photos out of Excel so i can use them

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[dwilson01]

This may give you enough to start with. What it does is cycle through each picture (shape) in the active worksheet and copies it to the clipboard. I'm not familiar enough with using external application to know how to paste the clipboard into an Access table for instance, but maybe you can do that part of the program.

Sub LoopingObjects()

'This starts Microsfot Access,
'I would think you could open a particular table
'allowing you to paste pictures into, maybe not?
Application.ActivateMicrosoftApp xlMicrosoftAccess

For Each shp In ActiveSheet.Shapes
shp.Copy
'Add code to paste pictures
Next

End Sub

Hope this helps

 

[kevinclark]

Hi Neil

Good luck trying to find sample code that will extract imbeded pictures from Excel. I will keep my eyes on this post to see if anyone has some samples but in the mean time - One thing you can do is use the Save AS HTML option and it will extract the images for you into a directory.

 

[nberryman]

Kevin your a star, I always impress on anyone I train to seek the easiest solution and try not to think too deep.

Note to self. Practise what you preach

For anyone else watching this saving as HTML gets all the picture out into its own folder

Cheers Neil Berryman
IT Trainer
neil_berryman@btopenworld.com