If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Auto Number in Invoice
Hi
hope someone can help me. I have an invoice i have designed and the last bit i need before i use it is to assign a invoice number to the template and have it automatically increase by one everytime - ie 101 would be 102 on opening the next time. i have read McGimpseys page and used the code - i put it in the this workbook part but its not doing anything. Have i got the right code or do i need any other bits to go with it? Also i dont understand what `text` file i need - as its saying to create one? and what is default path as string - is that where i type where my template is. Sorry im a newbie to invoice numbering. but i only need this and then i can move on to other things. many thanks |
#2
|
|||
|
|||
Auto Number in Invoice
OH I FORGOT TO SAY MY INVOICE NUMBER IS IN CELL `E5` - THIS IS WHERE I WANT
IT TO CHANGE EACH TIME "Pammi J" wrote: Hi hope someone can help me. I have an invoice i have designed and the last bit i need before i use it is to assign a invoice number to the template and have it automatically increase by one everytime - ie 101 would be 102 on opening the next time. i have read McGimpseys page and used the code - i put it in the this workbook part but its not doing anything. Have i got the right code or do i need any other bits to go with it? Also i dont understand what `text` file i need - as its saying to create one? and what is default path as string - is that where i type where my template is. Sorry im a newbie to invoice numbering. but i only need this and then i can move on to other things. many thanks |
#3
|
|||
|
|||
Auto Number in Invoice
Sounds like your problem is with the two Const statements at the beginning of
the function? ----- Const sDEFAULT_PATH As String = "your path here" Const sDEFAULT_FNAME As String = "defaultseq.txt" ----- The first one should be set to the path to where the text file containing the sequence of numbers is kept. The "your path here" part should actually look something like this: "C:\Documents and Settings\PammiJ\My Documents\" For the second line, yes you need to create a file somewhere (in the folder pointed to by the first line) and all it needs to contain initially is the first invoice number you want to use, minus 1. Create the file using Notepad: open Notepad, start a new file, just type a number into it (assume you want invoices to start at 1, type 0 into it). Then use Save As to give it a name like the one he suggested or you could give it some other name like PammiJs_InvoiceNumbers.txt and save it in the path you have decided on. Close Notepad. Now your first two lines in that function should look something like this: Const sDEFAULT_PATH As String = "C:\Documents and Settings\PammiJ\My Documents\" Const sDEFAULT_FNAME As String = "PammiJs_InvoiceNumbers.txt" I think the McGimpsey page uses cell B2 as the one where the invoice number is to appear, just change "B2" in his sample code to "E5" and it should work. Only thing that might keep it from working is your Macro Security - make sure macros are permitted to run - best setting that's a balance of security and convenience is MEDIUM where you are prompted when the workbook is opened whether or not to let macro code execute. That will keep you from being unpleasantly surprised if someone sends you an Excel workbook with code in it that you weren't expecting. One other thing that can also keep macros from running these days is your anti-virus application. Some of those have a setting in them that prevents VBA code from running, and if yours is one of those (I use Kaspersky Business Optimal and it has such a setting) then you'll need to set it to allow you to run the code. "Pammi J" wrote: Hi hope someone can help me. I have an invoice i have designed and the last bit i need before i use it is to assign a invoice number to the template and have it automatically increase by one everytime - ie 101 would be 102 on opening the next time. i have read McGimpseys page and used the code - i put it in the this workbook part but its not doing anything. Have i got the right code or do i need any other bits to go with it? Also i dont understand what `text` file i need - as its saying to create one? and what is default path as string - is that where i type where my template is. Sorry im a newbie to invoice numbering. but i only need this and then i can move on to other things. many thanks |
#4
|
|||
|
|||
Auto Number in Invoice
Hi and many thanks for your quick reply.
I have done that and saved my invoice as a template but when i open there is no number in my e5 cell. Here is the code i put in Have i done something wrong - my macro security is med and firewall ect is not preventing macros from running. Public Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long Const sDEFAULT_PATH As String = "C:\Documents and Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices" Const sDEFAULT_FNAME As String = "inv no.txt" Dim nFileNumber As Long nFileNumber = FreeFile If sFileName = "" Then sFileName = sDEFAULT_FNAME If InStr(sFileName, Application.PathSeparator) = 0 Then _ sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName If nSeqNumber = -1& Then If Dir(sFileName) "" Then Open sFileName For Input As nFileNumber Input #nFileNumber, nSeqNumber nSeqNumber = nSeqNumber + 1& Close nFileNumber Else nSeqNumber = 1& End If End If On Error GoTo PathError Open sFileName For Output As nFileNumber On Error GoTo 0 Print #nFileNumber, nSeqNumber Close nFileNumber NextSeqNumber = nSeqNumber Exit Function PathError: NextSeqNumber = -1& End Function Public Sub SetUpNewClient() With ThisWorkbook.Sheets(1) .Range("B2").Value = NextSeqNumber(.Range("E5").Value & ".txt", 849) End With End Sub "JLatham" wrote: Sounds like your problem is with the two Const statements at the beginning of the function? ----- Const sDEFAULT_PATH As String = "your path here" Const sDEFAULT_FNAME As String = "defaultseq.txt" ----- The first one should be set to the path to where the text file containing the sequence of numbers is kept. The "your path here" part should actually look something like this: "C:\Documents and Settings\PammiJ\My Documents\" For the second line, yes you need to create a file somewhere (in the folder pointed to by the first line) and all it needs to contain initially is the first invoice number you want to use, minus 1. Create the file using Notepad: open Notepad, start a new file, just type a number into it (assume you want invoices to start at 1, type 0 into it). Then use Save As to give it a name like the one he suggested or you could give it some other name like PammiJs_InvoiceNumbers.txt and save it in the path you have decided on. Close Notepad. Now your first two lines in that function should look something like this: Const sDEFAULT_PATH As String = "C:\Documents and Settings\PammiJ\My Documents\" Const sDEFAULT_FNAME As String = "PammiJs_InvoiceNumbers.txt" I think the McGimpsey page uses cell B2 as the one where the invoice number is to appear, just change "B2" in his sample code to "E5" and it should work. Only thing that might keep it from working is your Macro Security - make sure macros are permitted to run - best setting that's a balance of security and convenience is MEDIUM where you are prompted when the workbook is opened whether or not to let macro code execute. That will keep you from being unpleasantly surprised if someone sends you an Excel workbook with code in it that you weren't expecting. One other thing that can also keep macros from running these days is your anti-virus application. Some of those have a setting in them that prevents VBA code from running, and if yours is one of those (I use Kaspersky Business Optimal and it has such a setting) then you'll need to set it to allow you to run the code. "Pammi J" wrote: Hi hope someone can help me. I have an invoice i have designed and the last bit i need before i use it is to assign a invoice number to the template and have it automatically increase by one everytime - ie 101 would be 102 on opening the next time. i have read McGimpseys page and used the code - i put it in the this workbook part but its not doing anything. Have i got the right code or do i need any other bits to go with it? Also i dont understand what `text` file i need - as its saying to create one? and what is default path as string - is that where i type where my template is. Sorry im a newbie to invoice numbering. but i only need this and then i can move on to other things. many thanks |
#5
|
|||
|
|||
Auto Number in Invoice
You are pushing my capability but... If you save this as a template (*.xlt)
when you open a File New copy (*+1,2,3.xls) it is an exact copy of your existing template. I think what you need is a look-up cell somewhere else =E5+1 probably as a "Workbook_Open()" event. HTH Lou "Pammi J" wrote: Hi and many thanks for your quick reply. I have done that and saved my invoice as a template but when i open there is no number in my e5 cell. Here is the code i put in Have i done something wrong - my macro security is med and firewall ect is not preventing macros from running. Public Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long Const sDEFAULT_PATH As String = "C:\Documents and Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices" Const sDEFAULT_FNAME As String = "inv no.txt" Dim nFileNumber As Long nFileNumber = FreeFile If sFileName = "" Then sFileName = sDEFAULT_FNAME If InStr(sFileName, Application.PathSeparator) = 0 Then _ sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName If nSeqNumber = -1& Then If Dir(sFileName) "" Then Open sFileName For Input As nFileNumber Input #nFileNumber, nSeqNumber nSeqNumber = nSeqNumber + 1& Close nFileNumber Else nSeqNumber = 1& End If End If On Error GoTo PathError Open sFileName For Output As nFileNumber On Error GoTo 0 Print #nFileNumber, nSeqNumber Close nFileNumber NextSeqNumber = nSeqNumber Exit Function PathError: NextSeqNumber = -1& End Function Public Sub SetUpNewClient() With ThisWorkbook.Sheets(1) .Range("B2").Value = NextSeqNumber(.Range("E5").Value & ".txt", 849) End With End Sub "JLatham" wrote: Sounds like your problem is with the two Const statements at the beginning of the function? ----- Const sDEFAULT_PATH As String = "your path here" Const sDEFAULT_FNAME As String = "defaultseq.txt" ----- The first one should be set to the path to where the text file containing the sequence of numbers is kept. The "your path here" part should actually look something like this: "C:\Documents and Settings\PammiJ\My Documents\" For the second line, yes you need to create a file somewhere (in the folder pointed to by the first line) and all it needs to contain initially is the first invoice number you want to use, minus 1. Create the file using Notepad: open Notepad, start a new file, just type a number into it (assume you want invoices to start at 1, type 0 into it). Then use Save As to give it a name like the one he suggested or you could give it some other name like PammiJs_InvoiceNumbers.txt and save it in the path you have decided on. Close Notepad. Now your first two lines in that function should look something like this: Const sDEFAULT_PATH As String = "C:\Documents and Settings\PammiJ\My Documents\" Const sDEFAULT_FNAME As String = "PammiJs_InvoiceNumbers.txt" I think the McGimpsey page uses cell B2 as the one where the invoice number is to appear, just change "B2" in his sample code to "E5" and it should work. Only thing that might keep it from working is your Macro Security - make sure macros are permitted to run - best setting that's a balance of security and convenience is MEDIUM where you are prompted when the workbook is opened whether or not to let macro code execute. That will keep you from being unpleasantly surprised if someone sends you an Excel workbook with code in it that you weren't expecting. One other thing that can also keep macros from running these days is your anti-virus application. Some of those have a setting in them that prevents VBA code from running, and if yours is one of those (I use Kaspersky Business Optimal and it has such a setting) then you'll need to set it to allow you to run the code. "Pammi J" wrote: Hi hope someone can help me. I have an invoice i have designed and the last bit i need before i use it is to assign a invoice number to the template and have it automatically increase by one everytime - ie 101 would be 102 on opening the next time. i have read McGimpseys page and used the code - i put it in the this workbook part but its not doing anything. Have i got the right code or do i need any other bits to go with it? Also i dont understand what `text` file i need - as its saying to create one? and what is default path as string - is that where i type where my template is. Sorry im a newbie to invoice numbering. but i only need this and then i can move on to other things. many thanks |
#6
|
|||
|
|||
Auto Number in Invoice
I'll look at this more this evening - but I believe that Rookie 1st class is
on the right track. The original code was designed to be driven from the Workbook_Open event. My question on your routine is What is in E5 when you make the call from within your SetUpNewClient() routine? That is expected to be the name of the file with the sequence numbers in it (Inv no.txt). Oh, and I recommend you do away with the space in that file name - they just cause issues when programming with filenames or paths that contain spaces. Try renaming the file (and changing the code) to something line InvNo.txt or Inv_no.txt. Not because it shouldn't work, but because it's easier to work with in general. "Pammi J" wrote: Hi and many thanks for your quick reply. I have done that and saved my invoice as a template but when i open there is no number in my e5 cell. Here is the code i put in Have i done something wrong - my macro security is med and firewall ect is not preventing macros from running. Public Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long Const sDEFAULT_PATH As String = "C:\Documents and Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices" Const sDEFAULT_FNAME As String = "inv no.txt" Dim nFileNumber As Long nFileNumber = FreeFile If sFileName = "" Then sFileName = sDEFAULT_FNAME If InStr(sFileName, Application.PathSeparator) = 0 Then _ sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName If nSeqNumber = -1& Then If Dir(sFileName) "" Then Open sFileName For Input As nFileNumber Input #nFileNumber, nSeqNumber nSeqNumber = nSeqNumber + 1& Close nFileNumber Else nSeqNumber = 1& End If End If On Error GoTo PathError Open sFileName For Output As nFileNumber On Error GoTo 0 Print #nFileNumber, nSeqNumber Close nFileNumber NextSeqNumber = nSeqNumber Exit Function PathError: NextSeqNumber = -1& End Function Public Sub SetUpNewClient() With ThisWorkbook.Sheets(1) .Range("B2").Value = NextSeqNumber(.Range("E5").Value & ".txt", 849) End With End Sub "JLatham" wrote: Sounds like your problem is with the two Const statements at the beginning of the function? ----- Const sDEFAULT_PATH As String = "your path here" Const sDEFAULT_FNAME As String = "defaultseq.txt" ----- The first one should be set to the path to where the text file containing the sequence of numbers is kept. The "your path here" part should actually look something like this: "C:\Documents and Settings\PammiJ\My Documents\" For the second line, yes you need to create a file somewhere (in the folder pointed to by the first line) and all it needs to contain initially is the first invoice number you want to use, minus 1. Create the file using Notepad: open Notepad, start a new file, just type a number into it (assume you want invoices to start at 1, type 0 into it). Then use Save As to give it a name like the one he suggested or you could give it some other name like PammiJs_InvoiceNumbers.txt and save it in the path you have decided on. Close Notepad. Now your first two lines in that function should look something like this: Const sDEFAULT_PATH As String = "C:\Documents and Settings\PammiJ\My Documents\" Const sDEFAULT_FNAME As String = "PammiJs_InvoiceNumbers.txt" I think the McGimpsey page uses cell B2 as the one where the invoice number is to appear, just change "B2" in his sample code to "E5" and it should work. Only thing that might keep it from working is your Macro Security - make sure macros are permitted to run - best setting that's a balance of security and convenience is MEDIUM where you are prompted when the workbook is opened whether or not to let macro code execute. That will keep you from being unpleasantly surprised if someone sends you an Excel workbook with code in it that you weren't expecting. One other thing that can also keep macros from running these days is your anti-virus application. Some of those have a setting in them that prevents VBA code from running, and if yours is one of those (I use Kaspersky Business Optimal and it has such a setting) then you'll need to set it to allow you to run the code. "Pammi J" wrote: Hi hope someone can help me. I have an invoice i have designed and the last bit i need before i use it is to assign a invoice number to the template and have it automatically increase by one everytime - ie 101 would be 102 on opening the next time. i have read McGimpseys page and used the code - i put it in the this workbook part but its not doing anything. Have i got the right code or do i need any other bits to go with it? Also i dont understand what `text` file i need - as its saying to create one? and what is default path as string - is that where i type where my template is. Sorry im a newbie to invoice numbering. but i only need this and then i can move on to other things. many thanks |
#7
|
|||
|
|||
Auto Number in Invoice
Aha!
In the definition for Const sDEFAULT_PATH, get rid of the and at the beginning and end of the string - they should not be in there. It should look like this: Const sDEFAULT_PATH As String = "C:\Documents and Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices" Plus, you remember that your own SetUpNewClient routine has to be run from Tools | Macro | Macros I messed with this some, and tried to 'read your mind' a little, I may have failed at that. I'm assuming that a customer/client's name is going to be in E5 or else E5 may be totally empty. This code takes both cases into account. I moved the Const declarations out of the sub into the body of the module and made them Public Constants so that both routines can reference them to get their values. I didn't change too much in the Function but I beat up on the Sub a bit to get it to decide whether to use whatever is in E5 as the basis for a filename, to see if it exists already (existing customer) or if this is a new customer and starting from ground zero. Hopefully the comments will explain it all well enough. Here goes - replaces all that you currently have (the board will rpobably wreak havoc on some of the longer lines of code, watch out - like your path should all be on a single line, the rest I think I broke properly) Just in case, I uploaded a working version that you can get he http://www.jlathamsite.com/uploads/for_pammij.xls Public Const sDEFAULT_PATH As String _ = "C:\Documents and Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices\" Public Const sDEFAULT_FNAME = "inv no.txt" Public Function NextSeqNumber(Optional sFileName As String, _ Optional nSeqNumber As Long = -1) As Long Dim nFileNumber As Long nFileNumber = FreeFile If IsMissing(sFileName) Or sFileName = "" Then sFileName = sDEFAULT_FNAME End If If InStr(sFileName, Application.PathSeparator) = 0 Then sFileName = sDEFAULT_PATH & Application.PathSeparator _ & sFileName End If If nSeqNumber = -1& Then If Dir(sFileName) "" Then Open sFileName For Input As nFileNumber Input #nFileNumber, nSeqNumber nSeqNumber = nSeqNumber + 1& Close nFileNumber Else nSeqNumber = 1& End If End If On Error GoTo PathError Open sFileName For Output As nFileNumber On Error GoTo 0 Print #nFileNumber, nSeqNumber Close nFileNumber NextSeqNumber = nSeqNumber Exit Function PathError: NextSeqNumber = -1& End Function Public Sub SetUpNewClient() Dim ClientInvoiceNumberFile As String Dim SequenceNumberMinus1 As Long Dim sFileName As String Dim nFileNumber As Integer With ThisWorkbook.Sheets(1) If IsEmpty(.Range("E5")) Then 'will force read from default .txt file ClientInvoiceNumberFile = sDEFAULT_FNAME Else 'will read from specified file ClientInvoiceNumberFile = .Range("E5") & ".txt" End If sFileName = sDEFAULT_PATH & Application.PathSeparator _ & ClientInvoiceNumberFile If Dir$(sFileName) = "" Then 'file does not exist, New Customer! 'create unique file for them nFileNumber = FreeFile() Open sFileName For Output As nFileNumber Print #nFileNumber, 849 ' to start at 850 for new customers Close #nFileNumber Else 'the file exists and so we 'presume it is an existing customer with 'existing invoice number in it 'and we DO NOTHING End If 'now we have a file that exists and 'it has some number in it 'call the function to do the work .Range("B2").Value = NextSeqNumber(sFileName, -1) End With End Sub "Pammi J" wrote: Hi and many thanks for your quick reply. I have done that and saved my invoice as a template but when i open there is no number in my e5 cell. Here is the code i put in Have i done something wrong - my macro security is med and firewall ect is not preventing macros from running. Public Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long Const sDEFAULT_PATH As String = "C:\Documents and Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices" Const sDEFAULT_FNAME As String = "inv no.txt" Dim nFileNumber As Long nFileNumber = FreeFile If sFileName = "" Then sFileName = sDEFAULT_FNAME If InStr(sFileName, Application.PathSeparator) = 0 Then _ sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName If nSeqNumber = -1& Then If Dir(sFileName) "" Then Open sFileName For Input As nFileNumber Input #nFileNumber, nSeqNumber nSeqNumber = nSeqNumber + 1& Close nFileNumber Else nSeqNumber = 1& End If End If On Error GoTo PathError Open sFileName For Output As nFileNumber On Error GoTo 0 Print #nFileNumber, nSeqNumber Close nFileNumber NextSeqNumber = nSeqNumber Exit Function PathError: NextSeqNumber = -1& End Function Public Sub SetUpNewClient() With ThisWorkbook.Sheets(1) .Range("B2").Value = NextSeqNumber(.Range("E5").Value & ".txt", 849) End With End Sub "JLatham" wrote: Sounds like your problem is with the two Const statements at the beginning of the function? ----- Const sDEFAULT_PATH As String = "your path here" Const sDEFAULT_FNAME As String = "defaultseq.txt" ----- The first one should be set to the path to where the text file containing the sequence of numbers is kept. The "your path here" part should actually look something like this: "C:\Documents and Settings\PammiJ\My Documents\" For the second line, yes you need to create a file somewhere (in the folder pointed to by the first line) and all it needs to contain initially is the first invoice number you want to use, minus 1. Create the file using Notepad: open Notepad, start a new file, just type a number into it (assume you want invoices to start at 1, type 0 into it). Then use Save As to give it a name like the one he suggested or you could give it some other name like PammiJs_InvoiceNumbers.txt and save it in the path you have decided on. Close Notepad. Now your first two lines in that function should look something like this: Const sDEFAULT_PATH As String = "C:\Documents and Settings\PammiJ\My Documents\" Const sDEFAULT_FNAME As String = "PammiJs_InvoiceNumbers.txt" I think the McGimpsey page uses cell B2 as the one where the invoice number is to appear, just change "B2" in his sample code to "E5" and it should work. Only thing that might keep it from working is your Macro Security - make sure macros are permitted to run - best setting that's a balance of security and convenience is MEDIUM where you are prompted when the workbook is opened whether or not to let macro code execute. That will keep you from being unpleasantly surprised if someone sends you an Excel workbook with code in it that you weren't expecting. One other thing that can also keep macros from running these days is your anti-virus application. Some of those have a setting in them that prevents VBA code from running, and if yours is one of those (I use Kaspersky Business Optimal and it has such a setting) then you'll need to set it to allow you to run the code. "Pammi J" wrote: Hi hope someone can help me. I have an invoice i have designed and the last bit i need before i use it is to assign a invoice number to the template and have it automatically increase by one everytime - ie 101 would be 102 on opening the next time. i have read McGimpseys page and used the code - i put it in the this workbook part but its not doing anything. Have i got the right code or do i need any other bits to go with it? Also i dont understand what `text` file i need - as its saying to create one? and what is default path as string - is that where i type where my template is. Sorry im a newbie to invoice numbering. but i only need this and then i can move on to other things. many thanks |
#8
|
|||
|
|||
Auto Number in Invoice
isnt there an easier way to add a formula into the cell where the invoice
number is shown on the template to create auto numbering ? Real confused here, looks like programing to me. Thought Excel was already complete with automating codes for this sort of problem ? -- lizard1socal "JLatham" wrote: Aha! In the definition for Const sDEFAULT_PATH, get rid of the and at the beginning and end of the string - they should not be in there. It should look like this: Const sDEFAULT_PATH As String = "C:\Documents and Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices" Plus, you remember that your own SetUpNewClient routine has to be run from Tools | Macro | Macros I messed with this some, and tried to 'read your mind' a little, I may have failed at that. I'm assuming that a customer/client's name is going to be in E5 or else E5 may be totally empty. This code takes both cases into account. I moved the Const declarations out of the sub into the body of the module and made them Public Constants so that both routines can reference them to get their values. I didn't change too much in the Function but I beat up on the Sub a bit to get it to decide whether to use whatever is in E5 as the basis for a filename, to see if it exists already (existing customer) or if this is a new customer and starting from ground zero. Hopefully the comments will explain it all well enough. Here goes - replaces all that you currently have (the board will rpobably wreak havoc on some of the longer lines of code, watch out - like your path should all be on a single line, the rest I think I broke properly) Just in case, I uploaded a working version that you can get he http://www.jlathamsite.com/uploads/for_pammij.xls Public Const sDEFAULT_PATH As String _ = "C:\Documents and Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices\" Public Const sDEFAULT_FNAME = "inv no.txt" Public Function NextSeqNumber(Optional sFileName As String, _ Optional nSeqNumber As Long = -1) As Long Dim nFileNumber As Long nFileNumber = FreeFile If IsMissing(sFileName) Or sFileName = "" Then sFileName = sDEFAULT_FNAME End If If InStr(sFileName, Application.PathSeparator) = 0 Then sFileName = sDEFAULT_PATH & Application.PathSeparator _ & sFileName End If If nSeqNumber = -1& Then If Dir(sFileName) "" Then Open sFileName For Input As nFileNumber Input #nFileNumber, nSeqNumber nSeqNumber = nSeqNumber + 1& Close nFileNumber Else nSeqNumber = 1& End If End If On Error GoTo PathError Open sFileName For Output As nFileNumber On Error GoTo 0 Print #nFileNumber, nSeqNumber Close nFileNumber NextSeqNumber = nSeqNumber Exit Function PathError: NextSeqNumber = -1& End Function Public Sub SetUpNewClient() Dim ClientInvoiceNumberFile As String Dim SequenceNumberMinus1 As Long Dim sFileName As String Dim nFileNumber As Integer With ThisWorkbook.Sheets(1) If IsEmpty(.Range("E5")) Then 'will force read from default .txt file ClientInvoiceNumberFile = sDEFAULT_FNAME Else 'will read from specified file ClientInvoiceNumberFile = .Range("E5") & ".txt" End If sFileName = sDEFAULT_PATH & Application.PathSeparator _ & ClientInvoiceNumberFile If Dir$(sFileName) = "" Then 'file does not exist, New Customer! 'create unique file for them nFileNumber = FreeFile() Open sFileName For Output As nFileNumber Print #nFileNumber, 849 ' to start at 850 for new customers Close #nFileNumber Else 'the file exists and so we 'presume it is an existing customer with 'existing invoice number in it 'and we DO NOTHING End If 'now we have a file that exists and 'it has some number in it 'call the function to do the work .Range("B2").Value = NextSeqNumber(sFileName, -1) End With End Sub "Pammi J" wrote: Hi and many thanks for your quick reply. I have done that and saved my invoice as a template but when i open there is no number in my e5 cell. Here is the code i put in Have i done something wrong - my macro security is med and firewall ect is not preventing macros from running. Public Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long Const sDEFAULT_PATH As String = "C:\Documents and Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices" Const sDEFAULT_FNAME As String = "inv no.txt" Dim nFileNumber As Long nFileNumber = FreeFile If sFileName = "" Then sFileName = sDEFAULT_FNAME If InStr(sFileName, Application.PathSeparator) = 0 Then _ sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName If nSeqNumber = -1& Then If Dir(sFileName) "" Then Open sFileName For Input As nFileNumber Input #nFileNumber, nSeqNumber nSeqNumber = nSeqNumber + 1& Close nFileNumber Else nSeqNumber = 1& End If End If On Error GoTo PathError Open sFileName For Output As nFileNumber On Error GoTo 0 Print #nFileNumber, nSeqNumber Close nFileNumber NextSeqNumber = nSeqNumber Exit Function PathError: NextSeqNumber = -1& End Function Public Sub SetUpNewClient() With ThisWorkbook.Sheets(1) .Range("B2").Value = NextSeqNumber(.Range("E5").Value & ".txt", 849) End With End Sub "JLatham" wrote: Sounds like your problem is with the two Const statements at the beginning of the function? ----- Const sDEFAULT_PATH As String = "your path here" Const sDEFAULT_FNAME As String = "defaultseq.txt" ----- The first one should be set to the path to where the text file containing the sequence of numbers is kept. The "your path here" part should actually look something like this: "C:\Documents and Settings\PammiJ\My Documents\" For the second line, yes you need to create a file somewhere (in the folder pointed to by the first line) and all it needs to contain initially is the first invoice number you want to use, minus 1. Create the file using Notepad: open Notepad, start a new file, just type a number into it (assume you want invoices to start at 1, type 0 into it). Then use Save As to give it a name like the one he suggested or you could give it some other name like PammiJs_InvoiceNumbers.txt and save it in the path you have decided on. Close Notepad. Now your first two lines in that function should look something like this: Const sDEFAULT_PATH As String = "C:\Documents and Settings\PammiJ\My Documents\" Const sDEFAULT_FNAME As String = "PammiJs_InvoiceNumbers.txt" I think the McGimpsey page uses cell B2 as the one where the invoice number is to appear, just change "B2" in his sample code to "E5" and it should work. Only thing that might keep it from working is your Macro Security - make sure macros are permitted to run - best setting that's a balance of security and convenience is MEDIUM where you are prompted when the workbook is opened whether or not to let macro code execute. That will keep you from being unpleasantly surprised if someone sends you an Excel workbook with code in it that you weren't expecting. One other thing that can also keep macros from running these days is your anti-virus application. Some of those have a setting in them that prevents VBA code from running, and if yours is one of those (I use Kaspersky Business Optimal and it has such a setting) then you'll need to set it to allow you to run the code. "Pammi J" wrote: Hi hope someone can help me. I have an invoice i have designed and the last bit i need before i use it is to assign a invoice number to the template and have it automatically increase by one everytime - ie 101 would be 102 on opening the next time. i have read McGimpseys page and used the code - i put it in the this workbook part but its not doing anything. Have i got the right code or do i need any other bits to go with it? Also i dont understand what `text` file i need - as its saying to create one? and what is default path as string - is that where i type where my template is. Sorry im a newbie to invoice numbering. but i only need this and then i can move on to other things. many thanks |
#9
|
|||
|
|||
Auto Number in Invoice
-- lizard1socal "lizard1socal" wrote: isnt there an easy way to click a button on the toolbar to add a formula into the cell where the invoice number is shown on the template to create auto numbering ? Real confused here, looks like programing to me. Thought Excel was already complete with automating codes for this sort of problem ? Is it really that hard to enable the numbering function as to have to write a program ? -- lizard1socal |
#10
|
|||
|
|||
Auto Number in Invoice
You may be thinking of Access's AutoNumber type which is an auto-numbering
feature that can be used in tables for a variety of purposes, the most typical being that of providing a unique key field value for a table. To the best of my knowledge there is no built in function to simply auto-increment a value in Excel based on some action like the opening of a workbook. Excel contains many incredibly powerful functions; I've an internet acquaintance that has kind of put me to shame several times in coming up with a one-line cell formula (albeit somewhat complex usually) to do what I took many lines of VBA code to achieve. But in some instances there are simply things that cannot be done with a worksheet function. I believe this is one of them. Now there is a simple formula such as =A1+1 which would add one to the value in A1 ... but A1 has no real way of getting automatically updated, so the value in the cell with the formula in it never really changes either. There is no command like =ME+1 which is my fictitious way of creating a formula that would increment itself. I've seen many ways to try to set up an auto-incrementing scheme, and all that I remember are coded at some level or another. Besides the use of a text file here to hold the last used invoice number, one way I've seen is to use Excel's Define Names function to store the value in a Name within the workbook itself. That's a neat little way to do it also, but involves code also. Other schemes involve using a hidden cell somewhere to hold the last invoice number used and then take that value and increment it when the workbook is opened or when the user chooses to run a macro to assign the next invoice number to a cell. Again, some code is required. There are basically two issues to be dealt with in trying to get an auto incrementing number that is persistent between uses of the workbook: #1 - determining where to store the value that is the basis for the next number in sequence, and #2 - determining/controlling exactly when that number is incremented. It would appear that it takes some VBA code to provide this functionality. "lizard1socal" wrote: isnt there an easier way to add a formula into the cell where the invoice number is shown on the template to create auto numbering ? Real confused here, looks like programing to me. Thought Excel was already complete with automating codes for this sort of problem ? -- lizard1socal "JLatham" wrote: Aha! In the definition for Const sDEFAULT_PATH, get rid of the and at the beginning and end of the string - they should not be in there. It should look like this: Const sDEFAULT_PATH As String = "C:\Documents and Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices" Plus, you remember that your own SetUpNewClient routine has to be run from Tools | Macro | Macros I messed with this some, and tried to 'read your mind' a little, I may have failed at that. I'm assuming that a customer/client's name is going to be in E5 or else E5 may be totally empty. This code takes both cases into account. I moved the Const declarations out of the sub into the body of the module and made them Public Constants so that both routines can reference them to get their values. I didn't change too much in the Function but I beat up on the Sub a bit to get it to decide whether to use whatever is in E5 as the basis for a filename, to see if it exists already (existing customer) or if this is a new customer and starting from ground zero. Hopefully the comments will explain it all well enough. Here goes - replaces all that you currently have (the board will rpobably wreak havoc on some of the longer lines of code, watch out - like your path should all be on a single line, the rest I think I broke properly) Just in case, I uploaded a working version that you can get he http://www.jlathamsite.com/uploads/for_pammij.xls Public Const sDEFAULT_PATH As String _ = "C:\Documents and Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices\" Public Const sDEFAULT_FNAME = "inv no.txt" Public Function NextSeqNumber(Optional sFileName As String, _ Optional nSeqNumber As Long = -1) As Long Dim nFileNumber As Long nFileNumber = FreeFile If IsMissing(sFileName) Or sFileName = "" Then sFileName = sDEFAULT_FNAME End If If InStr(sFileName, Application.PathSeparator) = 0 Then sFileName = sDEFAULT_PATH & Application.PathSeparator _ & sFileName End If If nSeqNumber = -1& Then If Dir(sFileName) "" Then Open sFileName For Input As nFileNumber Input #nFileNumber, nSeqNumber nSeqNumber = nSeqNumber + 1& Close nFileNumber Else nSeqNumber = 1& End If End If On Error GoTo PathError Open sFileName For Output As nFileNumber On Error GoTo 0 Print #nFileNumber, nSeqNumber Close nFileNumber NextSeqNumber = nSeqNumber Exit Function PathError: NextSeqNumber = -1& End Function Public Sub SetUpNewClient() Dim ClientInvoiceNumberFile As String Dim SequenceNumberMinus1 As Long Dim sFileName As String Dim nFileNumber As Integer With ThisWorkbook.Sheets(1) If IsEmpty(.Range("E5")) Then 'will force read from default .txt file ClientInvoiceNumberFile = sDEFAULT_FNAME Else 'will read from specified file ClientInvoiceNumberFile = .Range("E5") & ".txt" End If sFileName = sDEFAULT_PATH & Application.PathSeparator _ & ClientInvoiceNumberFile If Dir$(sFileName) = "" Then 'file does not exist, New Customer! 'create unique file for them nFileNumber = FreeFile() Open sFileName For Output As nFileNumber Print #nFileNumber, 849 ' to start at 850 for new customers Close #nFileNumber Else 'the file exists and so we 'presume it is an existing customer with 'existing invoice number in it 'and we DO NOTHING End If 'now we have a file that exists and 'it has some number in it 'call the function to do the work .Range("B2").Value = NextSeqNumber(sFileName, -1) End With End Sub "Pammi J" wrote: Hi and many thanks for your quick reply. I have done that and saved my invoice as a template but when i open there is no number in my e5 cell. Here is the code i put in Have i done something wrong - my macro security is med and firewall ect is not preventing macros from running. Public Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long Const sDEFAULT_PATH As String = "C:\Documents and Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices" Const sDEFAULT_FNAME As String = "inv no.txt" Dim nFileNumber As Long nFileNumber = FreeFile If sFileName = "" Then sFileName = sDEFAULT_FNAME If InStr(sFileName, Application.PathSeparator) = 0 Then _ sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName If nSeqNumber = -1& Then If Dir(sFileName) "" Then Open sFileName For Input As nFileNumber Input #nFileNumber, nSeqNumber nSeqNumber = nSeqNumber + 1& Close nFileNumber Else nSeqNumber = 1& End If End If On Error GoTo PathError Open sFileName For Output As nFileNumber On Error GoTo 0 Print #nFileNumber, nSeqNumber Close nFileNumber NextSeqNumber = nSeqNumber Exit Function PathError: NextSeqNumber = -1& End Function Public Sub SetUpNewClient() With ThisWorkbook.Sheets(1) .Range("B2").Value = NextSeqNumber(.Range("E5").Value & ".txt", 849) End With End Sub "JLatham" wrote: Sounds like your problem is with the two Const statements at the beginning of the function? ----- Const sDEFAULT_PATH As String = "your path here" Const sDEFAULT_FNAME As String = "defaultseq.txt" ----- The first one should be set to the path to where the text file containing the sequence of numbers is kept. The "your path here" part should actually look something like this: "C:\Documents and Settings\PammiJ\My Documents\" For the second line, yes you need to create a file somewhere (in the folder pointed to by the first line) and all it needs to contain initially is the first invoice number you want to use, minus 1. Create the file using Notepad: open Notepad, start a new file, just type a number into it (assume you want invoices to start at 1, type 0 into it). Then use Save As to give it a name like the one he suggested or you could give it some other name like PammiJs_InvoiceNumbers.txt and save it in the path you have decided on. Close Notepad. Now your first two lines in that function should look something like this: Const sDEFAULT_PATH As String = "C:\Documents and Settings\PammiJ\My Documents\" Const sDEFAULT_FNAME As String = "PammiJs_InvoiceNumbers.txt" I think the McGimpsey page uses cell B2 as the one where the invoice number is to appear, just change "B2" in his sample code to "E5" and it should work. Only thing that might keep it from working is your Macro Security - make sure macros are permitted to run - best setting that's a balance of security and convenience is MEDIUM where you are prompted when the workbook is opened whether or not to let macro code execute. That will keep you from being unpleasantly surprised if someone sends you an Excel workbook with code in it that you weren't expecting. One other thing that can also keep macros from running these days is your anti-virus application. Some of those have a setting in them that prevents VBA code from running, and if yours is one of those (I use Kaspersky Business Optimal and it has such a setting) then you'll need to set it to allow you to run the code. "Pammi J" wrote: Hi hope someone can help me. I have an invoice i have designed and the last bit i need before i use it is to assign a invoice number to the template and have it automatically increase by one everytime - ie 101 would be 102 on opening the next time. i have read McGimpseys page and used the code - i put it in the this workbook part but its not doing anything. Have i got the right code or do i need any other bits to go with it? Also i dont understand what `text` file i need - as its saying to create one? and what is default path as string - is that where i type where my template is. Sorry im a newbie to invoice numbering. but i only need this and then i can move on to other things. many thanks |
Thread Tools | |
Display Modes | |
|
|