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