invoice Numbers

mumra said:

Hello again!

Is it possible create a number counter/invoice number generator in Excel. e.g each time a new invoice is called up, Excel creates an invoice number one on from what it was?

Click to expand...

Mumra, you could use some code like this, it will add one to A1 when the workbook is opened, Is this what you need?

External file

Is it possible to make the number from an external file, eg counter.txt
And even better, make a button, that will open file, increase, fill the cell with number, save and close file?

Regards
Stig Arne

Hi,

Before you start, you need to setup the following:

1. Use Notepad (Start menu/Programs/Accessories/Notepad), and open a new file called "InvNo".
2. Put 1 in "InvNo" (see Note b) at the end of the macro).
3. Select a directory and save "InvNo" as a text file (see note c) below).
4. Put the following macro in your invoice workbook (standard module), and assign the macro to a button from the Forms menu. Save the invoice workbook as an Excel template.
5. Set up a directory to save your invoices. The macro is using:
ActiveWorkbook.SaveAs "C:\My Documents\MyInvoices\….." (change to suit)

Macro by Harald Staff (Microsoft MVP) with slight amendments:
<pre>
Sub mInvoiceNumber()
'by Harald Staff
Dim ThisInvoice As Long
Dim ReadText As String
Dim StoreFile As String

StoreFile = "C:\My Documents\Temp\InvNo.txt"
'replace with another path,
'network folder if multi-user

'read previous number:
If Dir(StoreFile) = "" Then 'not found
ThisInvoice = 1
Else
Open StoreFile For _
Input Access Read As #1
While Not EOF(1)
Line Input #1, ReadText
ThisInvoice = Val(ReadText)
Wend
Close #1
End If
ThisInvoice = ThisInvoice + 1

'Store this number:
Open StoreFile For _
Output Access Write As #1
Print #1, ThisInvoice
Close #1
With Range("A1") ' change to suit
.Value = "Invoice No.: " & "00" & ThisInvoice
End With
ActiveWorkbook.SaveAs "C:\My Documents\MyInvoices\Invoice No. " & "00" & ThisInvoice
End Sub
</pre>
Notes:
a) See note 1 above - you can call the text file whatever you want - "InvNo" is just an example (if you change the name, amend the macro).
b) See note 2 above - you need to start the invoice numbering system with some number (I have chosen 1, but you can start at any number you want). You can re-set the numbering at any time by changing the number in the "InvNo.txt" file.
c) If you are running your invoice system in a multi-user environment, put "InvNo.txt" in a public directory.
d) See these lines in the macro:

With Range("A1")
.Value = "Invoice No.: " & "00" & ThisInvoice
End With

Change the Range to suit. If you start the invoice number at 50, cell A1 will show "Invoice No.: 0050" (no quotes). Change the formatting in the above line to suit (but do no touch "& ThisInvoice").

HTH

Mike