Macro or VB Code to link to image (1 Viewer)

carriepedersen

Registered User.
Local time
Today, 15:02
Joined
Jun 24, 2011
Messages
15
Hello there

I'm having troubles trying to find exactly what it is that I need. I'm trying to create a product database that will link to images that we have saved on our network. I don't want to embed the images because it might slow it down. What I would like to do is possibly create a hyperlink or something that someone could click and the image would pop up based on a line item. I have the UPC number stored in the table and then some other info, but I would like to look for the image by UPC and put a hyperlink possibly titled "Image" and they could click it and it will open and if there is no image for that UPC it would stay blank. One big problem I have is right now I have over 1,000 images and they are formatted differently then my UPC. Some have three or four zeros in front. Any ideas on how this can be accomplished? Would it be better to do this in Excel? I'm sorry I have tried in both Access and Excel and still cannot come up with what I need. Note all of my files are saved as "png".

Thank you for your time!

Carrie
 

penguino29

Registered User.
Local time
Today, 20:02
Joined
Dec 23, 2010
Messages
52
Hi Carrie,

Interesting project you have there. Your success depends vastly on the differences of the filenames vs. the UPC code that you already have in excel or in an access table.

Firstly I hope all the images are in the same directory, if not copy them to a single location for backup and testing. It also helps to identify them because you will need to construct a path to refer to them and its easier if they are in the same location.

I suggest you do an analysis first and see what the differences you are experiencing.

In a command prompt, change the directory location to the image directory and run

Code:
dir /b > imgnames.txt

You now have a list of image filenames saved in the imgnames.txt file that you can import into excel and access. You can then try to sort them or put them side by side to the UPCs and see what the characteristics are, what is matching and what isn't.

If there is a pattern that you can establish for the differences between UPC sand filenames, you can perhaps write some VBA codes that build the filename path from the UPC, and repair/convert any exceptions/differences on the fly. This is a non destructive way and you do not need to change filenames - but your vba code must be good enough to patch the differences.

If there are not so many differences, you can build a batch file to manually rename the filenames. You can do this by using excel, setup a new column for the new names next to the existing, and build the renaming commands by concantating a command column (rename command), old filename and new filename column and save as a the batch file and execute it. This will rename the offending files that you have singled out and they can be searched via the correct UPCs

If the above two options do not cover all possibilities and your proficiency with vba is good, you can also develop vba codes which "search as you click" method. What I mean is that: for the filenames which the database cannot find a match from the UPCs, the database will launch a common dialog box and ask the user to locate the image. Once located the database will save the filename/path for the future so next time it will know the name. Over a period of time the database will build up the missing links.

Forgive me for being a bit long-winded. I hope the above is helpful to get starting.
Come back and let us know how you get on.
 

Max D

Registered User.
Local time
Today, 13:02
Joined
Jul 3, 2009
Messages
91
I have is right now I have over 1,000 images and they are formatted differently then my UPC.
You can run your imagelist with the series of search-and-replace to make it nice.

I'm trying to create a product database that will link to images that we have saved on our network.

Check our AccessImagine component - automatically does all the work for organizing network image storage. No need to write the line of code here.

But, maybe you will need to convert your images to JPEG one.
 

carriepedersen

Registered User.
Local time
Today, 15:02
Joined
Jun 24, 2011
Messages
15
Check our AccessImagine component - automatically does all the work for organizing network image storage. No need to write the line of code here.

I am having trouble accessing this. Could you possibly provide more information on this?

Thank you for your help!!

Carrie
 

Max D

Registered User.
Local time
Today, 13:02
Joined
Jul 3, 2009
Messages
91
What do you mean? AccessImagine site is not working for you or you have some questions how to use it?
 

carriepedersen

Registered User.
Local time
Today, 15:02
Joined
Jun 24, 2011
Messages
15
Hello Max

I am trying to access the link and I am at work. It is blocking it so I cannot get into the site.
 

Max D

Registered User.
Local time
Today, 13:02
Joined
Jul 3, 2009
Messages
91
Hm, thats strange you have it blocked there!

Okey, i've attached AccessImagine control to this post. And here is simple instructions. Read more, if you need, when you get normal internet.

1. Install AccessImagine.
2. Edit MS Access form in design mode.
3. Select Insert ActiveX -> AccessImagine picture control.
4. Bind AccessImagine object to text field that is to hold image filename.
5. Find StoragePath at AccessImagine object properties and specify path where are you going to store images (i.e. "c:\mypics" or "\\zeon\db_images")

External storage should work now (but it doesn't hold any images at the beginning). You can test it back & forth to check how it maintains itself & so on.

To get it working with your existing images you should convert them to JPEGs (it can be done via Photoshop automation or some third-party converter) and populate an image filename field with proper names (it can be done via query that writes UPC+'.jpg' to image field + removing leading zeroes in filenames by hand, if it is possible).
 

Attachments

  • AccessImagine110.zip
    638.3 KB · Views: 154

Users who are viewing this thread

Top Bottom