Page 1 of 1
[Solved] Entering date and invoice number automatically in i
Posted: Wed Feb 18, 2015 2:16 pm
by randtek
I have an invoice spreadsheet I want to use for printing invoices for my customers. Is there a way to have it enter the current date in a cell only when creating a new invoice? I want my invoices to show the date they were created, but I would prefer not to have to type the dates in manually. Also, is there a way to have an invoice number field automatically increment each time a new invoice is created? Would this have to be done with a macro?
Re: Entering date and invoice number automatically in invoic
Posted: Wed Feb 18, 2015 2:32 pm
by Zizi64
OpenOffice 3.1 on Windows 7 Home Premium
that is a very old OOo version. You need install a newer ApacheOpenoffice and/or LibreOffice version. (or refrech your signature if you have newer version.
Is there a way to have it enter the current date in a cell only when creating a new invoice? I want my invoices to show the date they were created, but I would prefer not to have to type the dates in manually.
1.: You can use the 'NOW()' or 'TODAY()' function in the template of the invoice document. (the date will refresh, when you open the document in the future.
2.: You can achieve this task by macro:
Code: Select all
Sub MyDate
Dim MyRange As Object
Dim Cell As Object
Myrange = ThisComponent.NamedRanges.getByName("Date_of_Invoice")
Cell = MyRange.ReferredCells.getCellByPosition(0,0)
Cell.setValue(DateValue(Now))
End sub
You need create a Named Cell: "Date_of_Invoice", and must format it as needed. This date not refresh automatically, it will refresh only when you launch this macro, or you can adjust it manually.
Re: Entering date and invoice number automatically in invoic
Posted: Wed Feb 18, 2015 2:46 pm
by Zizi64
Also, is there a way to have an invoice number field automatically increment each time a new invoice is created?
The main question is: where you want to store the number of the last crated invoice? In the template of the invoice? In a database? In a separated spreadsheet file?
Re: Entering date and invoice number automatically in invoic
Posted: Wed Feb 18, 2015 2:48 pm
by randtek
Thank you for your quick and helpful reply. I will try that. Can the macro be set up to run automatically when the spreadsheet opens?
I am actually using Oo 4.1.1, and Windows 8.1. I have not been to this forum recently and did not think to update my profile. I will do that.
Re: Entering date and invoice number automatically in invoic
Posted: Wed Feb 18, 2015 3:10 pm
by randtek
Zizi64 wrote:Also, is there a way to have an invoice number field automatically increment each time a new invoice is created?
The main question is: where you want to store the number of the last crated invoice? In the template of the invoice? In a database? In a separated spreadsheet file?
I think the template would be the best option. I am guessing I could do this in the same macro? Possibly like this:
1. Load template
2. Rename and Save to the appropriate name for my invoice file system
3. Insert date using code provided above
4. Increment invoice using number in Invoice field and adding 1
5. Update template file with current invoice number.
I think I know what to do for everything but #5 now. Can you help me (or point me to resources/tutorials) see how to update another spreadsheet?
Re: Entering date and invoice number automatically in invoic
Posted: Wed Feb 18, 2015 3:15 pm
by Zizi64
Can the macro be set up to run automatically when the spreadsheet opens?
Yes, it can. But the date will refresh, when you reopen an archived invoice.
If this is acceptable to you, then you need use the simpliest NOW() or TODAY() function without any macro...
Re: Entering date and invoice number automatically in invoic
Posted: Wed Feb 18, 2015 3:32 pm
by Zizi64
I think the template would be the best option. I am guessing I could do this in the same macro? Possibly like this:
1. Load template
2. Rename and Save to the appropriate name for my invoice file system
3. Insert date using code provided above
4. Increment invoice using number in Invoice field and adding 1
5. Update template file with current invoice number.
I think I know what to do for everything but #5 now. Can you help me (or point me to resources/tutorials) see how to update another spreadsheet?
I think, you need use an another order:
1. Load template
2. Increment invoice using number in Invoice field and adding 1
3. Update template file with current invoice number.
4. Insert date using code provided above
5. Rename and Save to the appropriate name for my invoice file system
There is no any "another spreadsheet". There is ONE template file. You can "resave" it after increment of the number, and then can "save as" actual invoice.
Use the "StoreAsURL..." and "StoreToURL..." API functions with the appropriate export filter. There are examples in this Forum, and in the Andrew Pitonyak's documents.
viewtopic.php?f=20&t=7280
https://wiki.openoffice.org/wiki/Docume ... od_Options
http://www.google.hu/url?sa=t&rct=j&q=& ... 0519,d.ZWU
But what about the "abort", "cancel", "decrement" options? (when not required the opened and resaved invoice? But the number was incremented ...)
Re: Entering date and invoice number automatically in invoic
Posted: Wed Feb 18, 2015 3:45 pm
by Zizi64
Entering date and invoice number automatically in invoice
Posted: Wed Feb 18, 2015 3:51 pm
by randtek
Thank you for all of your assistance Tibor. You have answered my questions and given me the tools I needed to figure this out. I had not really considered the Cancel/Decrement issue. I will have to give that some thought. If you have suggestions about that, I would be interested to see them. If not, I will mark this post as [Solved].
Re: Entering date and invoice number automatically in invoic
Posted: Wed Feb 18, 2015 4:03 pm
by Zizi64
If you have suggestions about that, I would be interested to see them. If not, I will mark this post as [Solved].
I have many ideas an suggestions; the first is: maybe you need achieve this task based on a Database. (Sorry, I can not help you with the database management)
But it is not simple
job to plan and realize such invoicing system. I can not devote as much time as is necessary...
And maybe(?) my knowldge is not enough for it.
[SOLVED] Entering date and invoice number automatically in i
Posted: Wed Feb 18, 2015 4:22 pm
by randtek
Understood! Thank you so much for the assistance you have already given. I have downloaded and looked at the sample you provided. The "Refresh Date" button did not seem to work for me, but I am not concerned about that. I want to figure out how you created that "Refresh Date" button, and implement it in my Invoice, but I will work on that on my own, and create a new post if I get stuck. I am going to start digging in to that Macro document from Andrew you provided. It looks like it will be a great tool for me in the future!
Re: [SOLVED] Entering date and invoice number automatically
Posted: Wed Feb 18, 2015 4:32 pm
by Zizi64
The "Refresh Date" button did not seem to work for me,
The macro located in the document.
You need set the macro security to 'Medium', and then you need enable the macros located in the document at the opening procedure