Using mkDir call and getting Runtime error 76 "Path not found"

[mrmacys]

Hi All,

I've written a code so that when saving a file if it does not exist the code will prompt you to create the path and then save the file (fname).
see below,

ErrorHandler:

Dim a
a = MsgBox("Location not Found. Do you want to create this location?", vbCritical + vbYesNo, "Error")
If a = vbYes Then
MkDir "F:\AEROPOSTALE\Season\" & Season & "\" & SeaYr & "\" & Category & "\" & Vndr
ActiveWorkbook.SaveAs "F:\AEROPOSTALE\Season\" & Season & "\" & SeaYr & "\" & Category & "\" & Vndr & "\" & Fname & ".xls"

Else
End
End If

The line in red is where the debug points to when I get the runtime error 76.

 

[ktab]

the makedir command affects the last folder to be created. Obviously, you need to build the path folder by folder if it does not exist.
E.g. mkdir "c:\1\2\3" takes for granted that path c:\1\2 exists, and tries to create folder "3". If 2 is missing, you get an error

 

[Diablo II]

try adding back \ at the end
MkDir "F:\AEROPOSTALE\Season\" & Season & "\" & SeaYr & "\" & Category & "\" & Vndr\

 

[mrmacys]

OMG i can't believe that was it. I guess it assumes everything before the "|" already exists. Thanks so much guys, works great now

 

[Kenneth Hobson]

If you ever need to do that again:

There are at least 3 cases where MkDir can fail.
1. Drive does not exist.
2. Folder name is illegal.
3. Preceding folders do not exist.

There is an API method that can be used for case 3 or the ole DOS method:

Shell "cmd /c md " & """" & "F:\AEROPOSTALE\Season\" & Season & "\" & SeaYr & "\" & Category & "\" & Vndr" & """"

 

[ktab]

Check this out, i think it will help you somehow...

Public Sub test()
'your directory here....
Call MakeDirectory("c:\1\2\3\")
End Sub

'routine to create your directory path
Public Sub MakeDirectory(FolderPath As String)
Dim x, i As Integer, strPath As String
x = Split(FolderPath, "\")

For i = 0 To UBound(x) - 1
    strPath = strPath & x(i) & "\"
    If Not FolderExists(strPath) Then MkDir strPath
Next i

End Sub

'function to check if folder exist
Function FolderExists(FolderPath As String) As Boolean
On Error Resume Next

ChDir FolderPath
If Err Then FolderExists = False Else FolderExists = True

End Function

 

[mrmacys]

Ok so I fixed the runtime error 76 now I'm getting runtime error 75 "Path/File access error". I read that this has something to do with "read-only" file access. As it is now, allt he macros are coded on a single Sheet. So i have the main sheet adn then i have people that open this sheet read-only to run the macros. Could that have anything to do with this error?