variable not resetting

[paulageville]

Hi all,

Basically I have a spreadsheet I've created for work which is an order form.
There is a button on the order form, which the user clicks when they've finished inputting data. When they click that button a copy of the file is automatically saved (saveas).
The filename is incremented for each file saved, based on the month the order is for (which the user inputs into a cell [b3]). ie Oct11-1.xls, Oct11-2.xls etc
Depending on the month the order is for, I want each file to save in a different folder (eg Oct11, Nov 11 etc).
After compiling the code below I tested it inputting a date in October, the code worked fine, it created a folder "Oct11" and the file was saved in that folder as"Oct11-1.xls. The next few files were saved as "Oct11-2.xls" etc as expected.
However, if I change the month of the order, it still saves in the Oct11 folder. Surely strFilename is taken from the value of cell b3 each time the macro below is run? In which case the save location should change if cell b3 is changed? Or do I need to "reset" the variable in any way? If so, how?

Any help would be massively appreciated, I'm really stuck!!

Sub CreateNewFileName()
    Dim newFileName As String
    Dim strPath As String
    Dim strFilename As String
    Dim strExt As String
    strFilename = Range("B3").Value
    strFilename = Format(Date, "mmmyy")
    If Len(Dir("E:\hospitality\" & strFilename & "\", vbDirectory)) = 0 Then
        MkDir "E:\hospitality\" & strFilename & "\"
    End If
    strPath = "E:\hospitality\" & strFilename & "\"
    strExt = ".xls"
    newFileName = strFilename & "-" & GetNewSuffix(strPath, strFilename, strExt) & strExt
    x = MsgBox("Reference Number: " & newFileName, 0, "Order Saved")
    ActiveWorkbook.SaveAs strPath & newFileName
End Sub
 
Function GetNewSuffix(ByVal strPath As String, ByVal strName As String, ByVal strExt As String) As Integer
    Dim strFile As String, strSuffix As String, intMax As Integer
    On Error GoTo ErrorHandler
    strFile = Dir(strPath & "\" & strName & "*")
    Do While strFile <> ""
        strSuffix = Mid(strFile, Len(strName) + 2, Len(strFile) - Len(strName) - Len(strExt) - 1)
        If Mid(strFile, Len(strName) + 1, 1) = "-" And CSng(strSuffix) >= 0 And _
        InStr(1, strSuffix, ",") = 0 And InStr(1, strSuffix, ".") = 0 Then
            If CInt(strSuffix) >= intMax Then intMax = CInt(strSuffix)
        End If
NextFile:
        strFile = Dir
    Loop
    GetNewSuffix = intMax + 1
    Exit Function
     
ErrorHandler:
    If Err Then
        Err.Clear
        Resume NextFile
    End If
End Function

 

[VoG]

You need to delete or comment out this line of code

strFilename = Format(Date, "mmmyy")

 

[taurean]

The problem lies here:

[COLOR=blue]strFilename = Range("B3").Value[/COLOR]
[COLOR=red]strFilename = Format(Date, "mmmyy")
[/COLOR]

You are using same variable and the red marked one overrides the previous one because it converts Date[System Date] to the format you've specified and the current month being october it will save in October folder.

What you can do is insert a date Cell B3 and then delete the blue marked line and revise the other one like:

strFilename = Format(Range("B3").Value, "mmmyy")

 

[paulageville]

hi taurean,

i have done as you suggested: -
however it is now giving me an error message when trying to create a new folder for november.
any idea as to why?

Sub CreateNewFileName()
    Dim newFileName As String
    Dim strPath As String
    Dim strFilename As String
    Dim strExt As String
    strFilename = Format(Range("B3").Value, "mmmyy")
    If Len(Dir("E:\hospitality\" & strFilename & "\", vbDirectory)) = 0 Then
        MkDir "E:\hospitality\" & strFilename & "\"
    End If
    strPath = "E:\hospitality\" & strFilename & "\"
    strExt = ".xls"
    newFileName = strFilename & "-" & GetNewSuffix(strPath, strFilename, strExt) & strExt
    x = MsgBox("Reference Number: " & newFileName, 0, "Order Saved")
    ActiveWorkbook.SaveAs strPath & newFileName
End Sub
 
Function GetNewSuffix(ByVal strPath As String, ByVal strName As String, ByVal strExt As String) As Integer
    Dim strFile As String, strSuffix As String, intMax As Integer
    On Error GoTo ErrorHandler
    strFile = Dir(strPath & "\" & strName & "*")
    Do While strFile <> ""
        strSuffix = Mid(strFile, Len(strName) + 2, Len(strFile) - Len(strName) - Len(strExt) - 1)
        If Mid(strFile, Len(strName) + 1, 1) = "-" And CSng(strSuffix) >= 0 And _
        InStr(1, strSuffix, ",") = 0 And InStr(1, strSuffix, ".") = 0 Then
            If CInt(strSuffix) >= intMax Then intMax = CInt(strSuffix)
        End If
NextFile:
        strFile = Dir
    Loop
    GetNewSuffix = intMax + 1
    Exit Function
     
ErrorHandler:
    If Err Then
        Err.Clear
        Resume NextFile
    End If
End Function

 

[taurean]

In Cell B3, a valid date must be entered!
Edit:- Month must be November for the date specified!!!