[Solved] Remove Macros from spreadsheets by Vb.net

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
VBandOoffice
Posts: 33
Joined: Tue Jul 31, 2018 10:11 am

[Solved] Remove Macros from spreadsheets by Vb.net

Post by VBandOoffice »

Hi,
I've visited this forum several times and up to now, I've found solutions for my problems. This time not - This is my problem:

I've thousands of spreadsheets. All of them containing a wrong macro. I want to remove this macros with an VB Program. I know how to run macros by VB.net, but I found no solution how to remove this macros. All macros are located at same place, same name. (This macro is an function)

Thanks in advance!

In meantime I found some information at the API Dokumentation:

:: com :: sun :: star :: script :: browse ::
unpublished service BrowseNode

Usage Restrictions
not published
Description
This service provides access to the Macros and Macro containers via the XBrowseNode interface. XInvocation is an optional interface that is used to execute macros, or to create/delete/rename macros or macro containers.


Does someone know how to handle with this information?

Regards, VBandOoffice
Last edited by VBandOoffice on Tue Aug 07, 2018 12:57 pm, edited 1 time in total.
LibreOffice 7.5, Windows 10, VB.net 2019, C#
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Remove Macros from spreadsheets by Vb.net

Post by Zizi64 »

Can you upload one ot two piece of those spreadsheet files 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.
VBandOoffice
Posts: 33
Joined: Tue Jul 31, 2018 10:11 am

Re: Remove Macros from spreadsheets by Vb.net

Post by VBandOoffice »

Hello Zizi64,
thanks for trying to support me. - I've already used some of your hints successfully.
The originals are confidential, I cannot upload them. I will prepare are simple similar one and upload this one.
Best regards, VBandOoffice
LibreOffice 7.5, Windows 10, VB.net 2019, C#
VBandOoffice
Posts: 33
Joined: Tue Jul 31, 2018 10:11 am

Re: Remove Macros from spreadsheets by Vb.net

Post by VBandOoffice »

Hello Zizi64,

I've attached a simple sample. I want to remove/delete one of the macros: KuZ_Stb because it is containing an error.
For future I will use global macros, belonging to application not to document. If I have a global macro with same name, document macro will be prior to others.

Have a nice Weekend,
VBandOoffice
Attachments
SampleTryToRemove_KuZ_Stb.ods
I've changed only the name of the file, maybe there's a missunderstanding because of the last 2
(15.42 KiB) Downloaded 168 times
LibreOffice 7.5, Windows 10, VB.net 2019, C#
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Remove Macros from spreadsheets by Vb.net

Post by Zizi64 »

Here is a macro example - written by Bernard Marcelly - how to manipulate the macro libraries and modules by a macro. The sample macro shows, how to import, export the Standard library of the MyMacros - how delete, copy modules into/from a document. The macro uses a specific library name in the document "Std000" for storaging the macros exported from the My macros.

https://forum.openoffice.org/fr/forum/v ... =15&t=7879
Some informations in English:
viewtopic.php?t=37459

I think you must (and you can) modify the macro for your task: You need store the modified macro in the MyMacros, and it will work on the documents containing the unwanted macro 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.
VBandOoffice
Posts: 33
Joined: Tue Jul 31, 2018 10:11 am

Re: Remove Macros from spreadsheets by Vb.net

Post by VBandOoffice »

Hello Zizi64,
thanks again for your work. I'll check this out and will post the result, when it's working fine.
Best regards,
VBandOoffice
LibreOffice 7.5, Windows 10, VB.net 2019, C#
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remove Macros from spreadsheets by Vb.net

Post by Villeroy »

Acessing UNO from VB: http://www.kalitech.fr/clients/doc/VB_APIOOo_en.html
This snippet derived from the very first example snippet might work:

Code: Select all

Sub firstOOoProc()

Dim oSM                   'Root object for accessing OpenOffice from VB
Dim oDesk, oDoc As Object 'First objects from the API
Dim arg()                 'Ignore it for the moment !

'Instanciate OOo : this line is mandatory with VB for OOo API
  Set oSM = CreateObject("com.sun.star.ServiceManager")
'Create the first and most important service
  Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")

'Open an existing doc (pay attention to the syntax for first argument)
  Set oDoc = oDesk.loadComponentFromURL("file:///c:/dev/ooo/test.doc", "_blank", 0, arg())
REM #############################
Dim oLibs, aNames()As String, n As String
oLibs = oDoc.getBasicLibraries()
aNames() = oLibs.getElementNames()
For Each n in aNames()
  oLibs.removeLibrary(n)
Next
'Save the doc
REM  Call oDoc.storeToURL("file:///c:/dev/ooo/test2.sxw", arg())
  oDoc.store()
REM #############################
'Close the doc
  oDoc.Close (True)
  Set oDoc = Nothing
End Sub
The part between the hashes works fine with StarBasic.
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
VBandOoffice
Posts: 33
Joined: Tue Jul 31, 2018 10:11 am

Re: Remove Macros from spreadsheets by Vb.net

Post by VBandOoffice »

Hello Villeroy,
thank you very much for this snippet.
Best regards from sunny franconia,
VBandOoffice
LibreOffice 7.5, Windows 10, VB.net 2019, C#
VBandOoffice
Posts: 33
Joined: Tue Jul 31, 2018 10:11 am

Re: Remove Macros from spreadsheets by Vb.net

Post by VBandOoffice »

@Villeroy: I always get the Error message: BASIC-Laufzeitfehler. Eigenschaft oder Methode nicht gefunden: getBasicLibraries. (LibreOffice 5.4.7.2)

This is independent whether I use Star Basic or VB.net. Even with Xray tool, I cannot find something similar. Are there anyone able to enlighten me?

Best regards,
VBandOoffice
LibreOffice 7.5, Windows 10, VB.net 2019, C#
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Remove Macros from spreadsheets by Vb.net

Post by MrProgrammer »

Hi, and welcome to the forum.
VBandOoffice wrote:I've thousands of spreadsheets. All of them containing a wrong macro. I want to remove this macros with an VB Program.
This task will be much easier outside of the OpenOffice programming environment. If you had Linux or Mac instead of lame Windoze you could remove the macros with a simple script, like this:
cd {directory containing your thousands of spreadsheets}
mkdir -p Basic/Standard
bsf='Basic/Standard/Funktionen.xml'
bad='/Function KuZ_Stb/,/End Function/d'
for qqf in *.o[dt]s; do :
   unzip -p "$qqf" $bsf | sed "$bad" >$bsf
   zip -f "$qqf" $bsf
done
rm -r Basic

This unpacks each file, removes the nasty function using the stream editor (sed), and repacks it. Perhaps you can install a Linux subsystem on Windoze for this task. There are Windoze tools which can accomplish the same process, but I no longer do any Windoze programming (unless someone is paying me handsomely as compensation for that unpleasantness). A prudent programmer would ensure that appropriate backups have been made before beginning.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Last edited by MrProgrammer on Tue Aug 07, 2018 6:03 pm, edited 1 time in total.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
VBandOoffice
Posts: 33
Joined: Tue Jul 31, 2018 10:11 am

Re: Remove Macros from spreadsheets by Vb.net

Post by VBandOoffice »

Hello MrProgrammer,
interesting idea, but am I right, this will work only for macros located in application, not in document?
Or is this Basic/Standard/Function.xml part of the .ods file? - and I only have to get access to this part?
I'm not familiar with Linux, sorry. (Because I'm getting a compensation to do it with Windows).

By the way, work is nearly done, now. I'm able to get access to the Macromodules and I think, the rest of work will be simple.
It's like you suggested, only to remove the unwanted part of the script. The solution is based on snippet from Villeroy and XRAY tool.

Best regards,
VBandOoffice
LibreOffice 7.5, Windows 10, VB.net 2019, C#
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Remove Macros from spreadsheets by Vb.net

Post by Zizi64 »

Or is this Basic/Standard/Function.xml part of the .ods file?
Yes. Renamed a copy of your sample file to .zip extension, and unzipped it with my file manager software (Unreal Commander):
Unzipped.png
Last edited by Zizi64 on Tue Aug 07, 2018 8:46 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
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Remove Macros from spreadsheets by Vb.net

Post by Lupp »

VBandOoffice wrote:I always get the Error message: BASIC-Laufzeitfehler. Eigenschaft oder Methode nicht gefunden: getBasicLibraries.
(BASIC runtime error. Property or method not found: getBasicLibraries.)

There isn't (is no longer?) a get method for any document's .BasicLibraries collection. Simply access it (in Villeroy's code) as oDoc.BasicLibraries either directly or assigned to a Variant variable.
Last edited by Lupp on Tue Aug 07, 2018 10:19 am, edited 1 time in total.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remove Macros from spreadsheets by Vb.net

Post by Villeroy »

Indeed, BasicLibraries is a property. It is not a function.
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
VBandOoffice
Posts: 33
Joined: Tue Jul 31, 2018 10:11 am

Re: Remove Macros from spreadsheets by Vb.net

Post by VBandOoffice »

Hi all,

maybe this is only a very simple additional problem, but I don't know how to do:
Now I'm able to remove a macro module by another macro or by VB.net. Both work the same.

After I did, macros seems to be gone. But when I close the file and reopen it, all of them are back again???

I've attached the macro how I did it.
Best regards,
VBandOoffice
Attachments
removeMacro.txt
(720 Bytes) Downloaded 195 times
LibreOffice 7.5, Windows 10, VB.net 2019, C#
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remove Macros from spreadsheets by Vb.net

Post by Villeroy »

Do it like MrProgrammer does. I think, any language, including VB, is able to remove files from a zip archive.
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
VBandOoffice
Posts: 33
Joined: Tue Jul 31, 2018 10:11 am

Re: Remove Macros from spreadsheets by Vb.net

Post by VBandOoffice »

My problem is solved now, I checked the solution from MrProgrammer and it works fine.

A little more hint for others with similar problems: If you need to remove a macro module completly, you have to remove the
File modulname.xml from Foldername/Basic/Standard/ and additional the macro module name inside of script-lb.xml.
Otherwise you will have an empty macro module left.

Many Thanks to all, who supported me.
VBandOoffice
LibreOffice 7.5, Windows 10, VB.net 2019, C#
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Remove Macros from spreadsheets by Vb.net

Post by Lupp »

Going back to working on the document instead of manipulating the packed file (imo preferrable; will also work on "flat" without changes):

It seems the .LibraryContainer obtained by the respective Get method does not grant full access but mainly gives information. Manipulating it does only concern a copy returned by the Get method. Since I couldn't find a Set method, the given task cannot be accomplished this way.

The container .BasicLibraries gives full access. Th code given below worked for me in LibreOffice. I also tested it under AOO 4.1.5 and it also worked there if called via Macros>Run, but there was an issue when it was processed step by step from the debugger.

Code: Select all

Sub removeBasicModule(Optional pDoc As Object, Optional pBasLibN As String, Optional pModuleN As String)
If IsMissing(pDoc)      Then pDoc      = ThisComponent
If IsMissing(pBasLibN)  Then pBasLibN  = "Standard"             REM Just for my example!
If IsMissing(pModuleN)  Then pModuleN  = "Module2"              REM Just for my example!
Dim theBasLibs As Object, theBasLib As Object
theBasLibs = pDoc.BasicLibraries
If theBasLibs.hasByName(pBasLibN) Then
     theBasLib = theBasLibs.getbyName("Standard")
     If theBasLib.HasByName(pModuleN) Then
         theBasLib.RemoveByName(pModuleN)
         pDoc.StoreAsUrl(pDoc.URL, Array())
     End If
End If
End Sub
Edit: Append demo. Variant ..._0.ods before, ..._1.ods after running the macro.
Attachments
aoo_Demo_ManipulateBasicLibraries_0.ods
(10.48 KiB) Downloaded 144 times
aoo_Demo_ManipulateBasicLibraries_1.ods
(10.33 KiB) Downloaded 138 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
VBandOoffice
Posts: 33
Joined: Tue Jul 31, 2018 10:11 am

Re: [Solved] Remove Macros from spreadsheets by Vb.net

Post by VBandOoffice »

Thank you, Lupp

it really works!

Now I'm able to remove this MacroModule when the document is opened next time by an Application-StartUp-Macro.

Best regards,
VBandOoffice
LibreOffice 7.5, Windows 10, VB.net 2019, C#
Post Reply