Solved: Creating File Path for Hyperlink

[GMan]

I want to add a hyperlink to the column A of my workbook that links to the folder related to the request. My issue is that I know only 99% of the directory path for the Hyperlink.

For example:
Cell A2 would have the value of "FM1849" and would link to "C:\CSR Work\CC18xx\CC1849 - Update Report"

Cell A3 would have the value of "FM1850" and would link to "C:\CSR Work\CC18xx\CC1850 - Change Application Processing"

Currently I have coded:
[vba]
iRow = 2
Do While AllCSRs.Cells(iRow, 1) > ""
With AllCSRs
.Hyperlinks.Add anchor:=Range("A" & iRow), _
Address:="C:\CSR Work\" & _
Left(AllCSRs.Cells(iRow, 1), 4) & _
"xx\" & AllCSRs.Cells(iRow, 1) & _
" - Update Report"
End With
iRow = iRow + 1
Loop
[/vba]

My code would work for the A2 cell but will have the incorrect description for the A3 cell. Is there a way that I can make the " - Update Report" dynamic and pull the information form the folder tree itself?

The end result should be a hyperlink that when clicked will open up the folder that has all the documents related to change request work.

[Kenneth Hobs]

You can make a best guess by guessing the first one found I guess. e.g.
CC1849 - Update Report
CC1849 - Initial Report
CC1849 - Annual Report

Then it would find "CC1849 - Annual Report" as the one to link. If no subfolder with CC1849 in the left part exists then you could just set it to the root folder or root folder and that part of the subfolder name with a default part like " - Initial Report". If you can this last route, then the subfolder may not exist.

Is that something like what you want?

[GMan]

That would be great. There should only be 1 occurrance of the CC1849 in the folder. So defaulting to the Root is not an issue as well as assuming the first found is correct.

[Kenneth Hobs]

Test this in a backup of your xls. Change the AllCSRs sheet name if it is not Sheet1. It may need another tweak or two.
[VBA]Sub SetMyLinks()
Dim iRow As Long
Dim rootFolder As String, subFolder As String, finalFolder As String
Dim theFolder As String, AllCSRs As Worksheet

Set AllCSRs = Worksheets("Sheet1")
rootfolder = "C:\CSR Work\"

iRow = 2
With AllCSRs
Do While Not (IsEmpty(.Range("A" & iRow)))
finalFolder = .Range("A" & iRow).Value
subFolder = Left(finalFolder, 4) & "xx\"
theFolder = BuildFolder(rootFolder, subFolder, finalFolder)
Debug.Print theFolder
.Hyperlinks.Add anchor:=.Range("A" & iRow), Address:=theFolder
iRow = iRow + 1
Loop
End With
End Sub

Function BuildFolder(rFolder As String, sFolder As String, _
fFolder) As String
Dim a, f As String
f = Dir(rFolder & sFolder & fFolder & "*", vbDirectory)
If f = "" Then
BuildFolder = rFolder & sFolder
Else
BuildFolder = rFolder & sFolder & f
End If
End Function
[/VBA]

[GMan]

Works great. Exactly what I have been attempting to do. It is nice to see you kept some of my original code. I often wonder if I am writing intelligent code or if I am stuck in the COBOL way of thinking.

I learn a little more with every macro, every question, every experience.

Thanks for the help!