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

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