Macro to open all xmls in folder and save as sxw

[Guttmann1983]

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.

[RoryOF]

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?

[Zizi64]

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.

[Villeroy]

menu:File>Wizards>Document Converter saves everything as ods.
sxc was the native file format for OpenOffice.org 1.x until 2005

[Guttmann1983]

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.

[Guttmann1983]

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

Code: Select all

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

[RoryOF]

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

[Villeroy]

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.

[RoryOF]

Thanks for the correction, Villeroy.

[Guttmann1983]

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.

[Zizi64]

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/

[Guttmann1983]

I know that 4.1.5 can. I just need to know what the vba macro would be.

[Zizi64]

Can you upload some sample documents here?

[Guttmann1983]

sure. attached.

[Zizi64]

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

[Guttmann1983]

I dont understand what that means. I understand excel macros pretty well but not openoffice.

[Zizi64]

Code: Select all

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.

[Guttmann1983]

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.

[RoryOF]

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

It may need tweaking for more recent OpenOffice version.

[Zizi64]

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...

[Guttmann1983]

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/

[Villeroy]

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.

[Lupp]

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

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

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

theDocument.storeAsUrl(pFilePathInUrlFormat, myArgs) 

See the well known texts by Andrew Pitonyak.

[Guttmann1983]

Thankyou Lupp, no you did not miss anything.

[Lupp]

Assuming nextUrl is containing the complete filepath of the file to save to in URL notation

Code: Select all

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

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.

[Guttmann1983]

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.

[Villeroy]

Well, then do it with Excel.

[John_Ha]

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.

[RoryOF]

The best source book is
OpenOffice.org Macros Explained.odt V3