Invoice Numbering

[meghanwh]

I am creating a template for invoices. I want it to automatically fill
in the invoice number in cell C5 each time I open it, just one number
higher than the last one. I also want it to automatically save to a
folder on my desktop called "Invoices". How can I do this with VB?
Thanks.

 

[Nigel]

Create your invoice template and enter first invoice # -1 in cell c5, place
the following code into the workbook open event and manually save file as
"Template.xlt". If you are using Excel 2007 change as required.


Private Sub Workbook_Open()
Dim filePath As String
filePath = "C:\Users\User\Desktop\Invoices\"

With Sheets("Sheet1").Range("C5")
' increment invoice number
.Value = .Value + 1

' save template with next higher number
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=filePath & "Template.xlt",
FileFormat:=xlTemplate8
Application.DisplayAlerts = True

' save invoice numbered
ActiveWorkbook.SaveAs Filename:=filePath & "Invoice" & .Value & ".xls",
FileFormat:=xlExcel8

End With
End Sub

 

[Ivyleaf]

Hi,

There should be plenty of posts on this one, and just as many
answers / methods to achieve it. I would personally suggest writing a
DocProperty that auto increments and updates each time you open it.

Cheers,
Ivan.

 

[meghanwh]

Thanks Nigel. One question - when i go to reopen a previously created
invoice it runs the macro again and duplicates the invoice with a new
number. is there any way to prevent this? Thanks.

 

[Ivyleaf]

Hi,

I did notice that in Nigel's code. One way around it would be to check
if the file is the template or not. Here's my take on it, solving that
problem and using a document property:

Private Sub Workbook_Open()
Dim InvNo As Long, InvPath As String, PathDepth As Integer
Dim PartPath As String, i As Integer
If Len(ThisWorkbook.Path) > 0 Then Exit Sub

On Error Resume Next

InvPath = "C:\Documents\Invoices\2008"

PathDepth = Len(InvPath) - Len(Replace(InvPath, "\", ""))
i = PathDepth - 2
Do Until Len(Dir(InvPath, vbDirectory)) > 0
If i > -1 Then
PartPath = StrReverse(Replace(StrReverse(InvPath), "\",
"", , i))
MkDir Left(PartPath, InStrRev(PartPath, "\") - 1)
i = i - 1
Else
MkDir InvPath
End If
Loop

InvNo = Int(Mid(ThisWorkbook.BuiltinDocumentProperties("Title"),
9)) + 1

If InvNo = 0 Then InvNo = 1

With ThisWorkbook
.BuiltinDocumentProperties("Title") = "Invoice " &
Format(InvNo, "00000")
.SaveCopyAs InvPath & "\Template.xlt"
.SaveAs Filename:=InvPath & "\Invoice " & Format(InvNo,
"00000") & ".xls"
End With
End Sub

Note the third line that simply checks the path of the file. If you
have opened the template, the file has not yet been saved, hence the
path is zero length.

As for using the document property, I prefer this method as it exposes
the data to Windows etc. If you hover the mouse over the file in
explorer for example, in this case it will show the invoice number. To
display the number in a cell in the worksheet, you can either add a
line of code that sets it when the file is created, " Range("A1") =
InvNo " for example, or add the following code to a module in the
template:

Function Invoice()
Invoice = Int(Mid(ThisWorkbook.BuiltinDocumentProperties("Title"),
9))
End Function

Then you can simply put the formula " =Invoice() " anywhere in the
file and it will contain the correct number and never be out of sync
with the file.

Cheers,
Ivan.

 

[meghanwh]

Thanks Ivan. It seems to be working except for putting the invoice
number onto the spreadsheet in cell C5. I think i just didn't put the
function in the right place.

The VB is now:

Private Sub Workbook_Open()
Dim InvNo As Long, InvPath As String, PathDepth As Integer
Dim PartPath As String, i As Integer
If Len(ThisWorkbook.Path) > 0 Then Exit Sub


On Error Resume Next


InvPath = "C:\Documents and Settings\alumni\Desktop\Invoices"


PathDepth = Len(InvPath) - Len(Replace(InvPath, "\", ""))
i = PathDepth - 2
Do Until Len(Dir(InvPath, vbDirectory)) > 0
If i > -1 Then
PartPath = StrReverse(Replace(StrReverse(InvPath), "\",
"", , i))
MkDir Left(PartPath, InStrRev(PartPath, "\") - 1)
i = i - 1
Else
MkDir InvPath
End If
Loop


InvNo = Int(Mid(ThisWorkbook.BuiltinDocumentProperties("Title"),
9)) + 1


If InvNo = 0 Then InvNo = 1


With ThisWorkbook
.BuiltinDocumentProperties("Title") = "Invoice " &
Format(InvNo, "00000")
.SaveCopyAs InvPath & "\InvoiceTemplate.xlt"
.SaveAs Filename:=InvPath & "\Invoice " & Format(InvNo,
"00000") & ".xls"
End With
End Sub

Function Invoice()
Invoice = Int(Mid(ThisWorkbook.BuiltinDocumentProperties("Title"),
9))
End Function

 

[Gord Dibben]

Dave Peterson posted this a while back.

If the workbook has never been saved, which is the case with a newly created
workbook from the Template, the invoice number will increment.

After saving, the number will not increment when opening that saved workbook.

Private Sub Workbook_Open()
if thisworkbook.path = "" then
'it's never been saved, so increment
Sheet1.Range("H16").Value = Sheet1.Range("H16").Value + 1
end if
End Sub

Incorporate into your macro.


Gord Dibben MS Excel MVP

 

[meghanwh]

Thanks everyone. Everything seems to be working except for showing the
invoice number in cell C5. Any thoughts on how to do this would be
great. thanks.

 

[Gord Dibben]

Which set of code are you using?

If Ivan's code see below for his notes

................................................................

To display the number in a cell in the worksheet, you can either add a
line of code that sets it when the file is created, " Range("A1") =
InvNo " for example, or add the following code to a module in the
template:

Function Invoice()
Invoice = Int(Mid(ThisWorkbook.BuiltinDocumentProperties("Title"),9))
End Function

Then you can simply put the formula " =Invoice() " anywhere in the
file and it will contain the correct number and never be out of sync
with the file.

................................................

You would use C5 in either case.


Gord