I have several invoicingpoint-of-sale custom apps that stores the next
invoice number in the workbook for the period being invoiced (ie:
month, qtr, or fiscal year). This is stored in a CustomProperty named
"NextInvNum" as a number that gets appended to the invoice numbering
scheme. (In my app invoices are, by default, prefaced with the calendar
year followed by a hyphen and the invoice number) All invoices are
stored in a fiscal period file which, in most cases, is an entire
calendar year. Here's my file naming format...

CompanyName_YYYY_Invoices.xls

...where invoices are added whenever new ones are created for the
current year.


How it works:
An invoice template is stored as a XLT and used for adding new invoices
to the fiscal file via Sheets.Add Template:=gsDefaultInvTemplate.

The app reads the value stored in the CustomProperty named "NextInvNum"
and inserts the new invoice number into the range named "InvoiceNum",
then sets the sheetname to the same value, and finally sets the
CustomProperty value to the next invoice number. The invoice number
scheme is simply...

YYYY-###

...which can be modified to fit the user's scheme however desired. (The
default concept precludes that the year is the current calendar year,
and the delimiter is a hyphen) The actual format of the number digits
used is determined by the next invoice number. For example, #1000 will
display as 2013-1000, where the previous invoice number was 2013-999.
The first invoice number was 2013-001.

Each January the app[s] will create a new fiscal file for the new
calendar year. The template.xlt does not have a CustomProperty; this is
added when a new calendar year file is created.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion