[Solved] Copy the worksheet to another directory

[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

[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?

[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 ... me_Library).

[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

[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.

[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

[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.

[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

[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.)

[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

[Villeroy]

viewtopic.php?f=20&t=93252&p=444223#p444223

[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

[Villeroy]

http://www.openoffice.org/api/docs/comm ... kMode.html

[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.

[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".

[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 .

[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.

[Math]

on the ERROR screen is written the following :

BASIC Execution Error

Property or method not found SupportsService


hugs

[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

[robleyd]

Have you checked the documentation to see if there is such a thing as SupportsService, or whether there might be a typo?

[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   Expand viewCollapse view

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? ...)

[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 .

[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!!!

[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 .

[mikele]

Hello,
your example please. Lupp's file works - yours not ... so why editing a working macro???

[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

[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 .

[Zizi64]

Code: Select all   Expand viewCollapse view

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   Expand viewCollapse view

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

[Villeroy]

*PLONK*

[Math]

[SOLVED]

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

thanks so much for your help .



hugs friends .