Macro to open all xmls in folder and save as sxw

Creating a macro - Writing a Script - Using the API

Macro to open all xmls in folder and save as sxw

Postby Guttmann1983 » Fri Jan 04, 2019 5:14 pm

Hi, I am trying to figure out the excel vba macro to use openoffice writer to open xml files and then save them as sxw files. No edits to the files necessary.
Open Office 4.1.5
Win10
Guttmann1983
 
Posts: 11
Joined: Fri Jan 04, 2019 5:11 pm

Re: vba for Writer to open all xmls in folder and save as sx

Postby RoryOF » Fri Jan 04, 2019 5:52 pm

Calc is not Excel and uses a different macro language; why sxw files - this is an old, outmoded format? Do the wizards on OO's /File /Wizards /Document converter not help?
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31239
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: vba for Writer to open all xmls in folder and save as sx

Postby Zizi64 » Fri Jan 04, 2019 6:09 pm

Always use the native, international standard ODF file types (.odt, .ods, .odg, .odp ...) for the Apache OpenOffice and the LibreOffice.
Note: There is not (never was and never will be) 100% compatibility between the different file formats. You will lose some formatting properties at the conversion.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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: 9447
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: vba for Writer to open all xmls in folder and save as sx

Postby Villeroy » Fri Jan 04, 2019 6:20 pm

menu:File>Wizards>Document Converter saves everything as ods.
sxc was the native file format for OpenOffice.org 1.x until 2005
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28444
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: vba for Writer to open all xmls in folder and save as sx

Postby Guttmann1983 » Fri Jan 04, 2019 6:26 pm

I am using excel to perform other functions with excel and word documents. The last thign I need to do is to save the file which is now in .xml format as .sxw. The files have to be in .sxw format because that is the only format they will import, in my use case.
Open Office 4.1.5
Win10
Guttmann1983
 
Posts: 11
Joined: Fri Jan 04, 2019 5:11 pm

Re: vba for Writer to open all xmls in folder and save as sx

Postby Guttmann1983 » Fri Jan 04, 2019 6:48 pm

this is what I was trying -- it opens the file in writer but is not saving it in sxw

