MkDir Runtime Error 76

[Beto]

Hi all,

I am getting hung up on error 76 - path not found at MKDIR in macro below... any thoughts? network path is right and have access...



Sub createRequestNumberAndSave()


Const requestPath = "\\xxxxxxxxx\shares\department\Purchasing and Inventory\Test File\Purchase Req" ' request root save path
Const fNamePrefix = "Req" ' prefix for the filename
Const fNameExt = ".xlsm" ' file extension
Const getRequestDate = "B4" ' we GET the DATE of the request from B4
Const putRequestNumber = "E7" ' we will PUT the new filename into cell E7


Dim reqDate As Date
Dim folderName As String
Dim fName As String
Dim fNum As Long
Dim nextRequestNum As Long


'get the request date and make sure it's valid
If IsDate(Range(getRequestDate).Value) Then
'valid date found in cell B4
reqDate = Range(getRequestDate).Value
Else
'valid date not found in B4. Do we want to default to today's date?
'vbLf is new line like "alt & enter"
If MsgBox("Cell " & getRequestDate & " does not contain a valid date." & vbLf & vbLf & _
"Do you want to use today's date instead?", vbQuestion + vbOKCancel, "Date not found") <> vbOK Then
Call MsgBox("Request Not Saved.", vbCritical + vbononly, "User Cancelled")
Exit Sub 'stop running
Else
reqDate = Date 'use today's date
End If
End If


'find the next unused request number for this month
folderName = Format(reqDate, "YYYYMMM")
nextRequestNum = 0


'figure out the next unused "file number"
fName = Dir(requestPath & folderName & "" & fNamePrefix & folderName & "-*" & fNameExt)
If fName = "" Then
'file not found
If Dir(requestPath & folderName, vbDirectory) = "" Then
'month not found - create folder?
If MsgBox("Okay to create folder '" & requestPath & folderName & "' for request #" & folderName & "-001 ?", _
vbOKCancel + vbQuestion, "Folder not Found") <> vbOK Then Exit Sub
'create folder
MkDir (requestPath & folderName)
End If
Else
'month found. Now find the highest request number in the folder.
Do While fName <> ""
Debug.Print "Found File: " & fName
'get the number (filename = fNamePrefix & "YYYYMMM-___.xlsm" so we know where it is
If IsNumeric(Mid(fName, 9 + Len(fNamePrefix), 3)) Then 'it's a valid number
fNum = Val(Mid(fName, 9 + Len(fNamePrefix), 3))
'if it's the biggest so far, remember it
If fNum > nextRequestNum Then nextRequestNum = fNum 'biggest one so far
End If
fName = Dir
Loop
End If


'we have the next available request#
nextRequestNum = nextRequestNum + 1 'new request# (numeric)
'PUT the new request# (text) in cell E7
Range(putRequestNumber).Value = fNamePrefix & folderName & "-" & Format(nextRequestNum, "000")
fName = requestPath & folderName & "" & Range(putRequestNumber).Value & fNameExt
Debug.Print "Saving as: " & fName





'save file
ActiveWorkbook.SaveAs fName


'DOUBLE CHECK check that file exists
If Dir(fName) = "" Then
'something went wrong (file wasn't saved)
Call MsgBox("ERROR! FILE NOT SAVED: " & fName, vbCritical + vbOKOnly, "ERROR!")
Stop
End If


'success message!
Call MsgBox("Request saved successfully:" & vbLf & vbLf & fName, vbInformation, "Request Created")


'NextRequest '?


Call Email_1


End Sub

[Kenneth Hobs]

Welcome to the forum! Please paste code between code tags. Type them or click the # icon on toolbar to insert them.

Const requestPath = "\\xxxxxxxxx\shares\department\Purchasing and Inventory\Test File\Purchase Req\" ' request root save path

[Beto]

Hi Kenneth - thank you for the quick response and welcome!

Unfortunately I continue to have the same error with the "" ...

Anything else you can think of here?

Thanks!

Welcome to the forum! Please paste code between code tags. Type them or click the # icon on toolbar to insert them.

