Page 1 of 1

[Solved] Copy the worksheet to another directory

Posted: Sun Dec 02, 2018 6:44 pm
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

Re: macro: Copy the worksheet to another directory

Posted: Sun Dec 02, 2018 7:30 pm
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?

Re: macro: Copy the worksheet to another directory

Posted: Sun Dec 02, 2018 7:51 pm
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).

Re: Copy the worksheet to another directory

Posted: Sun Dec 02, 2018 10:31 pm
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

Re: Copy the worksheet to another directory

Posted: Sun Dec 02, 2018 11:19 pm
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.

Re: Copy the worksheet to another directory

Posted: Mon Dec 03, 2018 11:45 am
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

Re: Copy the worksheet to another directory

Posted: Mon Dec 03, 2018 3:34 pm
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.

Re: Copy the worksheet to another directory

Posted: Mon Dec 03, 2018 3:51 pm
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

Re: Copy the worksheet to another directory

Posted: Mon Dec 03, 2018 6:48 pm
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.)

Re: Copy the worksheet to another directory

Posted: Mon Dec 03, 2018 8:04 pm
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

Re: Copy the worksheet to another directory

Posted: Mon Dec 03, 2018 8:30 pm
by Villeroy

Re: Copy the worksheet to another directory

Posted: Tue Dec 04, 2018 12:25 pm
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

Re: Copy the worksheet to another directory

Posted: Tue Dec 04, 2018 12:33 pm
by Villeroy

Re: Copy the worksheet to another directory

Posted: Tue Dec 04, 2018 8:52 pm
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.

Re: Copy the worksheet to another directory

Posted: Tue Dec 04, 2018 10:34 pm
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".

Re: Copy the worksheet to another directory

Posted: Wed Dec 05, 2018 2:02 pm
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 .

Re: Copy the worksheet to another directory

Posted: Wed Dec 05, 2018 2:09 pm
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.

Re: Copy the worksheet to another directory

Posted: Wed Dec 05, 2018 2:15 pm
by Math
on the ERROR screen is written the following :

BASIC Execution Error

Property or method not found SupportsService


hugs

Re: Copy the worksheet to another directory

Posted: Wed Dec 05, 2018 2:26 pm
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

Re: Copy the worksheet to another directory

Posted: Wed Dec 05, 2018 2:28 pm
by robleyd
Have you checked the documentation to see if there is such a thing as SupportsService, or whether there might be a typo?

Re: Copy the worksheet to another directory

Posted: Wed Dec 05, 2018 3:35 pm
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? ...)

Re: Copy the worksheet to another directory

Posted: Wed Dec 05, 2018 6:01 pm
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 .

Re: Copy the worksheet to another directory

Posted: Wed Dec 05, 2018 6:07 pm
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!!!

Re: Copy the worksheet to another directory

Posted: Thu Dec 06, 2018 1:11 am
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 .

Re: Copy the worksheet to another directory

Posted: Thu Dec 06, 2018 8:34 am
by mikele
Hello,
your example please. Lupp's file works - yours not ... so why editing a working macro???

Re: Copy the worksheet to another directory

Posted: Thu Dec 06, 2018 12:01 pm
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

Re: Copy the worksheet to another directory

Posted: Thu Dec 06, 2018 12:41 pm
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 .

Re: Copy the worksheet to another directory

Posted: Thu Dec 06, 2018 1:28 pm
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

Re: Copy the worksheet to another directory

Posted: Thu Dec 06, 2018 10:35 pm
by Villeroy
*PLONK*

Re: Copy the worksheet to another directory

Posted: Fri Dec 07, 2018 12:31 pm
by Math
[SOLVED]

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

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



hugs friends .