Hyperlinks to Folder/Files

[chef]

I wonder if this is possible and would appreciate any guidance

I have case reference numbers ( approx 1500 ) in column B and this reference number has a unique folder with the same reference number that contains various documents in path Q/Employment Tracker/CMS

Would it be possible to click on any cell reference/case number in any row in Column B and then that would open up folder or at least highlight the corresponding folder against the reference?

Many thanks

 

[John_w]

Try this:

Sub Create_Hyperlinks()
    Dim cell As Range
    For Each cell In Range("B2", Cells(Rows.Count, 2).End(xlUp))
        cell.Parent.Hyperlinks.Add Anchor:=cell, Address:="Q:\Employment Tracker\CMS\" & cell.Value
    Next
End Sub

 

[chef]

Thank you so so much for taking the time with this. Just awesome.

I would never in a million years fathomed this out and I'm really grateful for your help as it will help so many users at work.

kind regards

 

[chef]

Please forgive me for perhaps not applying this correct.

I started a new spreadsheet to try this and I have the case references starting at Row7 in column B
I put your code in the worksheet by opening up view code on the CMS tab in workbook and pasting directly there.

So I have managed to get the hyperlinks to work perfect and opens up the unique reference folder in the path but for just Rows B7/B8
Whenever I put a new reference into further rows in column B9 onwards, nothing links and I try clicking and click and hold as previous
I can copy the first two rows format etc down but then I have to manually edit the hyperlink path with each individual cell reference.

Is this something I am doing wrong as I am not understanding why it would work for first 2 records in row B7/B8 and actually show the link when you hover over but nothing when new entries are input.
Sorry to be a pain as this is so helpful and its my lack of VBA knowledge that is not helping.

Any nudge in the right direction would be greatly appreciated

regards

 

[John_w]

The macro goes in a standard module, not a worksheet module, and is designed to be run manually. When run it creates a hyperlink to the case reference folder for each cell from B2 to the last cell in column B with data. The user can then click one of the hyperlinks and that folder will be opened in File Explorer. If you add or change a case reference number the macro would have to be run again to recreate the hyperlinks. One problem with using hyperlinks is that multiple File Explorer windows for the same folder are opened if the user clicks the same hyperlink again.

Here is a better, automatic method which doesn't use hyperlinks and ensures that only 1 File Explorer window is opened for a particular case reference when the user selects a cell from B7:B<last> containing a value.

Delete the previous code in the CMS worksheet module and insert this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim mainFolder As String, caseFolder As String
    
    mainFolder = "Q:\Employment Tracker\CMS\"
    
    If Right(mainFolder, 1) <> "\" Then mainFolder = mainFolder & "\"
    
    If Target.Column = 2 And Target.Row >= 7 And Target.Count = 1 And Target.Value <> "" Then
        caseFolder = mainFolder & Target.Value
        If Dir(caseFolder, vbDirectory) <> vbNullString Then
            Open_File_Explorer caseFolder
        Else
            MsgBox "The folder for Case Reference " & Target.Value & " doesn't exist in " & mainFolder
        End If
    End If

End Sub

Put this code in a new standard module (e.g. Module1):

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As LongPtr) As LongPtr
#Else
    Private Declare Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As Long) As Long
#End If


Public Sub Open_File_Explorer(folder As String)

    'Open File Explorer at the specified folder if not already open, otherwise bring it to the foreground

    Static Sh As Object
    Dim ShWindow As Object
    Dim i As Variant
    Dim foundWindow As Boolean
        
    If Right(folder, 1) = "\" Then folder = Left(folder, Len(folder) - 1) 'remove trailing "\"
    
    If Sh Is Nothing Then Set Sh = CreateObject("Shell.Application")
    foundWindow = False
    i = 0
    While i < Sh.Windows.Count And Not foundWindow
        Set ShWindow = Sh.Windows(i)
        If StrComp("file:///" & Replace(folder, "\", "/"), ShWindow.LocationUrl, vbTextCompare) = 0 Then foundWindow = True
        i = i + 1
    Wend

    If Not foundWindow Then
        Shell "explorer.exe " & folder, vbNormalFocus
    Else
        SetForegroundWindow ShWindow.hwnd
    End If

End Sub

 

[chef]

wow and wow !!!!

First of all, thank you so much for taking the time to do this and this is an amazing piece of work and to me genius.
I'm not going to pretend I understand it but the fact I have a solution is amazing and is going to help so so much and offers even more flexibility.

I'm just so grateful...
Have a great weekend