DIR$ function unreliable?

[CWD]

I am trying to get a macro to hyperlink to a folder while checking to see if a folder exists for the hyperlinked item. My problem is that the first DIR$ call is unreliable. Sometimes it works as written while other times it fails with a Runtime error '76' Path not found.

Why would it work sometimes but not others? This is my first attempt using the DIR Function.

Here's a link to the folder structure and the XLSM File project. Any help would be greatly appreciated.

https://1drv.ms/u/s!AtvY5ZcCa165hLMithqXu8YsNcl3Gg

Thank you.

Sub Macro1()
Dim strFile As String


' Gets column to hyperlink
Set Rng = Application.InputBox(Prompt:="Select cells to hyperlink.", Title:="Generate Effort Hyperlinks", Type:=8)
' Sets the folder name for the OAWRs, RFPs, HSIs, LGs, PROPs, etc.
strFile = Dir$("..\*Efforts", vbDirectory)  '##### THIS RETURNS EMPTY... SOMETIMES ####
        


' Iterates through cells to generate hyperlink
For Each cell In Rng
    ' Selects cell to manipulate
    cell.Select
    With Selection
        .Hyperlinks.Delete ' Deletes existing Hyperlink
        
        ' Checks for existing folder as loop; creates Effort Folders if not found
        Do While Len(Dir$("..\" & strFile & "\" & Mid(.Offset(0, 1), 1, Len(.Offset(0, 1).Value) - 3) & "\" & .Offset(0, 1).Value & "*", vbDirectory)) = 0
            MkDir "..\" & strFile & "\" & Mid(.Offset(0, 1), 1, Len(.Offset(0, 1).Value) - 3) & "\" & .Offset(0, 1).Value _
                & " (" & .Value & ") " & .Offset(0, 3)
            ' Select CASE sets up different folders for efforts
            Select Case Mid(.Offset(0, 1), 1, Len(.Offset(0, 1).Value) - 3)
            Case "RFP"
                MkDir "..\" & strFile & "\" & Mid(.Offset(0, 1), 1, Len(.Offset(0, 1).Value) - 3) & "\" & .Offset(0, 1).Value _
                    & " (" & .Value & ") " & .Offset(0, 3) & "\Proposal"
            Case Else
                MkDir "..\" & strFile & "\" & Mid(.Offset(0, 1), 1, Len(.Offset(0, 1).Value) - 3) & "\" & .Offset(0, 1).Value _
                    & " (" & .Value & ") " & .Offset(0, 3) & "\Package"
            End Select
            ' Creates default ATP-WA folder
            MkDir "..\" & strFile & "\" & Mid(.Offset(0, 1), 1, Len(.Offset(0, 1).Value) - 3) & "\" & .Offset(0, 1).Value _
                & " (" & .Value & ") " & .Offset(0, 3) & "\ATP-WA"
        Loop


        ' Searches for the Directory with the Effort number.  Example, "OAWR001" or "RFP001" and sets the strFile variable to the folder name found
        strHyper = Dir$("..\" & strFile & "\" & Mid(.Offset(0, 1), 1, Len(.Offset(0, 1).Value) - 3) & "\" & .Offset(0, 1).Value & "*", vbDirectory)
        
        ' Sets the hyperlink to the path of the folder
        .Hyperlinks.Add Anchor:=Selection, _
            Address:="..\" & strFile & "\" & Mid(.Offset(0, 1), 1, Len(.Offset(0, 1).Value) - 3) & "\" & strHyper
    End With
Next cell
    
End Sub

 

[CWD]

I got it sorted...

I threw in some DEBUG MSGBOX alerts and found that the CurDir was set to my user folder. For example, My Documents fall under M:\CWD\Documents, so the Excel path was set to M:\CWD. The DIR$ command was looking UP one folder from M:\CWD in M:\ thus throwing the error. I fixed it by setting the CurDir = ThisWorkbook.Path in the top part of the code as below.

' Gets column to hyperlink
Set Rng = Application.InputBox(Prompt:="Select cells to hyperlink.", Title:="Generate Effort Hyperlinks", Type:=8)
' Sets the folder name for the OAWRs, RFPs, HSIs, LGs, PROPs, etc.

' DEBUG 1 List Current Directory
MsgBox "Debug 1: " & CurDir

' Change Directory to the workbook path
ChDir ThisWorkbook.Path

' DEBUG 2 List Current Directory after change
MsgBox "Debug 2: " & CurDir

strFile = Dir("..\*Efforts", vbDirectory)

' DEBUG 3 List Current Directory for HAHAs
MsgBox "Debug 3: " & CurDir

' Error trap hack...        
If strFile = "" Then
    MsgBox "Effort Path Empty"
    Exit Sub
End If


' Iterates through cells to generate hyperlink
For Each cell In Rng
. . . . rest of example in previous post . . . .