Run-Time Error 75 "Path/File Access Error"

[mrmacys]

Hi All,

I'm encountering Run time error 75 on the below code. In the excel macro, when the file is not found the below codes run to save the excel workbook,

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

Else
End
End If

Fname refers to the excel file I'm saving,

 

[RoryA]

MkDir will only create the lowest level folder and will fail if any of the parent folders are missing. You can use a function like this to build the whole thing as required:

Function CheckAndMakePath(strPath As String) As Boolean
   ' input must not include file name!
   Dim lngIndex
   On Error GoTo err_handle
   If right$(strPath, 1) <> Application.PathSeparator Then strPath = strPath & Application.PathSeparator
   CheckAndMakePath = True
   lngIndex = InStr(1, strPath, Application.PathSeparator, vbTextCompare)
   If lngIndex = 0 Then
      CheckAndMakePath = False
   Else
      Do
         If Dir(left$(strPath, lngIndex), vbDirectory) = "" Then MkDir left$(strPath, lngIndex)
         lngIndex = InStr(lngIndex + 1, strPath, Application.PathSeparator, vbTextCompare)
      Loop While lngIndex <> 0
   End If
   
leave:
   Exit Function
   
err_handle:
   CheckAndMakePath = False
   Resume leave
End Function

 

[mrmacys]

Wow, i remember when ZIP drives where the best thing out. An any case,it was a typo above, I don't have an A drive, It should be F.

 

[Scott.C.Lyerly]

I learn something new every day!