Code: Select all   Expand viewCollapse view
Public Sub openSessionOpenOffice(fdir As String)
    Dim serviceManager As Object, oText As Object, oCursor As Object
    Dim Desktop As Object, Document As Object
    Dim Path As String, File As String
    Dim args(), args2(0)
    Dim fso As New FileSystemObject
    Dim sFolder As String, strFilePattern As String
    Dim strFileName As String, sFileName As String, sFileName2 As String
    strFilePattern = "*.xml*"
    sFolder = "C:\brent\test\"
    strFileName = Dir(sFolder & "\xml\" & strFilePattern)
    sFolder2 = sFolder & "sxw\"
    If Not fso.FolderExists(sFolder2) Then fso.CreateFolder sFolder2
    Do While strFileName <> ""
        sFileName = sFolder & strFileName
        File = "file:///" & sFolder & "xml\" & strFileName
        File = Application.WorksheetFunction.Substitute(File, "\", "/")
        File2 = sFolder2 & Left(strFileName, InStrRev(strFileName, ".") - 1) & ".sxw"
        Set serviceManager = CreateObject("com.sun.star.serviceManager")
        Set Desktop = serviceManager.createInstance("com.sun.star.frame.Desktop")
        Set Document = Desktop.loadComponentFromURL(File, "_blank", 0, args)
        Document.StoreAsURL (File2)
        Document.Modified = False
        Document.Close (True)
        fCount = fCount + 1
        strFileName = Dir$()
    Loop

End Sub
Open Office 4.1.5
Win10
Guttmann1983
 
Posts: 11
Joined: Fri Jan 04, 2019 5:11 pm

Re: vba for Writer to open all xmls in folder and save as sx

Postby RoryOF » Fri Jan 04, 2019 6:50 pm

As far as I know, OO 4.x does not have sxw filters.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31239
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: vba for Writer to open all xmls in folder and save as sx

Postby Villeroy » Fri Jan 04, 2019 7:03 pm

RoryOF wrote:As far as I know, OO 4.x does not have sxw filters.

It has. sxw (StarOffice, XML, Writer) was the predecessor of the ODF standard with its odt (Open Document Text) files. Before sxw there was a binary file format (stw ?) which is no longer supported.
It makes no sense to save the latest Microsoft format in an outdated format. This has many draw backs with no advantage.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28444
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: vba for Writer to open all xmls in folder and save as sx

Postby RoryOF » Fri Jan 04, 2019 7:21 pm

Thanks for the correction, Villeroy.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31239
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: vba for Writer to open all xmls in folder and save as sx

Postby Guttmann1983 » Fri Jan 04, 2019 7:44 pm

I am not saying that it makes sense, I am saying that I do not have a choice. It needs to be in .sxw format to be imported.
Open Office 4.1.5
Win10
Guttmann1983
 
Posts: 11
Joined: Fri Jan 04, 2019 5:11 pm

Re: vba for Writer to open all xmls in folder and save as sx

Postby Zizi64 » Fri Jan 04, 2019 8:41 pm

Here are the older versions of the LibreOffice, even the portable versions. Try it one of them. As I remember, the LO 3.x.x versions can save into the older (.sx_ formats)

http://downloadarchive.documentfoundati ... ffice/old/
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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: 9447
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: vba for Writer to open all xmls in folder and save as sx

Postby Guttmann1983 » Fri Jan 04, 2019 8:44 pm

I know that 4.1.5 can. I just need to know what the vba macro would be.
Open Office 4.1.5
Win10
Guttmann1983
 
Posts: 11
Joined: Fri Jan 04, 2019 5:11 pm

Re: vba for Writer to open all xmls in folder and save as sx

Postby Zizi64 » Fri Jan 04, 2019 9:04 pm

Can you upload some sample documents here?
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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: 9447
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: vba for Writer to open all xmls in folder and save as sx

Postby Guttmann1983 » Fri Jan 04, 2019 9:11 pm

sure. attached.
Attachments
Example2.xml
(48.23 KiB) Downloaded 63 times
Example1.xml
(48.23 KiB) Downloaded 56 times
Open Office 4.1.5
Win10
Guttmann1983
 
Posts: 11
Joined: Fri Jan 04, 2019 5:11 pm

Re: vba for Writer to open all xmls in folder and save as sx

Postby Zizi64 » Fri Jan 04, 2019 9:22 pm

Try to use real URL-s and the URL conversion functions of the OpenOffice/Libreoffice API/Basic:
ConvertToURL()
ConvertFromURL()
For the Load... and the Store... statements too.

https://wiki.openoffice.org/wiki/URL_Basics
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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: 9447
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: vba for Writer to open all xmls in folder and save as sx

Postby Guttmann1983 » Fri Jan 04, 2019 9:26 pm

I dont understand what that means. I understand excel macros pretty well but not openoffice.
Open Office 4.1.5
Win10
Guttmann1983
 
Posts: 11
Joined: Fri Jan 04, 2019 5:11 pm

Re: vba for Writer to open all xmls in folder and save as sx

Postby Zizi64 » Fri Jan 04, 2019 9:31 pm

Code: Select all   Expand viewCollapse view
Public Sub openSessionOpenOffice(fdir As String)
    Dim serviceManager As Object, oText As Object, oCursor As Object
    Dim Desktop As Object, Document As Object
    Dim Path As String, File As String
    Dim args(), args2(0)
    Dim fso As New FileSystemObject
    Dim sFolder As String, strFilePattern As String
    Dim strFileName As String, sFileName As String, sFileName2 As String
    strFilePattern = "*.xml*"
    sFolder = "C:\brent\test\"
    strFileName = Dir(sFolder & "\xml\" & strFilePattern)
    sFolder2 = sFolder & "sxw\"
    If Not fso.FolderExists(sFolder2) Then fso.CreateFolder sFolder2
    Do While strFileName <> ""
        sFileName = sFolder & strFileName
        File = "file:///" & sFolder & "xml\" & strFileName
        File = Application.WorksheetFunction.Substitute(File, "\", "/")
        File2 = sFolder2 & Left(strFileName, InStrRev(strFileName, ".") - 1) & ".sxw"
        Set serviceManager = CreateObject("com.sun.star.serviceManager")
        Set Desktop = serviceManager.createInstance("com.sun.star.frame.Desktop")
        Set Document = Desktop.loadComponentFromURL(File, "_blank", 0, args)
        Document.StoreAsURL (File2)
        Document.Modified = False
        Document.Close (True)
        fCount = fCount + 1
        strFileName = Dir$()
    Loop

End Sub


You macro IS NOT a VBA macro. It is a StarBasic macro based on the API (Application Programming Interface) functions of the AOO and LO - mixed with some VBA commands.
You need totally rewrite it based on the API functions.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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: 9447
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: vba for Writer to open all xmls in folder and save as sx

Postby Guttmann1983 » Fri Jan 04, 2019 9:33 pm

Okay. My macro runs in excel. That is what I am after. Perhaps I need to go to an excel forum because we are not getting anywhere here.
Open Office 4.1.5
Win10
Guttmann1983
 
Posts: 11
Joined: Fri Jan 04, 2019 5:11 pm

Re: vba for Writer to open all xmls in folder and save as sx

Postby RoryOF » Fri Jan 04, 2019 9:36 pm

Here is another macro running on Python using PyODConverter
https://www.oooninja.com/2008/02/batch-command-line-file-conversion-with.html

It may need tweaking for more recent OpenOffice version.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31239
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: vba for Writer to open all xmls in folder and save as sx

Postby Zizi64 » Fri Jan 04, 2019 9:38 pm

My macro runs in excel.

Really in the Excel? Or do you meant: in the AOO/LO CALC?

I think, the Excel CAN NOT save/export in the .sxw fileformat...
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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: 9447
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: vba for Writer to open all xmls in folder and save as sx

Postby Guttmann1983 » Fri Jan 04, 2019 9:44 pm

Yes in excel... what I provided already opens the file..

you can see in the link below that it is possible (youll need to use google translate to translate the webpage), i just am not sure how to save as sxw as the format

https://www.developpez.net/forums/d7216 ... fice-base/
Open Office 4.1.5
Win10
Guttmann1983
 
Posts: 11
Joined: Fri Jan 04, 2019 5:11 pm

Re: vba for Writer to open all xmls in folder and save as sx

Postby Villeroy » Sat Jan 05, 2019 12:30 am

Run the same OpenOffice installer that was used to install your current version.
It will prompt you to repair, customize or remove the installation. You want to customize.
Add the "experimental XML filters" (or similar).
Now you can read and write those old fashioned Microsoft XML files.
The new file filter appears as "Microsoft Excel 2003 XML" in the GUI. The internal filter name is "MS Excel 2003 XML".
Do not save spreadsheets as sxc. Save them as ods which can be opened with may spreadsheet applications including recent versions of MS Excel.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28444
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to open all xmls in folder and save as sxw

Postby Lupp » Sat Jan 05, 2019 4:22 pm

Did I miss something?
I cannot find an answer telling what FilterName and what FilterProperties to use when needing to write a text document into an .sxw file using the document methods storeToUrl or storeAsUrl.
Did someone know and tell? (I don't know. Respective information is not easily found.)

Anyway storeAsUrl and storeToUrl expect a second parameter which should be an array as you get it by
Code: Select all   Expand viewCollapse view
Dim myArgs(u) As New com.sun.beans.PropertyValue
in OpenOffice Basic where u is the upper bound of the index range starting with 0. (I never used the ServiceManager for the purpose.)
Now comes:
Code: Select all   Expand viewCollapse view
myArgs(0).Name = "FilterName"
myArgs(0).Value = "just (an) example"
myArgs(1).Name = ...
and so on. Different filters may allow for different property names. Here I was wrong. The differences are encapsulated in the properties FilterOptions and FilterData. (Sparse documentation if any to find.)
Finally:
Code: Select all   Expand viewCollapse view
theDocument.storeAsUrl(pFilePathInUrlFormat, myArgs)

See the well known texts by Andrew Pitonyak.
Last edited by Lupp on Sat Jan 05, 2019 8:26 pm, edited 2 times in total.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2897
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Macro to open all xmls in folder and save as sxw

Postby Guttmann1983 » Sat Jan 05, 2019 5:07 pm

Thankyou Lupp, no you did not miss anything.
Open Office 4.1.5
Win10
Guttmann1983
 
Posts: 11
Joined: Fri Jan 04, 2019 5:11 pm

Re: Macro to open all xmls in folder and save as sxw

Postby Lupp » Sat Jan 05, 2019 5:57 pm

Assuming nextUrl is containing the complete filepath of the file to save to in URL notation
Code: Select all   Expand viewCollapse view
Dim myStoreArgs(0) As New com.sun.star.beans.PropertyValue
myStoreArgs(0).Name  = "FilterName"
myStoreArgs(0).Value = "StarOffice XML (Writer)"
theDoc.storeToUrl(nextUrl, myStoreArgs)
as OpenOffice Basic code should do with AOO. (The filter seems to be no longer supported by recent versions of LibreOffice.)
I don't know how to get that in Excel-VBA. Never wrote a line of VBA code.

Amendment
Code: Select all   Expand viewCollapse view
File = "file:///" & sFolder & "xml\" & strFileName
won't work. OpenOffice expects the ordinary slash as the path delimiter. The basic offers the functions convertToUrl to make sure to get the correct representation even if running on a system with different synatx. convertFromUrl system-localises an URL if it represents a file.
Under Win a file-url looks like "file:///C:/Users/Myself/Documents/SomeFolder/_workbench/firstTest.sxw".

Suggestion:
Since you need an OpeOffice anyway to do the conversion, it should be easier to achieve what you want if you use an OpenOffice document as your "Batch Processor" instead of an Excel thing.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2897
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Macro to open all xmls in folder and save as sxw

Postby Guttmann1983 » Mon Jan 07, 2019 5:29 pm

Okay, do you have an example of what the openoffice macro would be by chance? I could use excel to open an openoffice document and run a macro but I am unfamiliar with openoffice macros.
Open Office 4.1.5
Win10
Guttmann1983
 
Posts: 11
Joined: Fri Jan 04, 2019 5:11 pm

Re: Macro to open all xmls in folder and save as sxw

Postby Villeroy » Mon Jan 07, 2019 6:19 pm

Well, then do it with Excel.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28444
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to open all xmls in folder and save as sxw

Postby John_Ha » Mon Jan 07, 2019 9:47 pm

Guttmann1983 wrote:I am unfamiliar with openoffice macros.

Google pitonyak for his excellent book on OpenOffice macros.

.sxw is completely obsolete - you need to use .odt and change the receiving application so it accepts .odt files. For an overview of the problems see [Tutorial] Differences between Writer and MS Word files for why you should always work in and save files as .odt. In the diagram consider .sxw to be more akin to ,rtf than to .odt.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7785
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Macro to open all xmls in folder and save as sxw

Postby RoryOF » Mon Jan 07, 2019 9:50 pm

Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31239
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests