Use ADO to retrieve images in an excel document

[JasonEnsor]

Hi guys,

I am building a document generator of sorts that uses MS Word as the front end with a userform and Excel as the backend for some of the data. Due to the way the data is laid out I am able to query the data using ADO. What I am wondering is if it is possible to have images stored in cells in excel that I could then retrieve using ADO. If so any hints would be great.

At the moment I am considering storing the images on disk and in the ExcelDB doc having the location stored. Then when I retrieve the data load the selected image based on the filename. This however does open up potential issues with if the file is moved etc

Would appreciate any advice etc.

Regards

J.

 

[mintjulep]

Excel really isn't intended to be a file storage platform. I can't imagine that being efficient.

Store your files on ..... a file system.

Manage permissions to limit the opportunities for people to muck things up.

 

[JasonEnsor]

If I had full control over the users system I wouldn’t be coding this using word, excel and VBA. I’d have made it a fully contained .net app however like most systems I end up working on or building I am at the mercy of the user. The file system with permissions would have been ideal if I could control the system fully. I think I am just gonna have to go file system and put a disclaimer that if they delete/move images then the system will fall over

Regards

J.

 

[Andrzejek]

if they delete/move images then the system will fall over

Isn't that what error handling is for...
"if they delete/move images then" pop them a message: "Hey, someone messed it up!"

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[JasonEnsor]

Yeah that is what error handling is for, however as I am building it for a client, who has a client who is trying to sell to many clients I am trying to prevent as many issues as possible as there is a big chain for issues to be passed back along. Ideally my error handling on this would involve a wooden stick hitting there hands everytime they did something stupid. The user should never need to touch the images folder but I can almost guarantee that someone will. Makes me think of looking at a windows 95 pc years ago where some of the core dlls had been deleted by a user as they “didn’t need them”.

Regards

J.

 

[Andrzejek]

a wooden stick hitting there hands everytime they did something stupid" - oh, one can dream....

I had a user who re-organized the files on his HD: all exe's in one folder, all dll's in another folder, all ocx's in another folder, etc. Makes sense, right? And for some reason his computer stopped working.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Dan_W]

I am very familiar with the challenges you're facing, so you have my sympathies.

With all the usual caveats re: 'Excel is not a database/file system', etc out of the way, one potential way of storing the images in excel would be to convert the image files into Base64, store the B64 in a cell or more if necessary, and then reconstitute it into a file and add that file as an image into your word document. Of course, it depends on how big this images are, because storing a B64 string will consume more diskspace than storing it in its original form. The benefit to this approach is that you can still use ADO to extract the B64 string.

WOuld that work?

 

[strongm]

> I can almost guarantee that someone will

They may be just as likely to delete the ExcelDB file (after all, they think they are working in word, so what on earth is that pesky Excel file doing there, I'll get rid of that ...)