Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invoice Numbering
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invoice Numbering
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 -- Regards, Nigel wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invoice Numbering
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. On Mar 29, 4:10*pm, wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invoice Numbering
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. On Mar 29, 2:10*am, "Nigel" wrote: 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 -- Regards, Nigel wrote in message ... 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.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invoice Numbering
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("Ti tle"), 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("Ti tle"), 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. On Mar 30, 12:16*am, wrote: 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. On Mar 29, 2:10*am, "Nigel" wrote: 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 -- Regards, Nigel wrote in message ... 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invoice Numbering
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("Ti tle"), 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("Ti tle"), 9)) End Function On Mar 29, 11:19*am, Ivyleaf wrote: 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("Ti tle"), 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("Ti tle"), 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. On Mar 30, 12:16*am, wrote: 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. On Mar 29, 2:10*am, "Nigel" wrote: 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 -- Regards, Nigel wrote in message .... 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invoice Numbering
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 On Sat, 29 Mar 2008 06:16:11 -0700 (PDT), wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invoice Numbering
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. On Mar 29, 1:22*pm, Gord Dibben <gorddibbATshawDOTca wrote: 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 On Sat, 29 Mar 2008 06:16:11 -0700 (PDT), wrote: 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.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Invoice numbering | Excel Discussion (Misc queries) | |||
Invoice Numbering | Excel Discussion (Misc queries) | |||
Invoice Numbering | Excel Worksheet Functions | |||
Invoice Numbering | Excel Worksheet Functions | |||
Invoice Numbering | Excel Worksheet Functions |