[Solved] Copy the worksheet to another directory

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

[Solved] Copy the worksheet to another directory

Post by Math »

greetings ,

          I have an Excel vba macro that saves a copy of the chosen worksheet to another directory .

          I want to turn the macro vba into a Macro in LibreOffice Calc (.ods) .

          the new "Calc" macro should do the same operation .

comments

         1) the name of the new worksheet is in cell "G1" of the current file .

         2) Attach the current file.xls with Macro vba attached to the "Copy" button .

         source of vba macro: Sub Salvar() > https://www.clubedohardware.com.br/foru ... mo-no-vba/


hugs friends
Attachments
copy_to_another_directory.xls
(35 KiB) Downloaded 208 times
Last edited by Math on Fri Dec 07, 2018 12:32 pm, edited 1 time in total.
LibreOffice 5.4.4.2 on Windows 7
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: macro: Copy the worksheet to another directory

Post by mikele »

Hi,
I want to turn the macro vba into a Macro in LibreOffice Calc (.ods) .
Then, first of all, you should use a .ods file. Do you really want to use a .xls file with openoffice-basic?
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: macro: Copy the worksheet to another directory

Post by Zizi64 »

Do not use the obsolete .xls file format. You will lost some formatting properties at the conversion, and the embedded macros will not stored/work properly. Always use the native, international Standard ODF file formats.


Try the "StoreTo...()" or the "StoreAs...()" API function:
https://wiki.openoffice.org/wiki/Saving_a_document


or the "FileCopy(Source, Destination)" calling:
https://wiki.openoffice.org/wiki/Docume ... e_Library).
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.
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Post by Math »

Hi mikele ,

I want to use a LibreOffice file (.ods) .

the LibreOffice file (.ods) for the tests is attached .

I think that during the conversion of the file to .ods a solution has already occurred .


hugs
Attachments
copy_to_another_directory.ods
(20.58 KiB) Downloaded 233 times
LibreOffice 5.4.4.2 on Windows 7
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy the worksheet to another directory

Post by Zizi64 »

Have you tried the sample code snippets of the sites linked above?

You must rewrite your macro based on the AOO/LO API functions or the StarBasic Copy function.
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.
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Post by Math »

greetings Zizi64 ,

thanks for the suggestion, I have not had time to test the code snippets of the links linked above .

the attached worksheet (.ods) already copies to the correct directory, but the problem is that you are also copying formulas .
I did a test with formulas and is copying formulas too .

I need to copy only the values .


hugs
LibreOffice 5.4.4.2 on Windows 7
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy the worksheet to another directory

Post by Zizi64 »

I have not had time to test the code snippets of the links linked above
Then how you will use them? One of these methods is necessary, if you want to make a copy of a specific document by your macro.
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
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy the worksheet to another directory

Post by Zizi64 »

I need to copy only the values .
You need get the used cell range in the source file and then must get the numeric or the string content (data) of the cells. Then you need put the strings or the numbers into the cells of the target document instead of the formulas.

You have found some solution for this task in an another topic marked 'as solved' by you - but you not shared the solution:
viewtopic.php?f=20&t=95906&p=458018#p458018
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
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Copy the worksheet to another directory

Post by Lupp »

Using the dispatcher slots .uno:Move, .uno:Copy, .uno:Paste, and .uno:InsertContents should be very efficient.
See attached demo. (Saving to csv or whatever is not included there.)
Attachments
aoo96097CopyDocNoFormulae_1.ods
(14.85 KiB) Downloaded 220 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Post by Math »

greetings sr. Lupp ,

very good your Basic code for demonstration, your macro is doing correct copy operation and Generate a new worksheet without the formulas inside the same file .

Note :

need to copy the spreadsheet and then generate a new file without the formulas inside a new directory, the new directory will be specified within the macro itself, the directory will be "C: \ Users \ User \ Documents \ plan \ new location \"

and the name of the new file generated without the formulas will be the same as the name found in the "G1" cell of the copied worksheet .


hugs
LibreOffice 5.4.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy the worksheet to another directory

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Post by Math »

greetings ,

         I did tests with the Basic code of the Link indicated by mr. Villeroy, and the following happened :

         the macro executed a correct copy, generated a new correct file, but also copied together the formulas .

         I tested the Link macro in a file with formulas, and also copied the formulas .

         I need the macro Do not copy the formulas, only the values, and if possible without the macro buttons .

         the macro in the [copy_to_another_directory.ods] file attached in this topic, is also copying the formulas .


hugs
LibreOffice 5.4.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy the worksheet to another directory

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy the worksheet to another directory

Post by Zizi64 »

I need the macro Do not copy the formulas, only the values, and if possible without the macro buttons .
Have you tried to write that macro? Please show us your "StarBasic+API" type macro 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.
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Copy the worksheet to another directory

Post by Lupp »

Adapt the helper functions newPath and newName to your needs. The Sub to call is storeCastratedAs.
The module should better be available from the local Standard library. If provided from the doc's script container it will also be contained in the "pure-data-file".
Attachments
aoo96097CopyDocNoFormulae_2.ods
(18.21 KiB) Downloaded 220 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Post by Math »

greetings sr. Lupp ,

            An ERROR occurred to execute the line : If NOT pDoc.SupportsService("com.sun.star.sheet.SpreadsheetDocument")

          follows an image of the screen with the ERROR .


