Beto
06-14-2018, 04:46 PM
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
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