I want Excel to create a folder when HYPERLINK function returns "Cannot open specified file"

[lulyluly]

Hello.

I use the HYPERLINK function for linking to some existing folders.

I want that if the linked folder is not found, instead of getting the msgbox "Cannot open specified file", a new folder will be created (just in the path and name of the missing folder)

[#2]

@lulyluly 

It can be done using vba, but with some flaws.

Let's say your Target folder address is:

C:\lulyluly\Documents\MyProjects\MyFiles\Forlder01

If just the last subfolder (Folder01)  is changed using a code to verify and create a new one will not be a problem.

But talking about Folder address in general the other Subfolder also can be changed, and how to identify what is the correct root folder address in such situations?

You need to specify in the code what is your Root Folder address. Don't forget to keep the last Backslash ("\")

Option Explicit
Const RootFolder As String = "C:\RootFolderAddress\"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim HypAddress As String, LastSubfolder As String
    Dim tmp As Variant
    
    On Error Resume Next
    
    If Target.Hyperlinks.Count > 0 Then
    Application.DisplayAlerts = False
        HypAddress = Target.Hyperlinks(1).Address
        If Dir(HypAddress, vbDirectory) = "" Then
            tmp = Split(HypAddress, "\")
            LastSubfolder = tmp(UBound(tmp))
            HypAddress = RootFolder & LastSubfolder
            MkDir (HypAddress)
            Target.Hyperlinks.Add Anchor:=Target, Address:=RootFolder & LastSubfolder
            MsgBox ("The hyperlink was updated accordingly"), vbInformation
            ActiveWorkbook.FollowHyperlink HypAddress
        End If
    End If

    
End Sub