[Solved] Transfer VBA word macros to OpenOffice macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Marie-Helene
Posts: 6
Joined: Fri Feb 28, 2020 7:21 pm

[Solved] Transfer VBA word macros to OpenOffice macro

Post by Marie-Helene »

Hi,
I am a beginner. I use daily VBA macros (especially one) in Microsoft Word, and I am not able to rewrite it in open office.
I made some changes to adapt it, try to convert it online, nothing do.
Here is the original VBA macro.

Code: Select all

Sub CopySelectedToNewDoc()
'Sub CopySelectedToNewDoc()
Selection.Copyu
Documents.Add Template:="Normal", NewTemplate:=False, _
DocumentType:=0
Selection.Paste

End Sub
Sub EnleveDoubleEspace()
'' TrimGlobal Macro
    Do With Selection.Find
            .ClearFormatting
            .Text = "  "
            .MatchWildcards = False
            .Replacement.ClearFormatting
            .Replacement.Text = " "
            .Execute Replace:=wdReplaceAll
        End With
    Loop While Selection.Find.Found = True

End Sub

Sub AutoOpen()
ActiveWindow.View.Type = 3 'print layout
ActiveWindow.View.Zoom.Percentage = 60

End Sub
It is especially the first one (Copy selected text to a new document) that I really need.
Of course I changed "normal" to standard... No result.
I tried also to "record" a new macro which don't work neither.
If ever you have an idea or solution, I would really really appreciate.
Thank you.
Last edited by MrProgrammer on Fri Mar 27, 2020 8:18 pm, edited 2 times in total.
Reason: Edited code tags!, floris v, moderator; Tagged ✓ [Solved]
Marie-Helene
Openoffice 4.1.7
Window 10
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Transfer VBA word macros to OpenOffice macro

Post by MrProgrammer »

Hi, and welcome to the forum.
Marie-Helene wrote:I use daily VBA macros (especially one) in Microsoft Word, and I am not able to rewrite it in open office.
The macro language for OpenOffice is completely different than the language for Microsnot Word. You will need to rewrite your macros from the beginning. There is no simple translation, and writing OpenOffice macros is difficult. If you have a lot of time invested in the MS Word way of operation, you may want to consider staying with it.
Marie-Helene wrote:It is especially the first one (Copy selected text to a new document) that I really need.
Disclaimer: I do not use Word, and I am not familiar with the macro language, so I have to guess at what it is doing. By inspection, it seems to be copying selected text to a new document. In that case, it's just three shortcuts so I would dispense with the macro:
• Ctrl+C   Copy selected text to clipboard
• Ctrl+N   Create a new Writer document
• Ctrl+V   Paste text from clipboard
Marie-Helene wrote:If ever you have an idea or solution, I would really really appreciate.
EnleveDoubleEspace seems to be correcting double spaces. I suggest you enable Tools → Autocorrect Options → Options → Ignore double spaces. This corrects a double space to a single, as you type, thus there is no need for a macro.
Screen Shot 2020-03-05 at 21.41.26.jpg
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.
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).
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Transfer VBA word macros to OpenOffice macro

Post by Zizi64 »

How to create a new, empty document of the LibreOffice/Apache OpenOffice by using the StarBasic and the built-in API functions:
https://wiki.openoffice.org/wiki/Docume ... tarDesktop

Creating New Documents

Apache OpenOffice automatically creates a new document if the document specified in the URL is a template.

Alternatively, if only an empty document without any adaptation is needed, a private:factory URL can be specified:

Code: Select all

Dim Dummy() 
Dim Url As String
Dim Doc As Object
 
Url = "private:factory/swriter"
Doc = StarDesktop.loadComponentFromURL(Url, "_blank", 0, Dummy())
The call creates an empty Apache OpenOffice writer document.
Then you must reference to the object "Doc", when you try paste something into it, or when you try to set the zoom value of the view of document.
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.
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Transfer VBA word macros to OpenOffice macro

Post by JeJe »

You should look at the macro recorder - Tools/Macros/record macro. It doesn't give you the best way of doing things but for a beginner you find a way to do a lot of things eg if you ran the recorder and pressed ctrl+c to copy it would give you some code to do that. The better way is this though:

Code: Select all


Sub CopySelectedToNewDoc()

trans = thiscomponent.currentcontroller.gettransferable 'gets what's selected but without using the clipboard which is better
Doc = StarDesktop.loadComponentFromURL("private:factory/swriter", "_blank", 0, array()) 'create a new document
wait 100 'give the document a bit of time to load may not be needed
doc.currentcontroller.inserttransferable trans 'insert what you got from the original document

End Sub

Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Transfer VBA word macros to OpenOffice macro

Post by JeJe »

Code: Select all

