Auto Create Hyperlinks to files on **

kevin1010,

This could be automated with a macro, but the "folder structure" you have would be invalid. As in, A1.1.1 PROCESS is not a complete folder folder path. A complete folder path would look something like:

C:\A\A1.1.1 PROCESS

~tigeravatar

Originally Posted by tigeravatar

kevin1010,

This could be automated with a macro, but the "folder structure" you have would be invalid. As in, A1.1.1 PROCESS is not a complete folder folder path. A complete folder path would look something like:

C:\A\A1.1.1 PROCESS

~tigeravatar

You are correct about the complete folder path, I kind of figured we could just create a cell to auto add that in front of the path. Something like file:///...

I do not know how to make the macro though. I am by no means an expert.

kevin1010,

If you could post a sample workbook, I can create a macro to suit your needs

~tigeravatar

omg, if you can figure this out, I'll have to buy you beer someday.

This contains the first 30 lines... there are about 5600 total. Be aware there are other "main" sections. Like this one starts with Process. There is also electrical, mechanical, etc...

kevin1010,

I created a macro that should accomplish what you're looking for. Some notes about the macro:

• Change FldrCol to the column that contains the folder and file information (it was column A in the sample workook)
• Change StartRow to the row that the folder and file information starts on (it was row 3 in the sample workbook)
• Change the strBasePath to the base folder that contains the listed folders and subfolders. Be sure to include the ending \

Here's the code:

Sub CreateLinks()
    
    Dim FldrCol As String:  FldrCol = "A"
    Dim StartRow As Long:   StartRow = 3
    
    Const strBasePath As String = "C:\Test Folder\" 'Be sure to include the ending \
    
    Application.ScreenUpdating = False
    Dim rngData As Range:   Set rngData = ActiveSheet.Range(FldrCol & StartRow, Cells(Rows.Count, FldrCol).End(xlUp))
    Dim aCell As Range, strTopFldr As String, strSubFldr As String, strFullPath As String
    For Each aCell In rngData
        If aCell.Interior.ColorIndex = 37 Then
            strTopFldr = aCell.Value & "\"
        ElseIf aCell.Font.Bold = True Then
            strSubFldr = aCell.Value & "\"
        Else
            strFullPath = strBasePath & strTopFldr & strSubFldr
            Dim FileFound As Boolean:   FileFound = False
            Dim CurrentFile As String:  CurrentFile = Dir(strFullPath)
            While CurrentFile <> vbNullString And FileFound = False
                If InStr(1, CurrentFile, aCell.Value, vbTextCompare) > 0 Then
                    ActiveSheet.Hyperlinks.Add Anchor:=aCell, _
                                               Address:=strFullPath & CurrentFile, _
                                               TextToDisplay:=aCell.Value
                    FileFound = True
                End If
                CurrentFile = Dir()
            Wend
        End If
    Next aCell
    Application.ScreenUpdating = True
    
End Sub

Hope that helps,
~tigeravatar

kevin1010,

Forgot to mention something about the macro. In your sample workbook, the cell containing folder name "A1.1.1 PROCESS" was highlighted. The macro assumes all such folders are highlighted using that same color. The subfolders under that were bolded. The macro assumes all such subfolders are also bolded.

Let me know if this is not the case or if you have any questions,
~tigeravatar

Unfortunately you'd just have to change strBasePath to be whatever is needed. As an alternative, instead of using Const strBasePath As String = "C:\Test Folder\" you could instead do something like this:

Dim strBasePath As String: strBasePath = ActiveSheet.Range("A1").Value

Just change the Range("A1") to the cell that would contain that path (again, make sure the cell ends with the backslash). That way they can just update the cell to desired

~tigeravatar