auto increment invoice number/ID ?

[anamo]

hi all
the attached Template excel file has cell called "Invoice no."
I wan that cell to be automatically updated to give each invoice unique number

like S1000 and it ill be S1001 for the next invoice but i have no idea how this will be done


I tried to set it to be data and time using
=now() but the problem is that it shown by this format 7/31/14 10:59
i tried to covert it to this format 1407311059 but I fail


so please anyone can help !

 

[Useful]

Okay done but this wont work with template excel files am I right ??
if yes then what you suggest to do that


thanks

general file type is .xlsx (from office 2007) .xlsm or .xlsb also excel files. But when you have macro code in the module you have to save wb as .xlsm or .xlsb type.

 

[anamo]

general file type is .xlsx (from office 2007) .xlsm or .xlsb also excel files. But when you have macro code in the module you have to save wb as .xlsm or .xlsb type.

okay thanks

 

[Useful]

but I think that it'll be better to increase invoice number before printing or after archiving (registering).

Private Sub Workbook_BeforePrint(Cancel As Boolean)
[A1] = [A1] + 1
[A1].NumberFormat = """S""0"
End Sub

 

[anamo]

but I think that it'll be better to increase invoice number before printing or after archiving (registering).

Private Sub Workbook_BeforePrint(Cancel As Boolean)
[A1] = [A1] + 1
[A1].NumberFormat = """S""0"
End Sub

yea it better the whole idea is not really accurate enough since u can save anytime not just when u close the file printing is more accurate ... and sadly macros wont works with templates :S

 

[anamo]

yea it better the whole idea is not really accurate enough since u can save anytime not just when u close the file printing is more accurate ... and sadly macros wont works with templates :S

btw what do you mean by after archiving (registering) please

 

[anamo]

btw what do you mean by after archiving (registering) please

code wont print any id after printing the id field is empty

 

[Useful]

btw what do you mean by after archiving (registering) please

printing invoice details to pdf or inserting another base sheet rows to keep all invoice records
P.S : to avoid about macrophobia just save your file as .xlsb and that is all.

 

[anamo]

hi all
i come up with the following solution which has extra amazing thing it will save the file with the ID name
but i got problem with handling file exist exception :S

like if exception thrown then do the following
Range("E4").Value = Range("E4").Value + 1
but how to catch the exception ????

Sub nxt_invoice()
Range("E4").Value = Range("E4").Value + 1
Range("A8:E21").ClearContents
Range("A1").ClearContents
Range("c2:c5").ClearContents
End Sub


Sub save_invoice()
Dim newfn As Variant
' copy invoice to a new workbook
ActiveSheet.Copy
newfn = "D:\Invoice\Inv" & Range("E4").Value & ".xlsx"
ActiveWorkbook.SaveAs newfn, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
nxt_invoice
End Sub

 

[Useful]

hi all
i come up with the following solution which has extra amazing thing it will save the file with the ID name
but i got problem with handling file exist exception :S

like if exception thrown then do the following
Range("E4").Value = Range("E4").Value + 1
but how to catch the exception ????

Sub nxt_invoice()
Range("E4").Value = Range("E4").Value + 1
Range("A8:E21").ClearContents
Range("A1").ClearContents
Range("c2:c5").ClearContents
End Sub


Sub save_invoice()
Dim newfn As Variant
' copy invoice to a new workbook
ActiveSheet.Copy
newfn = "D:\Invoice\Inv" & Range("E4").Value & ".xlsx"
ActiveWorkbook.SaveAs newfn, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
nxt_invoice
End Sub

use

ThisWorkbook.Save

at the end of the line of save_invoice() to save your workbook.
(but it'll better to save your wb as .pdf)