I create invoices in Excel and would like to create a macro or some other way
of inserting the next invoice number automatically.

Any ideas anyone?

See J.E Excel pages
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Rod Ben" <Rod wrote in message ...
I create invoices in Excel and would like to create a macro or some other way
of inserting the next invoice number automatically.

Any ideas anyone?

Hi Ron

Thanks for this but i cant seem to get it to work. I have copied the code
into a module as suggested on the web site and i can get the macro to run but
get an error message "ambigous name detected : Next SeqNumber".

I am very much in the dark with this code. Anything obvious i am doing wrong?

The code i have used is:

Sub Getinvoicenumber()
Public Function NextSeqNumber(Optional sFileName As String, Optional
nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "C:\Documents and Settings\Rod
Bennett\Desktop"
Const sDEFAULT_FNAME As String = "defaultseq.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 Sub
End Sub


Any help greatly appreciated.

Thanks.

Rod

"Ron de Bruin" wrote:

See J.E Excel pages
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Rod Ben" <Rod wrote in message ...
I create invoices in Excel and would like to create a macro or some other way
of inserting the next invoice number automatically.

Any ideas anyone?

You copy a function in a macro and that is not correct

Read the page again and copy this function and sub in a normal module
Note I use C:\

Now run the macro a few times to test

Public Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "C:\"
Const sDEFAULT_FNAME As String = "defaultseq.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 NewClientInvoice()
ThisWorkbook.Sheets(1).Range("B2").Value = NextSeqNumber("Client1.txt")
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Rod Ben" wrote in message ...
Hi Ron

Thanks for this but i cant seem to get it to work. I have copied the code
into a module as suggested on the web site and i can get the macro to run but
get an error message "ambigous name detected : Next SeqNumber".

I am very much in the dark with this code. Anything obvious i am doing wrong?

The code i have used is:

Sub Getinvoicenumber()
Public Function NextSeqNumber(Optional sFileName As String, Optional
nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "C:\Documents and Settings\Rod
Bennett\Desktop"
Const sDEFAULT_FNAME As String = "defaultseq.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 Sub
End Sub


Any help greatly appreciated.

Thanks.

Rod

"Ron de Bruin" wrote:

See J.E Excel pages
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Rod Ben" <Rod wrote in message ...
I create invoices in Excel and would like to create a macro or some other way
of inserting the next invoice number automatically.

Any ideas anyone?

Hi Ron

Works great!

Many thanks for your time.

Have a good weekend.

Rod

"Ron de Bruin" wrote:

You copy a function in a macro and that is not correct

Read the page again and copy this function and sub in a normal module
Note I use C:\

Now run the macro a few times to test

Public Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "C:\"
Const sDEFAULT_FNAME As String = "defaultseq.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 NewClientInvoice()
ThisWorkbook.Sheets(1).Range("B2").Value = NextSeqNumber("Client1.txt")
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Rod Ben" wrote in message ...
Hi Ron

Thanks for this but i cant seem to get it to work. I have copied the code
into a module as suggested on the web site and i can get the macro to run but
get an error message "ambigous name detected : Next SeqNumber".

I am very much in the dark with this code. Anything obvious i am doing wrong?

The code i have used is:

Sub Getinvoicenumber()
Public Function NextSeqNumber(Optional sFileName As String, Optional
nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "C:\Documents and Settings\Rod
Bennett\Desktop"
Const sDEFAULT_FNAME As String = "defaultseq.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 Sub
End Sub


Any help greatly appreciated.

Thanks.

Rod

"Ron de Bruin" wrote:

See J.E Excel pages
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Rod Ben" <Rod wrote in message ...
I create invoices in Excel and would like to create a macro or some other way
of inserting the next invoice number automatically.

Any ideas anyone?