Const requestPath = "\\xxxxxxxxx\shares\department\Purchasing and Inventory\Test File\Purchase Req\" ' request root save path

 Sub createRequestNumberAndSave()    


    Const requestPath = "\\xxxxxx\shares\department\Purchasing and Inventory\Test File\Purchase Req\"  ' request root save path
    Const fNamePrefix = "Req"           ' prefix for the filename
    Const fNameExt = ".xlsm"            ' file extension
    Const getRequestDate = "B4"         ' we GET the DATE of the request from B4
    Const putRequestNumber = "E7"       ' we will PUT the new filename into cell E7


    Dim reqDate As Date
    Dim folderName As String
    Dim fName As String
    Dim fNum As Long
    Dim nextRequestNum As Long


    'get the request date and make sure it's valid
    If IsDate(Range(getRequestDate).Value) Then
        'valid date found in cell B4
        reqDate = Range(getRequestDate).Value
    Else
        'valid date not found in B4. Do we want to default to today's date?
        'vbLf is new line like "alt & enter"
        If MsgBox("Cell " & getRequestDate & " does not contain a valid date." & vbLf & vbLf & _
            "Do you want to use today's date instead?", vbQuestion + vbOKCancel, "Date not found") <> vbOK Then
                Call MsgBox("Request Not Saved.", vbCritical + vbononly, "User Cancelled")
                Exit Sub 'stop running
        Else
            reqDate = Date 'use today's date
        End If
    End If


    'find the next unused request number for this month
    folderName = Format(reqDate, "YYYYMMM")
    nextRequestNum = 0


    'figure out the next unused "file number"
    fName = Dir(requestPath & folderName & "\" & fNamePrefix & folderName & "-*" & fNameExt)
    If fName = "" Then
        'file not found
        If Dir(requestPath & folderName, vbDirectory) = "" Then
            'month not found - create folder?
            If MsgBox("Okay to create folder '" & requestPath & folderName & "' for request #" & folderName & "-001 ?", _
                vbOKCancel + vbQuestion, "Folder not Found") <> vbOK Then Exit Sub
            'create folder
            MkDir (requestPath & folderName)
        End If
    Else
        'month found. Now find the highest request number in the folder.
        Do While fName <> ""
            Debug.Print "Found File: " & fName
            'get the number (filename = fNamePrefix & "YYYYMMM-___.xlsm" so we know where it is
            If IsNumeric(Mid(fName, 9 + Len(fNamePrefix), 3)) Then 'it's a valid number
                fNum = Val(Mid(fName, 9 + Len(fNamePrefix), 3))
                'if it's the biggest so far, remember it
                If fNum > nextRequestNum Then nextRequestNum = fNum 'biggest one so far
            End If
            fName = Dir
        Loop
    End If


    'we have the next available request#
    nextRequestNum = nextRequestNum + 1 'new request# (numeric)
    'PUT the new request# (text) in cell E7
    Range(putRequestNumber).Value = fNamePrefix & folderName & "-" & Format(nextRequestNum, "000")
    fName = requestPath & folderName & "\" & Range(putRequestNumber).Value & fNameExt
    Debug.Print "Saving as: " & fName
   




    'save file
    ActiveWorkbook.SaveAs fName


    'DOUBLE CHECK check that file exists
    If Dir(fName) = "" Then
        'something went wrong (file wasn't saved)
        Call MsgBox("ERROR! FILE NOT SAVED: " & fName, vbCritical + vbOKOnly, "ERROR!")
        Stop
    End If


    'success message!
    Call MsgBox("Request saved successfully:" & vbLf & vbLf & fName, vbInformation, "Request Created")


    'NextRequest '?
    
    
Call Email_1


End Sub

[Beto]

Kenneth - to add one more piece of info:

If I use requestPath: X:\Purchasing and Inventory\Test File\Purchase Req\ then it works perfectly. Only errors out when I try and make it more generic for others to use by \\xxxxxxx\shares\department does it error out...

[Beto]

Update - File path was missing a character... works now for me but not yet for others on the drive... I'll get there...

[Kenneth Hobs]

A Shell() or ShellAndWait() routine may be needed for UNC paths.

e.g.

Sub Test()
  Dim f$, Msg$
  f = "k:\ken\"
  
  On Error GoTo Einfo
  'Debug.Print f
  'MsgBox f
  'MkDir f
  Shell "cmd /c md " & """" & f & """", vbHide
  
  Exit Sub
Einfo:
  Msg = "Error # " & Str(Err.Number) & " was generated by " _
    & Err.Source & Chr(13) & Err.Description
  MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
  End
End Sub

ShellAndWait() routines:
www.cpearson.com/Excel/ShellAndWait.aspx
http://www.mvps.org/access/api/api0004.htm