Solved: Capture picture, save to folder and create hyperlink in cell?

[Simon Lloyd]

Hi all, is it possible to, on the click of a command button take a picture using a webcam (or other such device) name it, save it to a folder and create a hyperlink to it in a cell?

My intention is that when a new employee is recruited and a userform is filled in (already created that) when the command button to commit the data is clicked i would like to be able to automatically take a picture of the employee save the picture as lets say TextBox1.Value store it in a folder called "Employee ID" (if it's not present create it) and then create a hyperlink to it in Sheets("Staff Details").Range("A1") (ficticious location as the location will be determined by the userform finding last entry and and using Rng.Offset to place the hyperlink in the cell).

This way ID badges can be made easily plus when employee records are called up a face will be put to the name (not only important for payment to the employee as they will mostly be agency workers but for security and legal reasons).

Any thoughts and ideas on how to accomplish this?

Regards,
Simon

[Ken Puls]

I am 100% sure that this is possible, but I'm also 100% sure that I can't tell you how. LOL! Does that help?

Reason I say it's possible is that there are many applications I've seen in the wild that will snap a pic and return it to a userform. The questions, though, comes down to:
-Does the camera expose its object model to code?
-Is there an API that you can use to trigger it?
-What format is the default output?

Once you've got all that, then things might be easy. Copying/renaming/deleting files is really easy in VB(A), so you'd just need to find the most recent picture and move/rename it to what you want. I'm sure that you'd be able to come across code on the internet to convert and scale your picture from one format to another. And from there it's importing it into your userform which again should be do-able.

The part that gives me most cause for concern is sending commands to your camera. Now... if you had a camera that always snapped a pic and dumped it immediately into a directory... Then you could just manually tigger your macro after you'd taken the picture. Copy it to a new folder, rename it, delete the original, and you're always clean for next time. Then it's the image conversion etc...

Hopefully some of this helps a bit conceptually, if nothing else.

[Simon Lloyd]

Ken thanks for the analysis, you can see what i'm getting at though, it doesn't need to be available to the userform, its just if she fills in the userform that records an employees details i wanted on close to be able to snap the employee, save it as employee name perhaps in a designated folder and then deposit a hyperlink for it at the end of the employee record.

Not being very experienced in VBA i wouldnt have the foggiest idea on manipulating applications outside of Excel, the hyperlink i could probably work out (but on second thoughts i only want the hyperlink to call the picture in to excel temporary for printing or inclusion in a report). I have no idea what API is or means at a guess Advanced Program Interface, but that still doesnt tell me anything.

I will trawl the internet for any other solutions.....i asked the question here first because this is where the experts hang out! LOL i don't class myself in that category but you never know rubbing shoulders and some may rub off!

Regards,
Simon

[Bob Phillips]

I would have thought it simplest just to buy a product that does all of that. Sometimes it is not worth re-inventing the wheel.

[Simon Lloyd]

Bob, i agree but unless it's really cheap funds will not allow,Susan gave up her full time job to allow me to take on a different position at work, so running her own business seems to be the only option to give us a little financial freedom!, i have been trawling the net for such software but haven't come across any as yet.

Regards,
Simon

[mdmackillop]

Hi Simon
I put together a utility here for handling photos in Excel. There might be some bits of it you can use.

[Simon Lloyd]

Malcom, it's greatly appreciated, as i agreed with Bob, i believe the relevant software should be purchased, in fact i believe everyone should be paid for their efforts as it's nice to be appreciated but appreciation doesn't feed you!.....that aside if we could afford it it would save me all the trouble of putting together a package she can use now and in the future.....although it won't have the standards of folk on here i will post the final version for all to see and use if they wish!

Regards,
Simon

[SamT]

If you have a dedicated ID camera that is always connected to the same computor port, its' internal folder should be available to VBA as its' location and name won't change..

Most cameras save in JPEG format and there is a JPEG filter for Excel.

You would have to take the picture before triggering the Insert method.
[VBA]ActiveSheet.Pictures.Insert("D:\Desktop\167335230.jpg").Select[/VBA]
Is the macro I just recorded.

You would have to set a Var to the path and find the picture name somehow.

[mdmackillop]

You would have to set a Var to the path and find the picture name somehow.

You could use DateCreated to determine the newest file in the folder.

[Simon Lloyd]

Linked to http://vbaexpress.com/forum/showthread.php?t=12567 in a way

Regards,
Simon

[Ken Puls]

I would have thought it simplest just to buy a product that does all of that. Sometimes it is not worth re-inventing the wheel.

True enough, but this does seem pretty interesting.

[Bob Phillips]

True enough, but this does seem pretty interesting.

Maybe for a sad old git like you , but I have too many other things to do rather than to spend time on something that someone else can invariably do better with appropriate tools.

Fancy having a crack at an Excel/VBA file compressor/de-compressor?

[Ken Puls]

Old? Ouch! LOL!

I'll ping you offline about the other.

[Simon Lloyd]

Marking this thread solved as i am following an alternative route here http://vbaexpress.com/forum/showthread.php?t=12567

Thanks to all those that replied!

Regards,
Simon