Copy sheet with current date automatically
-
- Posts: 8
- Joined: Thu Apr 23, 2020 2:54 pm
Copy sheet with current date automatically
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
Operating System - macOS
Re: Copy sheet with current date automatically
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)
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.
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.
-
- Posts: 8
- Joined: Thu Apr 23, 2020 2:54 pm
Re: Copy sheet with current date automatically
Thanks for replying but I am new to macros. I don't have any code .
Open office 4.1.7
Operating System - macOS
Operating System - macOS
Re: Copy sheet with current date automatically
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:
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
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
Greetings,
joesch
OOo 3.3.0 (Windows) AOO 4.1.7 (Windows) AOO 4.x (Xubuntu)
my tips for AOO/LO (in the forum de.openoffice.info) · ProOO-Box - Information, instructions, supplementary materials about OpenOffice (german)
my tips for AOO/LO (in the forum de.openoffice.info) · ProOO-Box - Information, instructions, supplementary materials about OpenOffice (german)
Re: Copy sheet with current date automatically
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
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Re: Copy sheet with current date automatically
in this Case he can use: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.
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
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()),":"),"-")
joesch
OOo 3.3.0 (Windows) AOO 4.1.7 (Windows) AOO 4.x (Xubuntu)
my tips for AOO/LO (in the forum de.openoffice.info) · ProOO-Box - Information, instructions, supplementary materials about OpenOffice (german)
my tips for AOO/LO (in the forum de.openoffice.info) · ProOO-Box - Information, instructions, supplementary materials about OpenOffice (german)
Re: Copy sheet with current date automatically
Then you must study these things first.abinash1788 wrote:Thanks for replying but I am new to macros. I don't have any code .
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.
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.
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Copy sheet with current date automatically
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:
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
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
Re: Copy sheet with current date automatically
Yes, that's right. Sorry, I overlooked that the sheet called "Template" is to be copied.Your code does not copy the sheet with the name "Template" (as indicated in the question), but the current sheet.
Yes, I knew about it. I didn't warn about it because I saw no need.If you knew about this feature of your decision, why didn't you warn about it?
Because I was not sure if the questioner really only meant the date.If the question is about the current DATE, why do you use the Now () function to form the name of a new sheet?
I don't see any reason why I shouldn't do this, because it is, for the specific case, convenient to use.You should not use the combination Join(Split(...)) to format the string.
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.
You're absolutely(!) right. Therefore I myself recommend the macro recorder only in exceptional cases.Finally, smart people recommend not using a macro recorder to create programs.
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
OOo 3.3.0 (Windows) AOO 4.1.7 (Windows) AOO 4.x (Xubuntu)
my tips for AOO/LO (in the forum de.openoffice.info) · ProOO-Box - Information, instructions, supplementary materials about OpenOffice (german)
my tips for AOO/LO (in the forum de.openoffice.info) · ProOO-Box - Information, instructions, supplementary materials about OpenOffice (german)
Re: Copy sheet with current date automatically
Oh, I see... I probably should have tested before posting.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.
Your code works correctly even in the presence of objects. Sorry for my false statement.
Greetings,
joesch
OOo 3.3.0 (Windows) AOO 4.1.7 (Windows) AOO 4.x (Xubuntu)
my tips for AOO/LO (in the forum de.openoffice.info) · ProOO-Box - Information, instructions, supplementary materials about OpenOffice (german)
my tips for AOO/LO (in the forum de.openoffice.info) · ProOO-Box - Information, instructions, supplementary materials about OpenOffice (german)
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Copy sheet with current date automatically
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
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
-
- Posts: 8
- Joined: Thu Apr 23, 2020 2:54 pm
Re: Copy sheet with current date automatically
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
Operating System - macOS
Re: Copy sheet with current date automatically
-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'.
-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
---
Lupp from München
Re: Copy sheet with current date automatically
Which macro did you talking about?
But now you can provide a document, right?
But now you can provide a document, right?
OpenOffice 4.1.12 - Windows 10
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Copy sheet with current date automatically
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.
Choose Tools - Customize - Toolbars tab.
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
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