hugs friend .
Attachments
Error 1.png
LibreOffice 5.4.4.2 on Windows 7
User avatar
robleyd
Moderator
Posts: 5036
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Copy the worksheet to another directory

Post by robleyd »

As this is a English speaking forum, it would help if you translate the error so it can be understood by English speakers.

At some stage you are going to have to learn how to interpret and correct syntax errors.
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
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Post by Math »

on the ERROR screen is written the following :

BASIC Execution Error

Property or method not found SupportsService


hugs
LibreOffice 5.4.4.2 on Windows 7
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy the worksheet to another directory

Post by Zizi64 »

What is the object 'pDoc' actually? Where you launched/called the 'Sub castrateAllSheets(pDoc As Object, pFlags As String)' macro routine from? What parameters (object and flag string) have you passed to the routine?

Use an object inspection tool to examine the programming objects: the existing properties, methods and services.
Popular object inspection tools:
MRI
XrayTool
Last edited by Zizi64 on Wed Dec 05, 2018 2:33 pm, edited 1 time in total.
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
robleyd
Moderator
Posts: 5036
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Copy the worksheet to another directory

Post by robleyd »

Have you checked the documentation to see if there is such a thing as SupportsService, or whether there might be a typo?
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
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Copy the worksheet to another directory

Post by Lupp »

@robleyd: Yes. The API gives access to interfaces that support services. If you have an object myObject representing a doument model (loaded from an Url e.g.) you may want to ask it if it is a SpreadsheetDocument. The respective condition (Boolean result) is

Code: Select all

myObject.SupportsService("com.sun.star.sheet.SpreadsheetDocument") 
If you are doubting because official help writes "supportsService" with the lower casec "s" at the beginning: This method name lies in a namespace being NOT case sensitive. Only the names given as quoted constants and the string used in the AS-New-declaration are treated case sensitive.

@Math: I took the time to write the complete code and to test it with AOO V 4.1.5 and with two recent versions of LibO as well. I just repeated the test with the above attached file after downloading it again. It worked as expected - and intended. I made also sure that the code didn't call any piece of code only available from my local macros...

This must be enough now.

(Did the error occur when testing with the downloaded file OR had you moved the code to another document already? ...to your local Basic library? Did you call the correct Sub storeCastratedAs? Did you execute it stepwise? Did you watch the critical object variable just before the error occurred? Did it represent the document you wanted to work on? If not: did you check the line where it got its assignment? ...)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Post by Math »

sr. Lupp ,

maybe I'm wrongly assigning the directory path and the new filename inside your BASIC code .

you can put the following information in your code and then re-attach the file .

follow the informations :

newPath : the new directory path where the copy of the worksheet (new file) will be written is ... C: \ Users \ Padrao \ Documents \ test A \ test B \ test C \

newName : The name of the new file is ... [ Test.ods ]


hugs friend .
LibreOffice 5.4.4.2 on Windows 7
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy the worksheet to another directory

Post by Zizi64 »

you can put the following information in your code and then re-attach the file .

follow the informations :

newPath : the new directory path where the copy of the worksheet (new file) will be written is ... C: \ Users \ Padrao \ Documents \ test A \ test B \ test C \

Math, please upload YOUR code and/or sample file here!!!
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.
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Post by Math »

greetings ,

we can use the example file of sr. Lupp to apply the newPath and newName that I put in the previous message .

in the attached file already has a very good BASIC code .


hugs .
LibreOffice 5.4.4.2 on Windows 7
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Copy the worksheet to another directory

Post by mikele »

Hello,
your example please. Lupp's file works - yours not ... so why editing a working macro???
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Post by Math »

Hello ,

macro of sr. Lupp in the attached file is very good, but something is going wrong for me, I believe I'm putting newPath and newName in the wrong place inside the macro .

I just want to check if I put the newPath and newName in the right place inside the macro sr. Lupp in the attached file .

so I asked a favor for sr. Lupp put in the code of the file attached my newPath and the newName that I informed in the previous message, because thus I can see if I am doing correct the adaptation .

maybe I have not put my newPath and newName in the correct place .

information : newPath > C:\Users\Padrao\Documents\teste A\test B\test C\
newName > Test.ods


hugs
LibreOffice 5.4.4.2 on Windows 7
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Post by Math »

follows the file of mr. Lupp with my newPath and newName adapted within the macro .

also follows an image of the new ERROR when I executed the macro .


hugs .
Attachments
image.png
CopyDocNoFormulae_2.ods
(18.36 KiB) Downloaded 182 times
LibreOffice 5.4.4.2 on Windows 7
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy the worksheet to another directory

Post by Zizi64 »

Code: Select all

newPath = Left(s, Len(s) - Len(h(u))) & pSubName & "C:\Users\Padrao\Documents\test A\test B\test C"

I suppose that you know nothing about the string concatenation statement, about the paths and URL-s, and about the programming/syntax, etc... (Why you force the usage of the macros without intensive learning?)
Use the Print command in your macro code to examine what you created by your program line.

Code: Select all

newPath = Left(s, Len(s) - Len(h(u))) & pSubName & "C:\Users\Padrao\Documents\test A\test B\test C"
print newPath
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
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy the worksheet to another directory

Post by Villeroy »

*PLONK*
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Post by Math »

[SOLVED]

sr. Lupp , sr. Zizi64 , sr. Villeroy , sr. robleyd

thanks so much for your help . :super: :super: :super:



hugs friends .
LibreOffice 5.4.4.2 on Windows 7
Post Reply