[Solved] Entering date and invoice number automatically in i

[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?

[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.

[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?

[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.

[randtek]

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?

[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...

[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 ...)

[Zizi64]

Invoice.ods

(25.31 KiB) Downloaded 521 times

[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].

[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.

[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!

[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