Copy sheet with current date automatically

Creating a macro - Writing a Script - Using the API

Copy sheet with current date automatically

Postby abinash1788 » Fri Apr 24, 2020 1:04 am

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
abinash1788
 
Posts: 8
Joined: Thu Apr 23, 2020 2:54 pm

Re: Copy sheet with current date automatically

Postby Zizi64 » Fri Apr 24, 2020 7:00 am

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; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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
Zizi64
Volunteer
 
Posts: 9429
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy sheet with current date automatically

Postby abinash1788 » Fri Apr 24, 2020 8:26 am

Thanks for replying but I am new to macros. I don't have any code .
Open office 4.1.7
Operating System - macOS
abinash1788
 
Posts: 8
Joined: Thu Apr 23, 2020 2:54 pm

Re: Copy sheet with current date automatically

Postby joesch » Fri Apr 24, 2020 9:05 am

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   Expand viewCollapse view
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
joesch
 
Posts: 38
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Re: Copy sheet with current date automatically

Postby robleyd » Fri Apr 24, 2020 9:12 am

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
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3393
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Copy sheet with current date automatically

Postby joesch » Fri Apr 24, 2020 10:01 am

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   Expand viewCollapse view
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   Expand viewCollapse view
newName = Join(Split(CStr(Now()),":"),"-")


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

Re: Copy sheet with current date automatically

Postby Zizi64 » Fri Apr 24, 2020 10:03 am

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; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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
Zizi64
Volunteer
 
Posts: 9429
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy sheet with current date automatically

Postby JohnSUN-Pensioner » Fri Apr 24, 2020 12:00 pm

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   Expand viewCollapse view
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
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 824
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Copy sheet with current date automatically

Postby joesch » Fri Apr 24, 2020 12:23 pm

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: 38
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Re: Copy sheet with current date automatically

Postby joesch » Fri Apr 24, 2020 12:30 pm

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
joesch
 
Posts: 38
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Re: Copy sheet with current date automatically

Postby JohnSUN-Pensioner » Fri Apr 24, 2020 12:51 pm

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   Expand viewCollapse view
   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
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 824
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Copy sheet with current date automatically

Postby abinash1788 » Tue Apr 28, 2020 9:14 am

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
abinash1788
 
Posts: 8
Joined: Thu Apr 23, 2020 2:54 pm

Re: Copy sheet with current date automatically

Postby Lupp » Tue Apr 28, 2020 10:45 am

-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 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2896
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Copy sheet with current date automatically

Postby Mr.Dandy » Tue Apr 28, 2020 10:46 am

Which macro did you talking about?
But now you can provide a document, right?
OpenOffice 4.1.7 - Windows 10
User avatar
Mr.Dandy
 
Posts: 314
Joined: Tue Dec 11, 2012 4:22 pm

Re: Copy sheet with current date automatically

Postby JohnSUN-Pensioner » Tue Apr 28, 2020 11:39 am

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
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 824
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests