Hyperlink to Photos When Moving Files

  • Forgive me if I'm in the wrong place - most of the posts I've read here are much more difficult issues than I face, but at the moment I'm somewhat bamboozled.

    I do not know or use VBA at all, so again if I'm in the wrong place please forgive me.

    I create inspection reports for clients - large spreadsheets which contain as many as 2000 items on various worksheets. I need to be able to hyperlink photos to multiple items - some items have more than 1 photo - and I know how to create the links. The problem is that I then have to move / copy these files to various locations in order to deliver to the client electronically, and that breaks the hyperlink. I've tried "embedding" the photos, but that creates a huge file size - and they're not easily deleted from the file.

    Any help at all would be appreciated, or if you could point in the right direction, please?

  • Re: Hyperlink to Photos When Moving Files


    If you want to send 2000 images electronically, then you are bound to run into some problems... I suggest you prepare a folder with the excel file and subfolders with the images. Then zip the whole lot and upload it somewhere such as google documents from where your client can dowload it.


    Now to the links part: Since hyperlinks are explicit, you can solve that with vba using the selection_change worksheet event. A smart way to link the images would be to create image paths and names which depend on the address of the cel in which the "link" is. This would avoid having to code separately for each cell.
    I could help you code that, but I would have to know a bit more about your data strucutre and how you would decide to store the images.


    Cheers


    Attila

  • Re: Hyperlink to Photos When Moving Files


    Attila,

    Thank you for the response.

    Basically I am creating inspection reports for a client, resulting from inspections done by my company. Excel is NOT the best way to do this, but at the moment the only method I can use.

    The workbook consists of multiple pages, divided by area of inspection. Each line (row) is a particular item - and all failed items have at least 1 photo, some more than 1. When I produce these reports for the client, they get the photos as well - and I try to include a method to view the photo from the report itself (Read the item, "Click" and see the photo. The last set of these reports I did, I inserted the photos as objects, shown as icons, and it worked well - but made a HUGE file size for the workbook.


    Hyperlinks would work better - but I have to eventually move these files (reports, photos, etc) to both an online file share and to a couple of CD's for the client's use - and then the links break.

    This particular report will include over 1000 photos.

    Have never used - and do not know - VBA.

    Any help is greatly appreciated!

  • Re: Hyperlink to Photos When Moving Files


    Thanks for the extra explanation WMayo, but I will require more specific information to be able to write you some code.
    Could you possibly upload a sample workbook, so that I can see the structure of the data included?


    Cheers


    Attila

  • Re: Hyperlink to Photos When Moving Files


    Attila,

    I have attached a single worksheet from the file - hopefully this enough but if not let me know and I'll upload the entire file (though it does have some propietary info in it).

  • Re: Hyperlink to Photos When Moving Files


    Hi WMayo,


    Are you sure you attached the file? I wouldn't upload any confidential data... you never know...
    A few lines of fictive data which show different types of content should suffice :)


    Cheers

  • Re: Hyperlink to Photos When Moving Files


    What information do you usually use to name your images? Instead of having to code for 1000 different photographs it would be easier if they were linked to some information contained in certain cells.
    Just to double check: the structure of the sample file (position of data, number of columns) is identical to your main file correct?


    Cheers

  • Re: Hyperlink to Photos When Moving Files


    The photos are named by the "Equipment Tag Number" - the combination of the 1st 3 columns. This is done for easy identification of the item if viewing without the excel report handy (every item in the inspection is tagged with this number).

    Yes - the structure is identical to the main file, except that there actually 3 different worksheets in the main file - these are fault listings and are divided into 3 different categories of fault, with each fault type listed on a different worksheet. The structure of each worksheet, however, is identical to the sample I sent you.

    The photos, by the way, are split into various subfolders - by area of inspection. Makes it difficult to create the links, etc - but is done for ease of location by the client, again especially when not accompanied by the excel report. So the entire file structure for all of the reports (there are several, but this is the only one which gets linked photos) is similar to this:

    Main Folder

    --- Report1.xlsx
    --- Report2.xlsx
    --- Photos Folder
    --- Area1 Folder
    --- Photo AA-001.jpg
    --- Photo AA-001.jpg
    --- Area 2 Folder
    --- Photo BB-001.jpg
    --- Photo BB-002.jpg

    etc

  • Re: Hyperlink to Photos When Moving Files


    Try this:


    In your workbook open the VBE (Alt+F11) and paste this into each of the worksheet modules which require this functionality. Then save your workbook as *.xlsm /Macro enabled workbook. Make sure that the folder names in red and the file extension for the images also in red are correct.


    I didn't find the "Equipment Tag Number" column so I used "Equipment Circuit ID". if this is incorrect you can change the Columns in the range objects (in green)


    Regards


    Attila

  • Re: Hyperlink to Photos When Moving Files


    See 1 problem....... when naming the folder in the code - the folders are different based on the area.... For example, all photos with a tag number (the tag numbers are in columns A, B, & C - when I changed the column in your code I wrote "A"+"B"+"C", will that work?) beginning with "BS" are in a folder named "Bulk Storage Area (BS)", all photos beginning with "DF" are in a folder called "Drill Floor (DF)", etc.

  • Re: Hyperlink to Photos When Moving Files


    Where can I find these tag numbers or how can they be deduced - the code should be able to put together the name of the image and its path using the data in the sheet. How many prefixes for images do you have / how many different folders?


    Cheers

  • Re: Hyperlink to Photos When Moving Files


    Ok I posted that into each module under VBA (not sure I did correctly - remember I've NEVER used VBA) and saved as an .xlsm. I then closed the file and re-opened - nothing has changed.

    Forgive me - I'm completely ignorant on VBA. Fairly savvy on Excel, but always the old "manual" type of functions.

    Also, given that there are multiple folders with photos (19 in total - almost 1000 photos altogether) - would I repeat the formula for EACH folder 3 times (once for A, once for B, once for C)?

    Appreciate your patience with me - this is something I've been screwing with for over a week now and I certainly appreciate the help!

  • Re: Hyperlink to Photos When Moving Files


    No you don't need to repeat the code that many times... There are ways around that. Now the code would work, BUT it doesn't include all the details for 19 folders...


    Could you supply the names of these folders and the conditions that need to be met for the image to be in that folder? (suffixes / prefixes in the image name, specific characters in the Equipment ID etc)


    Regards


    Attila

  • Re: Hyperlink to Photos When Moving Files


    Attila,

    The Equipment tag number are located in columns A-C of the worksheet (normally a single cell, but separated for extracting number by formula)

    Folder names are as follows: (note that the path is \mycomputer\local\desktop\Foldername\Photos\Reduced File Size\Foldername\Files - but that won't be the path when I upload these to our file-sharing website and copy them to CD's for the client)

    Acetylene Room (AR) Ballast Pump Room (BP) Ballast Pump Room Vent (BPV) Battery Locker 1 (BL1) Battery Locker 2 (BL2) Bulk Storage Area (BS) Bulk Storage Vents (BSV) Drill Floor (DF) Heli Fuel System (HR) Helideck (HL) Moon Pool (MP) Mud Pit Room (MPR) Mud Process Module (MPM) Mud Reserve Tank (MRT) Mud Reserve Tank Vent (MRV) OXY Room (OXY) Paint Locker (PL) Shale Shakers (SS) Well Test Area (WT)

  • Re: Hyperlink to Photos When Moving Files


    Oh... It even said nice and clear: Equipment Tag No... Sorry about that.


    There are two subs and a function for easier reading of the code, and because there are logical steps to opening the file:
    Deciding which folder it is in (Worksheet_Change sub), getting the name of the image (Function), and opening the file (ShowImage sub).


    To show an image of a piece of equipment just select the cell under image (note: it has to be non-empty)


    Tell me how you get along with that


    Regards


    Attila


    PS: you mentioned some had more than one picture... how did you think of going around solving that?

  • Re: Hyperlink to Photos When Moving Files


    Thanks again, Attila - I'll give this a run and let you know how it worked. Probably will be tomorrow - my wife is SCREAMING for me to get the grill fired up and cook her steak....... Women - have NO sense of priorities!!!

    On the "more than one picture", I'm not sure....... In the past I've used hyperlinks - would then make the item consume more than one row and insert a hyperlink in each row - or embedded the photos in each row, as you can insert them as icons and put more than 1 per cell (just takes a LOT of tweaking on icon size).

    Seems to me there should be a MUCH easier way to do all of this - yeah, I know - ACCESS!! - but Access has never been something I've been able to master.

    My MANY thanks for all the help and patience.

  • Re: Hyperlink to Photos When Moving Files


    Attila,

    Ok - I've entered the code (on one sheet for now), created column "N", and put the word "Photo" in each cell (since you said it had to be non-empty). Saved the workbook as an .xlsm, closed and re-opened - and nothing. What am I doing wrong? Remember I'm ignorant of VBA, so if there's something I have to do to "run" the code I don't know what that is - I've looked at all the options under the menus and I don't see anything that would make the code "start", if that's necessary?

  • Re: Hyperlink to Photos When Moving Files


    You've create a column with the header "N" ?
    In the sample you uploaded the "Photo" header was the one in column N.
    Selecting a non-empty cell in column N should open the image. I have assumed following organisation:
    Main Folder XYZ (name & path Irrelevant)
    --Excel File
    --Subfolder 1
    ----Image 1
    ----Image 2
    --Subfolder 2
    ----Image 1
    ----Image 2
    --Subfolder 3
    ----Image 1
    ----Image 2
    --Subfolder 4
    ----Image 1
    ----Image 2

    Regards

    Attila

    Edit: As long as the Excel File is at the same level of the organisational hierarchy as the folders containing the images there should not be any problem. (Thisworkbook.path returns the path of the workbook, giving you the flexibility required of moving the workbook around. Please also check the file extension of your images, I have assumed that they are *.jpg

  • Re: Hyperlink to Photos When Moving Files


    Yes, that's the file structure.

    I created the column "N" because in the version of the excel workbook I'm trying this with there wasn't a column "N". I forgot that the sample I sent you already had it, but I had created that just for your example.

    Wait - I think I see the problem, with my folder structure - there are actually 2 sub-folders under the sub-folder (\mainfolder\photos\original\subfolder and \mainfolder\photos\reduced size\subfolder). Let me fix that structure and try again.