Macro to open all xmls in folder and save as sxw
-
- Posts: 11
- Joined: Fri Jan 04, 2019 5:11 pm
Macro to open all xmls in folder and save as sxw
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
Win10
Re: vba for Writer to open all xmls in folder and save as sx
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.15 on Xubuntu 22.04.4 LTS
Re: vba for Writer to open all xmls in folder and save as sx
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.
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; 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.
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.
Re: vba for Writer to open all xmls in folder and save as sx
menu:File>Wizards>Document Converter saves everything as ods.
sxc was the native file format for OpenOffice.org 1.x until 2005
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 11
- Joined: Fri Jan 04, 2019 5:11 pm
Re: vba for Writer to open all xmls in folder and save as sx
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
Win10
-
- Posts: 11
- Joined: Fri Jan 04, 2019 5:11 pm
Re: vba for Writer to open all xmls in folder and save as sx
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
Open Office 4.1.5
Win10
Win10
Re: vba for Writer to open all xmls in folder and save as sx
As far as I know, OO 4.x does not have sxw filters.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: vba for Writer to open all xmls in folder and save as sx
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.RoryOF wrote:As far as I know, OO 4.x does not have sxw filters.
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: vba for Writer to open all xmls in folder and save as sx
Thanks for the correction, Villeroy.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
-
- Posts: 11
- Joined: Fri Jan 04, 2019 5:11 pm
Re: vba for Writer to open all xmls in folder and save as sx
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
Win10
Re: vba for Writer to open all xmls in folder and save as sx
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/
http://downloadarchive.documentfoundati ... ffice/old/
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.
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.
-
- Posts: 11
- Joined: Fri Jan 04, 2019 5:11 pm
Re: vba for Writer to open all xmls in folder and save as sx
I know that 4.1.5 can. I just need to know what the vba macro would be.
Open Office 4.1.5
Win10
Win10
Re: vba for Writer to open all xmls in folder and save as sx
Can you upload some sample documents 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.
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.
-
- Posts: 11
- Joined: Fri Jan 04, 2019 5:11 pm
Re: vba for Writer to open all xmls in folder and save as sx
sure. attached.
- Attachments
-
- Example2.xml
- (48.23 KiB) Downloaded 186 times
-
- Example1.xml
- (48.23 KiB) Downloaded 180 times
Open Office 4.1.5
Win10
Win10
Re: vba for Writer to open all xmls in folder and save as sx
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
ConvertToURL()
ConvertFromURL()
For the Load... and the Store... statements too.
https://wiki.openoffice.org/wiki/URL_Basics
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.
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.
-
- Posts: 11
- Joined: Fri Jan 04, 2019 5:11 pm
Re: vba for Writer to open all xmls in folder and save as sx
I dont understand what that means. I understand excel macros pretty well but not openoffice.
Open Office 4.1.5
Win10
Win10
Re: vba for Writer to open all xmls in folder and save as sx
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 need totally rewrite it based on the API functions.
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.
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.
-
- Posts: 11
- Joined: Fri Jan 04, 2019 5:11 pm
Re: vba for Writer to open all xmls in folder and save as sx
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
Win10
Re: vba for Writer to open all xmls in folder and save as sx
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.
https://www.oooninja.com/2008/02/batch- ... -with.html
It may need tweaking for more recent OpenOffice version.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: vba for Writer to open all xmls in folder and save as sx
Really in the Excel? Or do you meant: in the AOO/LO CALC?My macro runs in excel.
I think, the Excel CAN NOT save/export in the .sxw fileformat...
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.
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.
-
- Posts: 11
- Joined: Fri Jan 04, 2019 5:11 pm
Re: vba for Writer to open all xmls in folder and save as sx
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/
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
Win10
Re: vba for Writer to open all xmls in folder and save as sx
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.
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Macro to open all xmls in folder and save as sxw
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 byin 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: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:
See the well known texts by Andrew Pitonyak.
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
Now comes:
Code: Select all
myArgs(0).Name = "FilterName"
myArgs(0).Value = "just (an) example"
myArgs(1).Name = ...
Finally:
Code: Select all
theDocument.storeAsUrl(pFilePathInUrlFormat, myArgs)
Last edited by Lupp on Sat Jan 05, 2019 8:26 pm, edited 2 times 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
---
Lupp from München
-
- Posts: 11
- Joined: Fri Jan 04, 2019 5:11 pm
Re: Macro to open all xmls in folder and save as sxw
Thankyou Lupp, no you did not miss anything.
Open Office 4.1.5
Win10
Win10
Re: Macro to open all xmls in folder and save as sxw
Assuming nextUrl is containing the complete filepath of the file to save to in URL notation
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.
Amendmentwon'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.
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)
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
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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
-
- Posts: 11
- Joined: Fri Jan 04, 2019 5:11 pm
Re: Macro to open all xmls in folder and save as sxw
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
Win10
Re: Macro to open all xmls in folder and save as sxw
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Macro to open all xmls in folder and save as sxw
Google pitonyak for his excellent book on OpenOffice macros.Guttmann1983 wrote:I am unfamiliar with 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.
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.
Re: Macro to open all xmls in folder and save as sxw
The best source book is
OpenOffice.org Macros Explained.odt V3
OpenOffice.org Macros Explained.odt V3
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS