[Solved] Copy the worksheet to another directory

Creating a macro - Writing a Script - Using the API

[Solved] Copy the worksheet to another directory

Postby Math » Sun Dec 02, 2018 6:44 pm

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 10 times
Last edited by Math on Fri Dec 07, 2018 12:32 pm, edited 1 time in total.
OpenOffice 4.1.5 on Windows 7
Math
 
Posts: 68
Joined: Mon Oct 29, 2018 6:32 pm

Re: macro: Copy the worksheet to another directory

Postby mikele » Sun Dec 02, 2018 7:30 pm

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 on LinuxMint/WinXP/Win7
mikele
 
Posts: 7
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: macro: Copy the worksheet to another directory

Postby Zizi64 » Sun Dec 02, 2018 7:51 pm

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).
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.1 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.2 and AOO4.1.5
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: 7465
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy the worksheet to another directory

Postby Math » Sun Dec 02, 2018 10:31 pm

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 14 times
OpenOffice 4.1.5 on Windows 7
Math
 
Posts: 68
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Postby Zizi64 » Sun Dec 02, 2018 11:19 pm

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; LO4.4.7, LO6.1.1 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.2 and AOO4.1.5
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: 7465
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy the worksheet to another directory

Postby Math » Mon Dec 03, 2018 11:45 am

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
OpenOffice 4.1.5 on Windows 7
Math
 
Posts: 68
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Postby Zizi64 » Mon Dec 03, 2018 3:34 pm

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; LO4.4.7, LO6.1.1 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.2 and AOO4.1.5
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: 7465
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy the worksheet to another directory

Postby Zizi64 » Mon Dec 03, 2018 3:51 pm

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; LO4.4.7, LO6.1.1 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.2 and AOO4.1.5
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: 7465
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy the worksheet to another directory

Postby Lupp » Mon Dec 03, 2018 6:48 pm

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 9 times
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2178
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Copy the worksheet to another directory

Postby Math » Mon Dec 03, 2018 8:04 pm

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
OpenOffice 4.1.5 on Windows 7
Math
 
Posts: 68
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Postby Villeroy » Mon Dec 03, 2018 8:30 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25965
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy the worksheet to another directory

Postby Math » Tue Dec 04, 2018 12:25 pm

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
OpenOffice 4.1.5 on Windows 7
Math
 
Posts: 68
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Postby Villeroy » Tue Dec 04, 2018 12:33 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25965
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy the worksheet to another directory

Postby Zizi64 » Tue Dec 04, 2018 8:52 pm

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; LO4.4.7, LO6.1.1 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.2 and AOO4.1.5
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: 7465
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy the worksheet to another directory

Postby Lupp » Tue Dec 04, 2018 10:34 pm

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 16 times
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2178
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Copy the worksheet to another directory

Postby Math » Wed Dec 05, 2018 2:02 pm

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
OpenOffice 4.1.5 on Windows 7
Math
 
Posts: 68
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Postby robleyd » Wed Dec 05, 2018 2:09 pm

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
Apache OpenOffice 4.1.6 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2264
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Copy the worksheet to another directory

Postby Math » Wed Dec 05, 2018 2:15 pm

on the ERROR screen is written the following :

BASIC Execution Error

Property or method not found SupportsService


hugs
OpenOffice 4.1.5 on Windows 7
Math
 
Posts: 68
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Postby Zizi64 » Wed Dec 05, 2018 2:26 pm

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; LO4.4.7, LO6.1.1 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.2 and AOO4.1.5
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: 7465
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy the worksheet to another directory

Postby robleyd » Wed Dec 05, 2018 2:28 pm

Have you checked the documentation to see if there is such a thing as SupportsService, or whether there might be a typo?
Cheers
David
Apache OpenOffice 4.1.6 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2264
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Copy the worksheet to another directory

Postby Lupp » Wed Dec 05, 2018 3:35 pm

@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? ...)
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2178
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Copy the worksheet to another directory

Postby Math » Wed Dec 05, 2018 6:01 pm

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 .
OpenOffice 4.1.5 on Windows 7
Math
 
Posts: 68
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Postby Zizi64 » Wed Dec 05, 2018 6:07 pm

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; LO4.4.7, LO6.1.1 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.2 and AOO4.1.5
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: 7465
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy the worksheet to another directory

Postby Math » Thu Dec 06, 2018 1:11 am

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 .
OpenOffice 4.1.5 on Windows 7
Math
 
Posts: 68
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Postby mikele » Thu Dec 06, 2018 8:34 am

Hello,
your example please. Lupp's file works - yours not ... so why editing a working macro???
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 7
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Copy the worksheet to another directory

Postby Math » Thu Dec 06, 2018 12:01 pm

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
OpenOffice 4.1.5 on Windows 7
Math
 
Posts: 68
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Postby Math » Thu Dec 06, 2018 12:41 pm

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 8 times
OpenOffice 4.1.5 on Windows 7
Math
 
Posts: 68
Joined: Mon Oct 29, 2018 6:32 pm

Re: Copy the worksheet to another directory

Postby Zizi64 » Thu Dec 06, 2018 1:28 pm

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
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.1 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.2 and AOO4.1.5
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: 7465
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy the worksheet to another directory

Postby Villeroy » Thu Dec 06, 2018 10:35 pm

*PLONK*
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25965
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy the worksheet to another directory

Postby Math » Fri Dec 07, 2018 12:31 pm

[SOLVED]

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

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



hugs friends .
OpenOffice 4.1.5 on Windows 7
Math
 
Posts: 68
Joined: Mon Oct 29, 2018 6:32 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests