[Solved] Copy the worksheet to another directory
[Solved] Copy the worksheet to another directory
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
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 452 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
Re: macro: Copy the worksheet to another directory
Hi,
Then, first of all, you should use a .ods file. Do you really want to use a .xls file with openoffice-basic?I want to turn the macro vba into a Macro in LibreOffice Calc (.ods) .
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
Re: macro: Copy the worksheet to another directory
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).
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.
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.
Re: Copy the worksheet to another directory
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
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 492 times
LibreOffice 5.4.4.2 on Windows 7
Re: Copy the worksheet to another directory
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.
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.
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.
Re: Copy the worksheet to another directory
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
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
Re: Copy the worksheet to another directory
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.I have not had time to test the code snippets of the links linked above
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.
Re: Copy the worksheet to another directory
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.I need to copy only the values .
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.
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.
Re: Copy the worksheet to another directory
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.)
See attached demo. (Saving to csv or whatever is not included there.)
- Attachments
-
- aoo96097CopyDocNoFormulae_1.ods
- (14.85 KiB) Downloaded 477 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
---
Lupp from München
Re: Copy the worksheet to another directory
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
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
Re: Copy the worksheet to another directory
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Copy the worksheet to another directory
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
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
Re: Copy the worksheet to another directory
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Copy the worksheet to another directory
Have you tried to write that macro? Please show us your "StarBasic+API" type macro code.I need the macro Do not copy the formulas, only the values, and if possible without the macro buttons .
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.
Re: Copy the worksheet to another directory
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".
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 469 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
---
Lupp from München
Re: Copy the worksheet to another directory
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 .
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 .
LibreOffice 5.4.4.2 on Windows 7
Re: Copy the worksheet to another directory
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.
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.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Copy the worksheet to another directory
on the ERROR screen is written the following :
BASIC Execution Error
Property or method not found SupportsService
hugs
BASIC Execution Error
Property or method not found SupportsService
hugs
LibreOffice 5.4.4.2 on Windows 7
Re: Copy the worksheet to another directory
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
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.
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.
Re: Copy the worksheet to another directory
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.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Copy the worksheet to another directory
@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
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? ...)
Code: Select all
myObject.SupportsService("com.sun.star.sheet.SpreadsheetDocument")
@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
---
Lupp from München
Re: Copy the worksheet to another directory
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 .
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
Re: Copy the worksheet to another directory
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.
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.
Re: Copy the worksheet to another directory
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 .
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
Re: Copy the worksheet to another directory
Hello,
your example please. Lupp's file works - yours not ... so why editing a working macro???
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
Re: Copy the worksheet to another directory
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
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
Re: Copy the worksheet to another directory
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 .
also follows an image of the new ERROR when I executed the macro .
hugs .
- Attachments
-
- CopyDocNoFormulae_2.ods
- (18.36 KiB) Downloaded 454 times
LibreOffice 5.4.4.2 on Windows 7
Re: Copy the worksheet to another directory
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.
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.
Re: Copy the worksheet to another directory
*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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Copy the worksheet to another directory
[SOLVED]
sr. Lupp , sr. Zizi64 , sr. Villeroy , sr. robleyd
thanks so much for your help .
hugs friends .
sr. Lupp , sr. Zizi64 , sr. Villeroy , sr. robleyd
thanks so much for your help .
hugs friends .
LibreOffice 5.4.4.2 on Windows 7