Copy sheet with current date automatically

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
abinash1788
Posts: 8
Joined: Thu Apr 23, 2020 2:54 pm

Copy sheet with current date automatically

Post by abinash1788 »

I have a sheet named "Template" and want to copy sheet with name as current system date. Please help with this macro.
Open office 4.1.7
Operating System - macOS
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy sheet with current date automatically

Post by Zizi64 »

Please upload your tryings (sample file and the macro code). Then we will able help you.

We have not any information now, if you know anything about the macros, about the IDE, about the StarBasic, and about the API.
(IDE: Integrated Developing Environment; API: Application Programming Interface)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
abinash1788
Posts: 8
Joined: Thu Apr 23, 2020 2:54 pm

Re: Copy sheet with current date automatically

Post by abinash1788 »

Thanks for replying but I am new to macros. I don't have any code .
Open office 4.1.7
Operating System - macOS
joesch
Posts: 53
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Re: Copy sheet with current date automatically

Post by joesch »

A sheet has no date. So what's it really about?

If you want to copy a existing sheet of the current file to a new file, you can record it with the macro recorder:

Code: Select all

sub main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(1).Name = "Index"
args1(1).Value = 0
args1(0).Name = "Copy"
args1(0).Value = true

dispatcher.executeDispatch(document, ".uno:Move", "", 0, args1())
end sub
This is useful here, because it is not so easy with AOO to really copy a complete spreadsheet with native macro code, for example if it contains other objects (e.g. graphics).


Greetings,
joesch
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Copy sheet with current date automatically

Post by robleyd »

I think the poster is asking for a macro to create a copy of a sheet and use the current date as the name of the sheet.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
joesch
Posts: 53
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Re: Copy sheet with current date automatically

Post by joesch »

robleyd wrote:I think the poster is asking for a macro to create a copy of a sheet and use the current date as the name of the sheet.
in this Case he can use:

Code: Select all

sub copy_sheet()
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(2) as new com.sun.star.beans.PropertyValue
args1(0).Name = "DocName"
da = ThisComponent.Args
For i = 0 To UBOUND(da())
	If LCase(da(i).Name) = "title" Then args1(0).Value = da(i).Value
Next i
args1(1).Name = "Index"
args1(1).Value = 32767
args1(2).Name = "Copy"
args1(2).Value = true

dispatcher.executeDispatch(document, ".uno:Move", "", 0, args1())

'eleminate ":"
newName = Join(Split(CStr(Now()),":"),"-")

With ThisComponent.Sheets
	.getByIndex(.Count-1).Name = newName
End With

end sub
note:
This code works well for a German-language OO, for a foreign-language OO the following line may have to be modified to eliminate characters that must not exist in the table name:

Code: Select all

newName = Join(Split(CStr(Now()),":"),"-")
Greetings,
joesch
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy sheet with current date automatically

Post by Zizi64 »

abinash1788 wrote:Thanks for replying but I am new to macros. I don't have any code .
Then you must study these things first.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Copy sheet with current date automatically

Post by JohnSUN-Pensioner »

Dear @joesch, your solution has some minor flaws.
I respect your opinion, but I would not want the user who directly stated "I am new to macros" to start learning from such code samples.
Therefore, I will list those points that you did not think about when you proposed your solution.
Your code does not copy the sheet with the name "Template" (as indicated in the question), but the current sheet. If the user runs the macro from an another place, he will get the wrong result. If you knew about this feature of your decision, why didn't you warn about it?
If the question is about the current DATE, why do you use the Now () function to form the name of a new sheet? Current time is not necessary
You should not use the combination Join(Split(...)) to format the string. This is a good way to replace characters, but guessing which characters will need to be changed is very difficult. For example, for me, your construct created the string "04/24/2020 12-53-29", which cannot be used as the sheet name.
Finally, smart people recommend not using a macro recorder to create programs. The code is verbose, poorly readable, and poorly edited. API allows you to write short and efficient programs:

Code: Select all

Sub CreateNewSheetByDate
Dim oSheets As Variant
Dim sNewName As Variant
	oSheets = ThisComponent.getSheets()
	sNewName = Format(Date(), "YYYY-MM-DD")
	If Not oSheets.hasByName(sNewName) Then oSheets.copyByName("Template", sNewName, 32767)
End Sub
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
joesch
Posts: 53
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Re: Copy sheet with current date automatically

Post by joesch »

Your code does not copy the sheet with the name "Template" (as indicated in the question), but the current sheet.
Yes, that's right. Sorry, I overlooked that the sheet called "Template" is to be copied.
If you knew about this feature of your decision, why didn't you warn about it?
Yes, I knew about it. I didn't warn about it because I saw no need.
If the question is about the current DATE, why do you use the Now () function to form the name of a new sheet?
Because I was not sure if the questioner really only meant the date.
You should not use the combination Join(Split(...)) to format the string.
I don't see any reason why I shouldn't do this, because it is, for the specific case, convenient to use.
I have also pointed out problems in other languages, but I don't have an overview of the default formatting for system date (system time) for all languages in AOO.
Finally, smart people recommend not using a macro recorder to create programs.
You're absolutely(!) right. Therefore I myself recommend the macro recorder only in exceptional cases.


That's why I had emphasized that this is a special case, because it is unknown what the spreadsheet to be copied contains. If it contains objects, your code will not work properly.
(By the way, I know that the situation with LO is different, only the user asked for AOO and I see no other way if I want to make sure that the whole sheet is really copied).


Greetings,
joesch
joesch
Posts: 53
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Re: Copy sheet with current date automatically

Post by joesch »

That's why I had emphasized that this is a special case, because it is unknown what the spreadsheet to be copied contains. If it contains objects, your code will not work properly.
Oh, I see... I probably should have tested before posting.
Your code works correctly even in the presence of objects. Sorry for my false statement.



Greetings,
joesch
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Copy sheet with current date automatically

Post by JohnSUN-Pensioner »

In fairness, my macro is also not without flaws. If the current book does not have a sheet with the name "Template", a runtime error will be received. The correct condition may look like this:

Code: Select all

	If Not oSheets.hasByName(sNewName) And _
		oSheets.hasByName("Template") Then _
			oSheets.copyByName("Template", sNewName, 32767)
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
abinash1788
Posts: 8
Joined: Thu Apr 23, 2020 2:54 pm

Re: Copy sheet with current date automatically

Post by abinash1788 »

This macro is working fine but I added a button using Form control and assigned this macro under "Mouse pressed" event but not working. Is it possible to assign keyboard shortcut in code itself.
Open office 4.1.7
Operating System - macOS
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Copy sheet with current date automatically

Post by Lupp »

-0- The sheet-per-date design should be thoroughly considered. It's suspected to cause problems.
-1- Sheetnames containing special characters are bad. Better start with a letter and append letters, digits and underscores exclusivley then. Otherwise the names need disambiguation by singlequotes, causing complications with addressing in Calc an in user code as well.
-2- If you want to trigger macro excution with the help of a form button, assign the macro to the event 'Execute action'.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Mr.Dandy
Posts: 427
Joined: Tue Dec 11, 2012 4:22 pm

Re: Copy sheet with current date automatically

Post by Mr.Dandy »

Which macro did you talking about?
But now you can provide a document, right?
OpenOffice 4.1.12 - Windows 10
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Copy sheet with current date automatically

Post by JohnSUN-Pensioner »

Did you try to put a button on the Template sheet? It is a bad idea. When copying a template to a new sheet, you will receive a copy of this button on each new sheet - I don’t think that you want this.

Choose Tools - Customize - Toolbars tab.
SetMacroToPanel.png
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Post Reply