I have a template for creating Invoices in LibreOffice Calc, and I would like to be able to generate new invoices from this template. Currently I have a macro in place to increase the invoice number field by 1, but if possible I would like to be able to have this expanded to then create a duplicate file with the new invoice number in the file name. I’m very inexperienced with macros, and I have almost no prior programming experience, so I’m struggling to make this happen.
If anyone could steer me in the right direction that would be much appreciated
Welcome, Aaron!
Where did you get it? And what does it look like?
Tell me a little more about the scenario you want to implement: you create an invoice from a template, the number is increased by 1 compared to the previous one, the document is open on the screen and…? What next?
Hi,
Currently I have attempted to have the following script to change my invoice number:
Sub Increment
Dim oCellRangeByName As Variant
oCellRangeByName = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("F5")
oCellRangeByName.setValue(oCellRangeByName.getValue()+1.0)
End Sub
This is actually taken from a comment which I believe you wrote, taken from a ten year old topic. What I would like to be able to do is increment the invoice number by one in the master document, then copy the spreadsheet to a new file with the invoice number as the filename to create each individual invoice. Then, in the new copy, I can add the specific details such as client, goods etc. without editing my master template, apart from the number. I feel I’m not explaining very well, but is there a way to automate most of this process?
Of course, you can automate everything you need. You just need to think through the task carefully. As far as I understood the text of the macro, it should work not with the template, but with the last of the created invoices (in the template, in cell F5 there will always be the same number, increasing it by 1 will not give the desired effect). Do you save all issued invoices in one folder? And never delete old or erroneous ones? Perhaps we could approach the solution of the problem from this side. The macro could look through the existing files in the directory, select the file with the highest number and then add 1 to it. You enter the invoice number in the file name, don’t you?
Well, as far as working off of my established system, I don’t really have one yet, having just started and being a very small agricultural operation.
Is it sensible to increase cell F5 by 1 in the original, then create a copy where F5 contains the new value, meaning each new copy created from the original will have a greater value than the last? Or is there a better way to keep this organised?
My end goal is to be able to create invoives with unique invoice reference numbers, and to be able to fill them out easily without risking altering my master file apart from the invoice number if required
That’s great! That’s just amazing! I mean, it’s great that you asked for help early on, before your not-yet-created system started to fall apart.
Yes of course, there is a much better way to build this system. @Villeroy has written about this more than once - you need not Calc, but Base.
You can find this here in many of his posts, I’ll just try to summarize the points.
Users try to use spreadsheets as a replacement for a real database because of the external similarity of the grid, because of many years of Microsoft propaganda “a database is complicated, it is for specialists, a beginner will not cope with it until he attends our paid courses” and “using Excel tables as a simple database is possible and even necessary - you just need a few macros.” This is propaganda of false ideas.
The emergence of these ideas is caused by the corporation’s policy - having divided office products into separate components, each of which must be purchased separately, they encountered the reluctance of customers to buy Access. In the case of LibreOffice, there is no such problem - the office shell includes both a calculator and a database.
I don’t think that a database for your small (hopefully temporarily small, I sincerely wish you success and expansion) business will be too complicated. It will store in one place data on all your clients, on issued invoices and on their payment dates, on the shipment of goods. And, of course, it will allow you to create a printed form of an invoice to send to the client, with a list of items, the cost of each item, number, date, your logo and a bunch of everything that should be in this document.
Yes, I understand that you are afraid to use BASE, subconsciously you think that it is too difficult. Try watching a few videos on YouTube - maybe your fears will disappear. I liked this course - https://youtu.be/jWU0WdMJ2Kw?list=PLy7Kah3WzqrEerJ0VPNWVaR4CYHMr4wmV
And of course, if you have any additional questions or need help, you can always find helpers here.
Simple draft of an invoice database with clients, articles, invoices and changing prices. How to manage LibreOffice Base DataBases? - #13 by Villeroy (no macros, simple lists with name, ID only).
1 Like