Automatic increase invoice numbers

[blacktie]

Hi

Thus far I could not find any joy solving my issue.
Would you be so kind in helping to sort this out

I need excel to check a folder D:/invoices on my pc and determine the highest number invoice and add (+1) to the highest number and write the number into sheet 'Entertainment Invoice'!H6 .


for example: file names are
432 Invoice Shelly De Wet - Velmore (28 Oct 2013) L'Aquila.xlsx
433 Invoice Trudie Delport - CSIR (30 Oct 2013) City Hall.xlsx

So if I open a new invoice the number 434 is automatically displayed into the new invoice sheet ...into 'Entertainment Invoice'!H6

Your help will be highly appreciated

PS.
Just to clarify.

When I open the invoice the invoice number is not filled in.

Invoices are located as seperately saved documents in .xlsx format in a folder (d:/invoices)
for example:
432 Invoice Shelly De Wet - Velmore (28 Oct 2013) L'Aquila.xlsx
433 Invoice Trudie Delport - CSIR (30 Oct 2013) City Hall.xlsx

So if I open a new invoice the next invoice number 434 is automatically displayed into sheet 'Entertainment Invoice'!H6

Kind regards

[hectop]

You can list the files in the folder with the DIR function but unfortunately there is no easy way of the largest number. You can't specify a sort order.
Something like this could work, but it's a bit cumbersome.
You need to uncomment the iFileNo = line. I didn't have a number of files with numbers in front to test on. You can see what would be returned with the msgbox code. You would remove that for the actual calculation.

You may also need to put in some logic in case you have any other files in the folder that don't start with a number.

Please Login or Register  to view this content.