GET NEXT INVOICE NUMBER FROM SAVED FILE

[STUBS691]

Hi Guys,

im stuck with the best way to do this.

no. 1 - once I open the invoice template use vba fetch the next invoice number from the folder where the rest invoices are saved
or
no. 2- when i use my macro to save the file, with in the same macro use vba to find the next invoice number in the folder where they are saved.

my problem is i have multiple words with in my saved file its not just an invoice number as such.

any help would be a big help i cant find any vba at the moment that i can manipulate to work for me.

below is code i use with macro to save file to set location.

Sub actionSaveFile()
Dim sfname As String
Dim savename As String
Dim saveLocation As String
Dim strFile As String
Dim filename As String

saveLocation = "S:\Admin\Excel Invoice's\"
savename = Sheets("Invoice").Range("C8") & (", ") & Sheets("Invoice").Range("C10") & (", ") & ("Claim") & (" ") & Sheets("Invoice").Range("F12")

strFile = "S:\Admin\Excel Invoice's\"
filename = Sheets("Invoice").Range("C8") & (", ") & Sheets("Invoice").Range("C10") & (", ") & ("Claim") & (" ") & Sheets("Invoice").Range("F12") & (".xlsm")


If FileExists(strFile + filename) = True Then
    MsgBox "File name already exists, change the claim number"
Else
sfname = Application.GetSaveAsFilename(InitialFileName:=saveLocation + savename, fileFilter:="XLSM (*.xlsm), *.xslm")
ActiveWorkbook.SaveAs sfname, 52

End If
End Sub

thanks.

 

[diddi]

It depends on whether you have the invoice number on the document. if you want to use the next invoice number in your document then obviously, you need to determine the value when you open the template (which i would expect is the best method) but... this causes problems in multiuser environments if that applies.

In terms of getting next number from the folder of files, you can get the whole list and parse them to obtain largest value, assuming that the file names are systematic.
another option is to save a text file which contains the most recent value, and update it using a sequential text file read / write which is simple couple of lines of code

 

[STUBS691]

It depends on whether you have the invoice number on the document. if you want to use the next invoice number in your document then obviously, you need to determine the value when you open the template (which i would expect is the best method) but... this causes problems in multiuser environments if that applies.

In terms of getting next number from the folder of files, you can get the whole list and parse them to obtain largest value, assuming that the file names are systematic.
another option is to save a text file which contains the most recent value, and update it using a sequential text file read / write which is simple couple of lines of code

thanks for the reply, i managed to find this code and manipulated it to suit me, works a treat. it looks in the folder where i save my pdfs and generates the next number and then puts that in the cell.

this number only updates and increases when the pdf with the invoice number is saved back to that folder.

Sub CreateNewFileName()
     '--------------------------------------------------------------------------------
     'Produces an incremental FileName (if name is 'Data' it creates Data-1.xls)
     'Builds a suffix always one greater than the max suffix of any other potentially
     'existing files that have the same 'root' name, e.g. if 'Data.xls' and 'Data-2.xls'
     'exist, it creates Data-3.xls
     'Helps to avoid overwrite old files (among other uses)
     '--------------------------------------------------------------------------------
    Dim newFileName As String, strPath As String
    Dim strFileName As String, strExt As String
    strPath = "S:\Admin\Invoice's - PDF's\" 'Change to suit
    strFileName = "Inv" 'Change to suit
    strExt = ".pdf" 'Change to suit
    newFileName = strFileName & " " & GetNewSuffix(strPath, strFileName, strExt) & strExt
    


    MsgBox "The new Invoice Number is: " & newFileName
     'Save copy
    
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
     'File's name
    strFile = Dir(strPath & "\" & strName & "*")
    Do While strFile <> ""
         'File's suffix starts 2 chars after 'root' name (right after the "-")
        strSuffix = Mid(strFile, Len(strName) + 2, Len(strFile) - Len(strName) - Len(strExt) - 1)
         'FileName is valid if 1st char after name is " " and suffix is numeric with no dec point
         'Skip file if "." or "," exists in suffix
        If Mid(strFile, Len(strName) + 1, 1) = " " And CSng(strSuffix) >= 0 And _
        InStr(1, strSuffix, ",") = 0 And InStr(1, strSuffix, ".") = 0 Then
             'Store the max suffix
            If CInt(strSuffix) >= intMax Then intMax = CInt(strSuffix)
        End If
NextFile:
        strFile = Dir
    Loop
    GetNewSuffix = intMax + 1
    Sheets("Invoice").Range("F9") = GetNewSuffix
    Exit Function
     
ErrorHandler:
    If Err Then
        Err.Clear
        Resume NextFile
    
    End If
End Function