Sub EnleveDoubleEspace()
oReplace = ThisComponent.createReplaceDescriptor()
oReplace.SearchString = "  "
oReplace.ReplaceString = " "
ThisComponent.ReplaceAll(oReplace)
end sub
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Transfer VBA word macros to OpenOffice macro

Post by JeJe »

This you could have got yourself from the macro recorder (start it running then switch to print layout and set the zoom from the view/zoom menu/ click to stop the recorder and save the macro:

Code: Select all

Sub AutoOpen()
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "PrintLayout"
args1(0).Value = true

dispatcher.executeDispatch(document, ".uno:PrintLayout", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(2) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Zoom.Value"
args2(0).Value = 60
args2(1).Name = "Zoom.ValueSet"
args2(1).Value = 28703
args2(2).Name = "Zoom.Type"
args2(2).Value = 0

dispatcher.executeDispatch(document, ".uno:Zoom", "", 0, args2())


end sub
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Marie-Helene
Posts: 6
Joined: Fri Feb 28, 2020 7:21 pm

Re: Transfer VBA word macros to OpenOffice macro

Post by Marie-Helene »

First thank you all for the infos and the time you gave a real "newbie"... I realize it could have been post in the "beginner" section.

For MyProgrammer - I know this is an easy task, select text then 3 clicks... but when you have to do that 30 or 40 times in a row, usually in a rush, this is really time consuming.
By the way "recording" the macro did not work, as the selected text just disappeared in the new doc.

Zizi64 - Thank you so much, it work perfectly fine, and with a button on the menu bar, fantastic. Thanks also for the infos.
By the way, I just fond you TimeStampBackup add-on on another link. How useful! Thank you for your dedication. Working with so much Write doc, things happen regularly (yesterday for instance), I am sure that this will help.

Jeje - Your macro work also really well. The phrasing is different but at this point the result is similar. At least to me eyes.
Thank you also for the 2 others macros. The "remove double spaces" was created because Word was very insconsistent with replacing space (automatic replacement). Until now I feel Openoffice much stable and faster.

I did not expect so much help and support, thank you all to have take so much time for me. I really do appreciate.
Marie-Helene.
Marie-Helene
Openoffice 4.1.7
Window 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Transfer VBA word macros to OpenOffice macro

Post by Lupp »

A recorded macro In LibreOffice or AOO cannot create the new document and get a grip on it. We need to use the API insofar.

You can do it the way JeJe described. You can also do it using the dipatcher.

Anyway: The original VBA macro posted above tampers with formatting in a way Writer isn't made for. Linespacing would be done with the help of a paragraph style and character attributing in a similar way.
In addition I would assume that also in MS Word the given macro only can work for selections meeting some assurances not explicated. (I may be mistaken. I never wrote macros for Word and I didn't use Word for about 20 yesrs now.)

Trying it in an also sloppy way without any error catching it might be done (using the dispatcher) as follows:

Code: Select all

Sub copySelectionPasteInNewDocument()
dispatchHelper   = CreateUnoService("com.sun.star.frame.DispatchHelper")
docSource = ThisComponent
dispProvSource = docSource.CurrentController.Frame
dispatchHelper.executeDispatch(dispProvSource, ".uno:Copy", "", 0, Array())
docTarget = StarDesktop.LoadComponentFromURL("private:factory/swriter", "_blank", 0, Array())
dispProvTarget = docTarget.CurrentController.Frame
dispatchHelper.executeDispatch(dispProvTarget, ".uno:Paste", "", 0, Array())
dispatchHelper.executeDispatch(dispProvTarget, ".uno:SelectAll", "", 0, Array())
pasted = docTarget.CurrentSelection(0)
dispatchHelper.executeDispatch(dispProvTarget, ".uno:ResetAttributes", "", 0, Array())
REM You may want to store the new document now. You need to choose a folder path and a fileneme. Then
REM docTarget.storeAsUrl(ConvertToUrl(THECHOSENPATHNAME, Array())
REM If wanted also: docTarget.close(True)  
End Sub
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Marie-Helene
Posts: 6
Joined: Fri Feb 28, 2020 7:21 pm

Re: Transfer VBA word macros to OpenOffice macro

Post by Marie-Helene »

Thank you Lupp, this is very complete... And I can use the latter operation (target, save and name)
If I do so... And if it is not too much to ask
- is it a way to aks for OpenWrite to suggest a file name (fisrt few words in the file, title or first line) like Word does automaticaly when one "save as".
- is it a way to have the new file saved in the same folder as the original file it come from ? I don't have an idea about how to formulate it.
Thank you in advance...
Marie-Helene
Openoffice 4.1.7
Window 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Transfer VBA word macros to OpenOffice macro

Post by Villeroy »

If you would learn how to use a word processing software properly, you would not need any macro at all. You could easily switch between Writer, Word, WordPerfect, MacWrite and AbiWord. All these word processors are designed to be used by non-programmers. This type of macros works around the software and binds its users to one particular product.
A hands-on approach is http://openoffice-uni.org/
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
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Transfer VBA word macros to OpenOffice macro

Post by Lupp »

Marie-Helene wrote:Thank you Lupp, this is very complete...
It isn't. It is a Q&D, very quick and very dirty piece of code I mainly made to chill.
It doesn't worry at all about expectable errors and lots of other things actually needing to be considered before a program -even a very small one - can be developed.
I'm not a programmer and surely you are none.
Assuming you also don't want to learn programming: Either stick to the software you feel to know sufficiently, or change your strategy including the way you ask questions in a forum.
First learn some basics about the software you want to use from now on.
Consider then what you actually need and want to achieve by a limited project.
Describe it for yourself first - and find partial solutions (of which VBA code surely cannot be a part).
Then come to a forum and ask questions about what you couldn't accomplish on your own.

Let me exemplify it the fictional way:
I have text documents, mostly made with (software / scanner+OCR, ...)...
They contain lots of mostly useless or badly implemented formatting, objects of any kind, comments of no value...
I want to strip them down to get pieces of pure text which I can later use as modules when composing new documents.
The typical size of such a documnt is 5000 by word count, and the typical number of pieces I want to get from one of them is 25. (An average of 200 words per module.)
I can accept the huge lot of files I would get if one file per module is created, but I am interested in better solutions probably suggested.
A nice idea would be a system managing the modules in a way providing easy means for searching.
Of course, the typical MS idea of "firts line as filename" must be ruled out.
...
...
This will not be what you actually wanted to ask, but it shows a kind of asking being really useful for somebody spending his (f/m) time to consider recommendable solutions.
It may be that the solution you end up with actually requires some custom code. If so, this code surely must be of better quality than the Q&D piecees already posted.

There is a majority of questions in a forum like this one, of course, that are much shorter, addressing one technical point, and expexting also a short answer. Guidance concerning complex projects isn't the main strength of the forum way to get help.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Transfer VBA word macros to OpenOffice macro

Post by JeJe »

is it a way to aks for OpenWrite to suggest a file name (fisrt few words in the file, title or first line) like Word does automaticaly when one "save as".
Yes, this code does it in a roundabout way but DON'T USE IT as its not very robust.
(it
gets the first line of the document
saves the file to a temporary location
closes it
reopens it with the property SuggestedSaveAsName set to that first line
opens the save as dialog)

Code: Select all


url =converttourl( "C:\tmp\tmpfile.odt") 'ANY VALID TEMPORARY FILE NAME

tc =thiscomponent.Text.CreateTextCursorByRange(thiscomponent.text.getstart)
tc.gotoendofparagraph(true)
suggestion= tc.string

doc = thiscomponent.storetourl (converttourl(url),array())
Dim FileProperties(0) As New com.sun.star.beans.PropertyValue
FileProperties(0).Name = "SuggestedSaveAsName"
FileProperties(0).Value =suggestion & ".odt"
Doc = StarDesktop.loadComponentFromURL(URL, "_blank", 0, FileProperties())

oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oDispatcher.executeDispatch(doc.currentcontroller.frame, ".uno:SaveAs", "", 0, array())

There's another code here that will save using the first line but without opening the save as dialog

viewtopic.php?f=21&t=20046&p=398042&hil ... me#p398042

by why not just select what you want for the name, use ctrl+c and ctrl+v to paste it into the Save As dialog when it pops up?
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Marie-Helene
Posts: 6
Joined: Fri Feb 28, 2020 7:21 pm

Re: Transfer VBA word macros to OpenOffice macro

Post by Marie-Helene »

Hi Jeje
Thank you, I'll try these codes tonight.
As for your question, I know that in a way they are just sillies macros that does what a few simple clicks can easily do.
But twice a week, I get very long doc files that I have to cut in small portions and save as different files.
5 or 10 file would be fine... But it is usually 30 or 40. So selecting text, copy it, creating a new doc, copying text on it, naming it, and saving it need at least 10 clicks (plus naming), and that forty times in row, that a lot of clicking and take time.
This is why I appreciated so much your help in this matter.
Thank you very much for your time.
Last edited by Marie-Helene on Mon Mar 09, 2020 7:53 pm, edited 1 time in total.
Marie-Helene
Openoffice 4.1.7
Window 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Transfer VBA word macros to OpenOffice macro

Post by JeJe »

So you want to skip the save as dialog. Using my earlier code something like this:

(copies the selection to a new file, uses the first paragraph as the name, saves and closes the file
requires first paragraphs in the selection to all be different and not to be empty)

If you had some way of identifying in the macro each part that you wanted turning into a new file (header style or something) then you could automate it completely of course.

Code: Select all


REM  *****  BASIC  *****

Sub createnewfolderfromSelection

folderpath = "C:\tmp\" 'choose the folder path for all files

'copy the selection
trans = thiscomponent.currentcontroller.gettransferable 'gets what's selected but without using the clipboard which is better
Doc = StarDesktop.loadComponentFromURL("private:factory/swriter", "_blank", 0, array()) 'create a new document
wait 100 'give the document a bit of time to load may not be needed
doc.currentcontroller.inserttransferable trans 'insert what you got from the original document

'get the first paragraph for the name
tc =Doc.Text.CreateTextCursorByRange(doc.text.getstart)
tc.gotoendofparagraph(true)
suggestion = tc.string


'do the replacement
oReplace = doc.createReplaceDescriptor()
oReplace.SearchString = "  "
oReplace.ReplaceString = " "
doc.ReplaceAll(oReplace)

'set the zoom value 
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
document = doc.currentcontroller.frame
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "PrintLayout"
args1(0).Value = true
dispatcher.executeDispatch(document, ".uno:PrintLayout", "", 0, args1())
rem ----------------------------------------------------------------------
dim args2(2) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Zoom.Value"
args2(0).Value = 60
args2(1).Name = "Zoom.ValueSet"
args2(1).Value = 28703
args2(2).Name = "Zoom.Type"
args2(2).Value = 0
dispatcher.executeDispatch(document, ".uno:Zoom", "", 0, args2())

'save the file using the suggestion
pth = folderpath & suggestion & ".odt"
doc.storetourl (converttourl(pth),array())
doc.close(false)
End Sub

Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Transfer VBA word macros to OpenOffice macro

Post by JeJe »

If there's no way of identifying where you want to split the file from its contents you could do something like go through it typing a keyboard character that isn't used in your document ( | maybe ) at the start of each part. A macro to automatically save all the parts marked like that into new documents at once isn't much harder.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Transfer VBA word macros to OpenOffice macro

Post by Lupp »

(In a sense this is a repetition of something I already posted in a different form. More generalized:)

It often is extremely useful for somebody trying to help to know what the questioner finally, eventually wants to achieve.

In your case I really would want to know for what reason you need to split the texts and what use is supposed to be made of the parts into which it was split.

Is there anything realistic about my idea the goal (in general or for the single case) might be a kind of database of text modules?

Anyway: Do you know a reason for what the pieces must be saved to an extra file each, and for what reason that file must be a .odt (full-featured file type for formatted text) instead of a plain text file (.txt e.g.) despite the fact that there is no formatting left?

I simply cannot suggest something reasonable (also concerning the file names) without some answers to my questions.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Marie-Helene
Posts: 6
Joined: Fri Feb 28, 2020 7:21 pm

Re: Transfer VBA word macros to OpenOffice macro

Post by Marie-Helene »

Sorry for the delai.
It is a very repetitive task, that should (I think) be done by the printer, but that is not. So I have to do it.
I explain.
Once or twice a week I get long files, formated word docxs, around 7000 word or 40 000 caracters.
I have to proof read and correct them (often to rewrite them) and send them to be printed.
The printer ask the files to be cut in different shorter docs (about one for each publication or day of the week).
Each pieces is selected (the VBA macros or the new OO macros already keep the format by the way, which is fine), then saved as a ".doc" or "docx file", in the same folder (which are send together), then named for the day of the publication (the first 3 words or first line of the doc), then as different files in Word doc format. With the macros i already have (thank so much to you) it is not so bad. Without it is is very time consuming.
At this point, I am very happy with the macros I have, just that the name of the file don't appear by itself like in the "Word 2010 "save as" option. This is a small annoyance.
Marie-Helene
Openoffice 4.1.7
Window 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Transfer VBA word macros to OpenOffice macro

Post by Villeroy »

And what has OpenOffice got to do with it? Why don't you do that with MS Word?
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
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Transfer VBA word macros to OpenOffice macro

Post by JeJe »

The only way to set a suggestion in the save as dialog is what I did above, (or some variation of it)... it can only be set when you open the file.

But if you're going to accept a suggestion then you may as well skip the save as dialog altogether and save the file directly using code - as the other examples do. You could possibly do that and then change any file names you're not happy with in Windows explorer.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Marie-Helene
Posts: 6
Joined: Fri Feb 28, 2020 7:21 pm

Re: Transfer VBA word macros to OpenOffice macro

Post by Marie-Helene »

Good suggestion Jeje, this is what I do. But I'll try to work it as suggested.
You have been very helpful.
I wish the best to you... And all others
Thank you.
Marie-Helene
Openoffice 4.1.7
Window 10
Post Reply