Automatically Increment Invoice Number

  • kiwifella
    • #1

    Hello Gurus,

    I have figured out a simple way of creating sequential invoice numbers by creating a macro which simply copies the value of "1" & paste special/values/add this to existing number ie 1000 creating 1001 which is then "paste special" into the destination cell on invoice.

    This creates a rolling shedule ie next time it becomes 1002 etc.

    This works fine except that this "source" numbers register needs to be located in a separate spread sheet to the destination to make sure this system is not corruptible. The reason for this is I may be saving several copies of the invoicing spreadsheet as different client job's in progress, & I don't want to end up with double up invoice or quote numbers, hence for my pupose, a separate invoice number source or "register" if you like (in the form of a separate work book) is important.

    I have discovered I can still run the macro looking to this separate sheet to source the sequential number & it works perfectly, but only while the source work book is open.
    Can I make this work without having to open the source work book every time? Or alternatively can I tell the source workbook to automatically open when I open the destination (invoice) work book?

    Many thanks for your help in advance, FYI you will have to be fairly specific & use laymans terms! Thanks, your experience will be put to good use!

    • #2

    Re: Automactically open source workbook and run code to update active workbook

    Kiwifella,

    Welcome to Ozgrid.
    Please take the time to read the Forum Rules regarding thread titles. Thread titles should express a question and accurately reflect your need not express a plea for help (the obvious reason you came to the forum is for help).
    I have changed your thread title, but in the future, give more thought to crafting a relevant title. Key to a good thread title = think "search friendly." No one, using for search terms the words in your thread title, would find relevant results.

    Thanks,

    AAE

    • #3

    Re: Automactically open source workbook and run code to update active workbook

    OK thanks AAE

    • #4

    Re: Automactically open source workbook and run code to update active workbook

    Sounds like a workbook layout problem to me.

    In the SAME workbook - Could you not have one worksheet with the invoice layout on it and then on another worksheet a database type structure listing all the invoices and there relevant information. This way you can instantly look up any invoice from the same workbook and even incorporate a search function.

    Maybe you could post an example of your workbook?

    • #5

    Hi Reafidy,
    I like your thinking, however for the specific use that this workbook will be put to, sourcing the number & saving the records within itself will not work for me. This is because there will be several versions running which will have been "saved as" a specific job in progress, so potential for duplicate numbers. Hence the need to source from a central "register" if you like, all within the one folder.
    This is a calculating, quoting, price list, invoicing tool. I am doing a good turn for my brother in law who owns a roofing business, & I see how he spends hours every night manually calculating jobs, material, labour, typing quotes etc. I am using my (basic) excel skills to try to automate a lot of this for him, where he will be able simply punch in key criteria, m2 of roof area, tile type etc, & this tool will automatically crunch for him how many nails he will need, tiles, mtrs of battons, labour cost etc.
    Most of this is straight fwd basic calculations & formulas, however the trick is that when I observe the way he works & the flexibility he needs, I can see that he may have several quotes on the go, & even jobs in progress, which he will want to save as "(job Address)" & go back to the template for the next job, so sequential invoice numbers need to come from a central location, which could double as a log of Quote/Invoice numbers with details etc.
    I am imagining a macro button named "create Quote number" which would create sequential number, record on the central log, paste value onto quote/invoice.
    I can make all this happen if I have a 2nd spreadsheet (log) open at the same time, but not if it is closed. I need to make this simple to use as the recipients will be even less excel savy than I. Even if a workbook could be linked to open automatically when the main one is opened? Or you may have a better idea or know how to write to a register while closed?
    A huge thanks for taking an interest in my project, & for reading through this diatribe!!
    Kind Regards
    Kiwifella

    • #6

    1. make an invoicenumber independent of any program.
    2. make a file (with notepad for instance) and give it the name C:\20100001invoice.txt

    in VBA (Word, Excel, Access, n'importe) you can use

    1. Sub invoicenumber()
    2. invoicenum=Val(Dir("C:\*invoice.txt"))
    3. name "C:\" & Dir("C:\*invoice.txt") As "C:\" & invoicenum+1 & "invoice.txt"
    4. End Sub[


    This macro will provide the newest invoicenumber and renew the next invoicenumber as part of the filename.
    So there is no need to open any file in order to get the 'registered invoicenumbers'.

    • #7

    Thanks so much you guys are tremendous.
    Thanks snb, that should work. What about also writing to a log, ie keeping a log of invoice numbers & amounts etc, independant of main workbook/s in use?
    I thought of maybe using "personal macro workbook", do you know if i can store data there ie create an invoice log there, perhaps paste in as part of the macro?
    Thanks again I will have a crack your invoice number suggestion now.

    • #8

    To get an increasing invocenumber my suggestion is the simplest, fastest, multi-userest and fool-profest you can get.
    If you want to register the content of all the invoices you (or others) have made you'd best use

    1. Open "C:\invoiceregister.txt" for Append as #1
    2. Print #1, "your text"
    3. Close
    • #9

    I'm still not convinced you need seperate files. Attached is a screenshot of a VERY old invoicing program of ours. It had the ability to create/edit invoices as you please. You can then assign work done entries to each invoice with labour parts etc, and of course the invoice number is auto-incremented. The advantage is you can view/print/edit any invoice with the click of a button. There is no chance of duplicate invoices and you can keep a job going as long as you like.

    Anyway just an idea, snb has posted a good solution.

    • #10

    Hi Reafidy
    Looks pretty impressive mate but my workbook is pretty much built & is not that advanced!
    Many thanks for your suggestions.
    Cheers

    • #11

    Hi Snb
    Thanks heaps for these pointers.
    Unfortunately I am going to show my inexperiece here as I can't get this to work quite right.
    I have only "recorded" macros in the past, so don't know much about the codes or how to apply them.
    I had a crack at it, & I noticed the txt file name increases number every time I ran the macro so that seemed to work.
    My spreadsheet did not populate with a number however. The issue could be that I could not work out where to past the macro in VBA, so in the end I recorded a short macro named invoice number, then went in to edit where I found it & deleted it & pasted your code in. Looking at the code I took a guess that I needed to name my destination cell "invoicenum" would that be right? I did that but still no joy apart from the txt file did update beautifully!!!
    Also in terms of the record or "log" I want to keep, I want to include not just the Inv. number but date, job address. I could create a cell in the spreadsheet which contains all of these values together with a space between each by using the "&" formula, or is there a better way to make multiple entries on one line in the txt file?
    I am assuming the "your txt" part of the log code you have suggested is where I would insert the relevant cell number from my workbook to deliver the correct data to txt log, is that correct?
    Many thanks for sharing your expertise!!
    Kiwifella

    • #12

    Re: Automatically Increment Invoice Number

    Hi snb
    Many thanks for your useful & practical solutions - very clever!
    I do still need some assistance to make this work.
    I tried your suggestion 1. & I successfully got the txt file name changing by one increment so that worked beautifully.
    I was not successful however in getting this to write to a cell in my work book. This may have something to do with the fact that i was not sure quite how to apply your code. FYI my level of skill with macros to date has been limited to literally recording macros using the recorder, ie so simply recording some actions I want to repeat. I did try pasting the code into VBA editor, but then I could not find the macro to asign it to my workbook button.
    What I did therefore is I recorded a dummy macro named invoicenumber, deleted it & pasted your code in. I assumed from your code i must have to name a cell in my work book "invoicenum"? I did so but that cell did not return an invoice number when I ran the macro, however as I mentioned the txt file I created did increase each time, so that part certainly worked.
    Additionally for your suggestion to create an invoice register, I assume "your text" part of the code is where I would reference to a cell, ie A1?
    I would like to record data from several (4) cells in my workbook & write to register ie 1)invoice number, 2)customer, 3)$amount 4)date, I thought I could acheive this via the "&" formula writing all this to 1 cell then to your register, unless you know of a better way to acheive that?
    Hey, I really do appreciate your expertise & time on this, you are a life saver!!
    The work book is going to work brilliantly for what we need, & these 2 challenges are the last hurdle for me.
    Thankyou
    Kiwifella

    • #13

    Re: Automatically Increment Invoice Number

    What about:

    1. Sub invoicenumber()
    2. invoicenum=Val(Dir("C:\*invoice.txt"))
    3. name "C:\" & Dir("C:\*invoice.txt") As "C:\" & invoicenum+1 & "invoice.txt"
    4. sheets(1).range("A10")=invoicenum
    5. End Sub

    which can be replaced by

    1. Sub invoicenumber()
    2. sheet1.[A10]=Val(Dir("C:\*invoice.txt"))
    3. name "C:\" & Dir("C:\*invoice.txt") As "C:\" & sheet1.[A10]+1 & "invoice.txt"
    4. End Sub
    • #14

    Re: Automatically Increment Invoice Number

    Awesome, thanks snb