Hyperlink to Photos When Moving Files

WMayo · Jun 5th 2011

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?

Attilas · Jun 5th 2011

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

WMayo · Jun 5th 2011

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!

Attilas · Jun 5th 2011

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

WMayo · Jun 5th 2011

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).

Attilas · Jun 5th 2011

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

WMayo · Jun 5th 2011

Re: Hyperlink to Photos When Moving Files

Well I THOUGHT I had, but obviously not. Should be here now? I have removed the proprietary data, though it's merely a client name & logo.

Attilas · Jun 5th 2011

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

WMayo · Jun 5th 2011

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

Attilas · Jun 5th 2011

Re: Hyperlink to Photos When Moving Files

Try this:

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> Range("N1").Column Or IsEmpty(Target) Then Exit Sub
    Select Case Range("J" & Target.Row).Value
            Case "A"
            ThisWorkbook.FollowHyperlink ThisWorkbook.Path & "\[COLOR="#8b0000"]Area1 Folder[/COLOR]\" & Range("[COLOR="green"]E[/COLOR]" & Target.Row).Value & ".[COLOR="#8b0000"]jpg[/COLOR]"
            Case "B"
            ThisWorkbook.FollowHyperlink ThisWorkbook.Path & "\[COLOR="#8b0000"]Area2 Folder[/COLOR]\" & Range("[COLOR="green"]E[/COLOR]" & Target.Row).Value & ".[COLOR="#8b0000"]jpg[/COLOR]"
            Case "C"
            ThisWorkbook.FollowHyperlink ThisWorkbook.Path & "\[COLOR="#8b0000"]Area3 Folder[/COLOR]\" & Range("[COLOR="green"]E[/COLOR]" & Target.Row).Value & ".[COLOR="#8b0000"]jpg[/COLOR]"
    End Select
End Sub

Display More

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

WMayo · Jun 5th 2011

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.

Attilas · Jun 5th 2011

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

WMayo · Jun 5th 2011

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!

Attilas · Jun 5th 2011

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

WMayo · Jun 5th 2011

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)

Attilas · Jun 5th 2011

Re: Hyperlink to Photos When Moving Files

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

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> Range("N1").Column Or IsEmpty(Target) Then Exit Sub
    Select Case Range("A" & Target.Row).Value
            Case "AR"
            ShowImage "Acetylene Room", Target
            Case "BP"
            ShowImage "Ballast Pump Room", Target
            Case "BPV"
            ShowImage "Ballast Pump Room Vent", Target
            Case "BL1"
            ShowImage "Battery Locker 1", Target
            Case "BL2"
            ShowImage "Battery Locker 2", Target
            Case "BS"
            ShowImage "Bulk Storage Area", Target
            Case "BSV"
            ShowImage "Bulk Storage Area Vents", Target
            Case "DF"
            ShowImage "Drill Floor", Target
            Case "HR"
            ShowImage "Heli Fuel System", Target
            Case "HL"
            ShowImage "Helideck", Target
            Case "MP"
            ShowImage "Moon Pool", Target
            Case "MPR"
            ShowImage "Mud Pit Room", Target
            Case "MPM"
            ShowImage "Mud Process Module", Target
            Case "MRT"
            ShowImage "Mud Reserve Tank", Target
            Case "MRV"
            ShowImage "Mud Reserve Tank Vent", Target
            Case "OXY"
            ShowImage "OXY Room", Target
            Case "PL"
            ShowImage "Paint Locker", Target
            Case "SS"
            ShowImage "Shale Shakers", Target
            Case "WT"
            ShowImage "Well Test Area", Target
    End Select
End Sub


Sub ShowImage(Folder As String, Target As Range)
    ThisWorkbook.FollowHyperlink ThisWorkbook.Path & "\" & Folder & "\" & ImageName(Target)
End Sub


Function ImageName(Target As Range) As String
    TagNo = Range("A" & Target.Row).Value & Range("B" & Target.Row).Value & Range("C" & Target.Row).Value & ".jpg"
End Function

Display More

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?

WMayo · Jun 5th 2011

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.

WMayo · Jun 6th 2011

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?

Attilas · Jun 6th 2011

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

WMayo · Jun 6th 2011

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.

Hyperlink to Photos When Moving Files

Participate now!

Share