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