XML to DOC/PDF (Indidual Reports from ASM)

**tben2505** · 7th November 2011, 04:31 PM

Hi folks,

As most of you will be aware, the Individual Reports facility export the Word document reports as XML files.

Whilst I appreciate this is for efficiency reasons, many of our staff find it confusing and some machines are not set up properly to open them with the correct software (and we have many Macs, too).

So I was wondering if anyone knew of any software to convert them straight to Word dox or even PDFs; just so that the staff can double click on them and not be presented with XML code which scares them and makes them thing everything is broken!

Cheers,

Tom

**LosOjos** · 7th November 2011, 04:34 PM

Copy and paste this script in to Notepad, save it with a .vbs extension, then simply drag the folder containing all the SIMS generated XML documents on to it (make sure ONLY the XML documents are in the folder):

Code:

'==========================================================================
'
' NAME: Word XML to DOC.vbs
'
' AUTHOR: Josh Johnson
' DATE  : 04/02/2011
'
' COMMENT: Convert SIMS XML files in to XLS files
' HOW TO USE: Drag a folder containing the XML files onto the script.
'==========================================================================
Option Explicit
On Error Resume Next

Dim Arg 			'Script argument
Dim FSO			'File System Object
Dim oFol			'Folder object
Dim oFil			'File object
Dim oFils		'Files
Dim myWord		'Word object
Dim myDoc		'Document object
Dim filePath	'Path to file (need for Word to open the file)

'Check arguments
If WScript.Arguments.Length < 1 Then
	MsgBox "Drag a folder containing Word XML files onto this script", vbOKOnly, "Error!"
	WScript.Quit
End If 

Set FSO = CreateObject("Scripting.FileSystemObject")
Arg = WScript.Arguments(0)
If FSO.FolderExists(Arg) = False Then
   MsgBox "Drag and drop a folder onto this script to print the contents."
   Set FSO = Nothing
   WScript.Quit
End If

Set myWord = CreateObject("Word.Application")
myWord.Visible = True
myWord.Application.DisplayAlerts = False

'Get folder informaton
Set oFol = FSO.GetFolder(Arg)

Set oFils = oFol.Files
   For Each oFil in oFils  '-- enumerate files in the folder using For/Each. Each oFil is a File object.
	filePath = oFil
	Set myDoc = myWord.Documents.Open(filePath, True, True, False, , , False, , , 8, , True)
	myDoc.SaveAs oFol.Path & "\" & left(FSO.GetFileName(oFil), Len(FSO.GetFileName(oFil))-4) & ".doc", 0
	myDoc.Close
	oFil.Delete
	If Err <> 0 Then MsgBox "Error converting " & FSO.GetFileName(oFil) & vbNewLine & Err.Description, vbOKOnly, "Error"
	Err.Clear
	Set myDoc = Nothing
   Next 
myWord.Application.Quit
Set myWord = Nothing
Set oFils = Nothing
Set oFol = Nothing
MsgBox "Complete!", vbOKOnly, "Converting done"


Function strClean (strtoclean)
Dim objRegExp, outputStr
Set objRegExp = New Regexp

objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.Pattern = "[(?*"",\\<>&#~%{}+_.@:\/!;]+"
outputStr = objRegExp.Replace(strtoclean, "-")

objRegExp.Pattern = "\-+"
outputStr = objRegExp.Replace(outputStr, "-")

strClean = outputStr
End Function

EDIT: should probably explain what it does! The script will open each XML document in word and resave it as a DOC, then remove the original XML. I created it for compatibility issues much like you're having where staff aren't aware they need to open the XML in Word to view it properly.

**tben2505** · 7th November 2011, 04:50 PM

Originally Posted by LosOjos

Copy and paste this script in to Notepad, save it with a .vbs extension, then simply drag the folder containing all the SIMS generated XML documents on to it (make sure ONLY the XML documents are in the folder):

Code:

'==========================================================================
'
' NAME: Word XML to DOC.vbs
'
' AUTHOR: Josh Johnson
' DATE  : 04/02/2011
'
' COMMENT: Convert SIMS XML files in to XLS files
' HOW TO USE: Drag a folder containing the XML files onto the script.
'==========================================================================
Option Explicit
On Error Resume Next

Dim Arg 			'Script argument
Dim FSO			'File System Object
Dim oFol			'Folder object
Dim oFil			'File object
Dim oFils		'Files
Dim myWord		'Word object
Dim myDoc		'Document object
Dim filePath	'Path to file (need for Word to open the file)

'Check arguments
If WScript.Arguments.Length < 1 Then
	MsgBox "Drag a folder containing Word XML files onto this script", vbOKOnly, "Error!"
	WScript.Quit
End If 

Set FSO = CreateObject("Scripting.FileSystemObject")
Arg = WScript.Arguments(0)
If FSO.FolderExists(Arg) = False Then
   MsgBox "Drag and drop a folder onto this script to print the contents."
   Set FSO = Nothing
   WScript.Quit
End If

Set myWord = CreateObject("Word.Application")
myWord.Visible = True
myWord.Application.DisplayAlerts = False

'Get folder informaton
Set oFol = FSO.GetFolder(Arg)

Set oFils = oFol.Files
   For Each oFil in oFils  '-- enumerate files in the folder using For/Each. Each oFil is a File object.
	filePath = oFil
	Set myDoc = myWord.Documents.Open(filePath, True, True, False, , , False, , , 8, , True)
	myDoc.SaveAs oFol.Path & "\" & left(FSO.GetFileName(oFil), Len(FSO.GetFileName(oFil))-4) & ".doc", 0
	myDoc.Close
	oFil.Delete
	If Err <> 0 Then MsgBox "Error converting " & FSO.GetFileName(oFil) & vbNewLine & Err.Description, vbOKOnly, "Error"
	Err.Clear
	Set myDoc = Nothing
   Next 
myWord.Application.Quit
Set myWord = Nothing
Set oFils = Nothing
Set oFol = Nothing
MsgBox "Complete!", vbOKOnly, "Converting done"


Function strClean (strtoclean)
Dim objRegExp, outputStr
Set objRegExp = New Regexp

objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.Pattern = "[(?*"",\\<>&#~%{}+_.@:\/!;]+"
outputStr = objRegExp.Replace(strtoclean, "-")

objRegExp.Pattern = "\-+"
outputStr = objRegExp.Replace(outputStr, "-")

strClean = outputStr
End Function

EDIT: should probably explain what it does! The script will open each XML document in word and resave it as a DOC, then remove the original XML. I created it for compatibility issues much like you're having where staff aren't aware they need to open the XML in Word to view it properly.

WOW!

You genius!!!

Thank you SO much

**tben2505** · 7th November 2011, 04:51 PM

Do you have on for XML > XLS, too?

And once converted to XLS; do they still go back into SIMS ok??

Cheers

T

**LosOjos** · 7th November 2011, 04:54 PM

Originally Posted by tben2505

Do you have on for XML > XLS, too?

Indeed I do:

Code:

'==========================================================================
'
' NAME: Excel XML to XLS.vbs
'
' AUTHOR: Josh Johnson
' DATE  : 04/02/2011
'
' COMMENT: Convert SIMS XML files in to XLS files
' HOW TO USE: Drag a folder containing the XML files onto the script.
'==========================================================================
Option Explicit
On Error Resume Next

Dim Arg 			'Script argument
Dim FSO			'File System Object
Dim oFol			'Folder object
Dim oFil			'File object
Dim oFils		'Files
Dim myExcel		'Excel object
Dim myWorkbook		'Workbook object
Dim filePath	'Path to file (need for Excel to open the file)

'Check arguments
If WScript.Arguments.Length < 1 Then
	MsgBox "Drag a folder containing Excel XML files onto this script", vbOKOnly, "Error!"
	WScript.Quit
End If 

Set FSO = CreateObject("Scripting.FileSystemObject")
Arg = WScript.Arguments(0)
If FSO.FolderExists(Arg) = False Then
   MsgBox "Drag and drop a folder onto this script to print the contents."
   Set FSO = Nothing
   WScript.Quit
End If

Set myExcel = CreateObject("Excel.Application")
myExcel.Visible = True
myExcel.Application.DisplayAlerts = False

'Get folder informaton
Set oFol = FSO.GetFolder(Arg)

Set oFils = oFol.Files
   For Each oFil in oFils  '-- enumerate files in the folder using For/Each. Each oFil is a File object.
	filePath = oFil
	Set myWorkbook = myExcel.Workbooks.Open(filePath)
	myWorkbook.SaveAs oFol.Path & "\" & left(FSO.GetFileName(oFil), Len(FSO.GetFileName(oFil))-4) & ".xls", 56
	myWorkbook.Close
	oFil.Delete
	If Err <> 0 Then MsgBox "Error converting " & FSO.GetFileName(oFil) & vbNewLine & Err.Description, vbOKOnly, "Error"
	Err.Clear
	Set myWorkbook = Nothing
   Next 
myExcel.Application.Quit
Set myExcel = Nothing
Set oFils = Nothing
Set oFol = Nothing
MsgBox "Complete!", vbOKOnly, "Converting done"


Function strClean (strtoclean)
Dim objRegExp, outputStr
Set objRegExp = New Regexp

objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.Pattern = "[(?*"",\\<>&#~%{}+_.@:\/!;]+"
outputStr = objRegExp.Replace(strtoclean, "-")

objRegExp.Pattern = "\-+"
outputStr = objRegExp.Replace(outputStr, "-")

strClean = outputStr
End Function

That one works exactly the same as the previous

Originally Posted by tben2505

And once converted to XLS; do they still go back into SIMS ok??

Not as simply as the XML, as the XML stores additional data to allow SIMS to fully automate the re-import process, however you can still import it, it just requires you to manually identify the aspect and result set for each column (I'm afraid that's a limitation of XLS that can't be avoided)

**tben2505** · 7th November 2011, 04:55 PM

Splendid my friend: thanks again you're a real help!

Tom

**LosOjos** · 7th November 2011, 05:00 PM

Originally Posted by tben2505

Splendid my friend: thanks again you're a real help!

Tom

Any time

**tben2505** · 8th November 2011, 01:33 PM

Originally Posted by LosOjos

Copy and paste this script in to Notepad, save it with a .vbs extension, then simply drag the folder containing all the SIMS generated XML documents on to it (make sure ONLY the XML documents are in the folder):

Code:

'==========================================================================
'
' NAME: Word XML to DOC.vbs
'
' AUTHOR: Josh Johnson
' DATE  : 04/02/2011
'
' COMMENT: Convert SIMS XML files in to XLS files
' HOW TO USE: Drag a folder containing the XML files onto the script.
'==========================================================================
Option Explicit
On Error Resume Next

Dim Arg 			'Script argument
Dim FSO			'File System Object
Dim oFol			'Folder object
Dim oFil			'File object
Dim oFils		'Files
Dim myWord		'Word object
Dim myDoc		'Document object
Dim filePath	'Path to file (need for Word to open the file)

'Check arguments
If WScript.Arguments.Length < 1 Then
	MsgBox "Drag a folder containing Word XML files onto this script", vbOKOnly, "Error!"
	WScript.Quit
End If 

Set FSO = CreateObject("Scripting.FileSystemObject")
Arg = WScript.Arguments(0)
If FSO.FolderExists(Arg) = False Then
   MsgBox "Drag and drop a folder onto this script to print the contents."
   Set FSO = Nothing
   WScript.Quit
End If

Set myWord = CreateObject("Word.Application")
myWord.Visible = True
myWord.Application.DisplayAlerts = False

'Get folder informaton
Set oFol = FSO.GetFolder(Arg)

Set oFils = oFol.Files
   For Each oFil in oFils  '-- enumerate files in the folder using For/Each. Each oFil is a File object.
	filePath = oFil
	Set myDoc = myWord.Documents.Open(filePath, True, True, False, , , False, , , 8, , True)
	myDoc.SaveAs oFol.Path & "\" & left(FSO.GetFileName(oFil), Len(FSO.GetFileName(oFil))-4) & ".doc", 0
	myDoc.Close
	oFil.Delete
	If Err <> 0 Then MsgBox "Error converting " & FSO.GetFileName(oFil) & vbNewLine & Err.Description, vbOKOnly, "Error"
	Err.Clear
	Set myDoc = Nothing
   Next 
myWord.Application.Quit
Set myWord = Nothing
Set oFils = Nothing
Set oFol = Nothing
MsgBox "Complete!", vbOKOnly, "Converting done"


Function strClean (strtoclean)
Dim objRegExp, outputStr
Set objRegExp = New Regexp

objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.Pattern = "[(?*"",\\<>&#~%{}+_.@:\/!;]+"
outputStr = objRegExp.Replace(strtoclean, "-")

objRegExp.Pattern = "\-+"
outputStr = objRegExp.Replace(outputStr, "-")

strClean = outputStr
End Function

EDIT: should probably explain what it does! The script will open each XML document in word and resave it as a DOC, then remove the original XML. I created it for compatibility issues much like you're having where staff aren't aware they need to open the XML in Word to view it properly.

LosOjos, I don't suppose I could be particularly fussy and ask for this script to process the files not in alphabetical order, but in Date Modified Order (earliest to latest) or just in the order that they are in the folder to begin with?

It's just, I export the XML files for the year group sorted by reg group, so they come out in that order, therefore processing them in that order to convert to Word then allows me to convert to one single Word/PDF file still in reg group order!

Just if it's possible, otherwise I can just export separate batches by reg group

**vikpaw** · 15th November 2011, 01:41 PM

I tried to make a script to sort the contents once but it was too complex, the vbs commands didn't seem capable of managing it easily.If it doesn't process in the order you have it setup, then it's probably easier just to do it in batches.

**LosOjos** · 15th November 2011, 03:58 PM

Originally Posted by tben2505

LosOjos, I don't suppose I could be particularly fussy and ask for this script to process the files not in alphabetical order, but in Date Modified Order (earliest to latest) or just in the order that they are in the folder to begin with?

Sorry about the epic delay in replying to you, but I've just not had time to look at it until now.

I believe this will do the trick:

Code:

'==========================================================================
'
' NAME: Word XML to DOC [By Date].vbs
'
' AUTHOR: Josh Johnson
' DATE  : 04/02/2011
'
' COMMENT: Convert SIMS XML files in to XLS files [date sorted]
' HOW TO USE: Drag a folder containing the XML files onto the script.
'==========================================================================
Option Explicit
On Error Resume Next

Const adDate = 7
Const adVarChar = 200 

Dim Arg 			'Script argument
Dim FSO			'File System Object
Dim oFol			'Folder object
Dim oFil			'File object
Dim oFils		'Files
Dim myWord		'Word object
Dim myDoc		'Document object
Dim filePath	'Path to file (need for Word to open the file)

Dim rsFolder	'recordset to sort folder

'Check arguments
If WScript.Arguments.Length < 1 Then
	MsgBox "Drag a folder containing Word XML files onto this script", vbOKOnly, "Error!"
	WScript.Quit
End If 

Set FSO = CreateObject("Scripting.FileSystemObject")
Arg = WScript.Arguments(0)
If FSO.FolderExists(Arg) = False Then
   MsgBox "Drag and drop a folder onto this script to print the contents."
   Set FSO = Nothing
   WScript.Quit
End If
Set rsFolder = CreateObject("ADODB.Recordset")

'Get folder informaton
Set oFol = FSO.GetFolder(Arg)

With rsFolder.Fields
	.Append "Path", adVarChar, 200
	.Append "Name", adVarChar, 200
	.Append "DateLastModified", adDate
End With
rsFolder.Open()

Set oFils = oFol.Files
For Each oFil in oFils
	rsFolder.AddNew
	rsFolder("Path") = oFil
	rsFolder("Name") = oFil.Name
	rsFolder("DateLastModified") = oFil.DateLastModified
Next
rsFolder.Sort = "DateLastModified ASC"

Set myWord = CreateObject("Word.Application")
myWord.Visible = True
myWord.Application.DisplayAlerts = False

rsFolder.MoveFirst()
While Not rsFolder.EOF
	Set myDoc = myWord.Documents.Open(rsFolder("Path").Value, True, True, False, , , False, , , 8, , True)
	myDoc.SaveAs Left(rsFolder("Path").Value, Len(rsFolder("Path").Value)-4) & ".doc", 0
	myDoc.Close
	FSO.DeleteFile rsFolder("Path").Value
	If Err <> 0 Then MsgBox "Error converting " & rsFolder("Name").Value & vbNewLine & Err.Description, vbOKOnly, "Error"
	Err.Clear
	Set myDoc = Nothing
	rsFolder.MoveNext()
Wend
myWord.Application.Quit
Set myWord = Nothing
Set oFils = Nothing
Set oFol = Nothing
MsgBox "Complete!", vbOKOnly, "Converting done"


Function strClean (strtoclean)
Dim objRegExp, outputStr
Set objRegExp = New Regexp

objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.Pattern = "[(?*"",\\<>&#~%{}+_.@:\/!;]+"
outputStr = objRegExp.Replace(strtoclean, "-")

objRegExp.Pattern = "\-+"
outputStr = objRegExp.Replace(outputStr, "-")

strClean = outputStr
End Function

EDIT: Just updated the code, there was a major error in it!

**vikpaw** · 16th November 2011, 07:10 AM

Nice one @losojos - that was one of the methods i'd seen, but didn't get round to making it work properly.

**HanLouTay** · 26th January 2012, 10:34 AM

Originally Posted by LosOjos

Sorry about the epic delay in replying to you, but I've just not had time to look at it until now.

I believe this will do the trick:

Code:

'==========================================================================
'
' NAME: Word XML to DOC [By Date].vbs
'
' AUTHOR: Josh Johnson
' DATE  : 04/02/2011
'
' COMMENT: Convert SIMS XML files in to XLS files [date sorted]
' HOW TO USE: Drag a folder containing the XML files onto the script.
'==========================================================================
Option Explicit
On Error Resume Next

Const adDate = 7
Const adVarChar = 200 

Dim Arg 			'Script argument
Dim FSO			'File System Object
Dim oFol			'Folder object
Dim oFil			'File object
Dim oFils		'Files
Dim myWord		'Word object
Dim myDoc		'Document object
Dim filePath	'Path to file (need for Word to open the file)

Dim rsFolder	'recordset to sort folder

'Check arguments
If WScript.Arguments.Length < 1 Then
	MsgBox "Drag a folder containing Word XML files onto this script", vbOKOnly, "Error!"
	WScript.Quit
End If 

Set FSO = CreateObject("Scripting.FileSystemObject")
Arg = WScript.Arguments(0)
If FSO.FolderExists(Arg) = False Then
   MsgBox "Drag and drop a folder onto this script to print the contents."
   Set FSO = Nothing
   WScript.Quit
End If
Set rsFolder = CreateObject("ADODB.Recordset")

'Get folder informaton
Set oFol = FSO.GetFolder(Arg)

With rsFolder.Fields
	.Append "Path", adVarChar, 200
	.Append "Name", adVarChar, 200
	.Append "DateLastModified", adDate
End With
rsFolder.Open()

Set oFils = oFol.Files
For Each oFil in oFils
	rsFolder.AddNew
	rsFolder("Path") = oFil
	rsFolder("Name") = oFil.Name
	rsFolder("DateLastModified") = oFil.DateLastModified
Next
rsFolder.Sort = "DateLastModified ASC"

Set myWord = CreateObject("Word.Application")
myWord.Visible = True
myWord.Application.DisplayAlerts = False

rsFolder.MoveFirst()
While Not rsFolder.EOF
	Set myDoc = myWord.Documents.Open(rsFolder("Path").Value, True, True, False, , , False, , , 8, , True)
	myDoc.SaveAs Left(rsFolder("Path").Value, Len(rsFolder("Path").Value)-4) & ".doc", 0
	myDoc.Close
	FSO.DeleteFile rsFolder("Path").Value
	If Err <> 0 Then MsgBox "Error converting " & rsFolder("Name").Value & vbNewLine & Err.Description, vbOKOnly, "Error"
	Err.Clear
	Set myDoc = Nothing
	rsFolder.MoveNext()
Wend
myWord.Application.Quit
Set myWord = Nothing
Set oFils = Nothing
Set oFol = Nothing
MsgBox "Complete!", vbOKOnly, "Converting done"


Function strClean (strtoclean)
Dim objRegExp, outputStr
Set objRegExp = New Regexp

objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.Pattern = "[(?*"",\\<>&#~%{}+_.@:\/!;]+"
outputStr = objRegExp.Replace(strtoclean, "-")

objRegExp.Pattern = "\-+"
outputStr = objRegExp.Replace(outputStr, "-")

strClean = outputStr
End Function

EDIT: Just updated the code, there was a major error in it!

Hey, I have done as suggested but when I open my saved VBS file it opens up Spybot S&D File Scanner! I presume this is an issue with my computer rather than the script. Any suggestions?

**LosOjos** · 26th January 2012, 10:42 AM

Originally Posted by HanLouTay

Hey, I have done as suggested but when I open my saved VBS file it opens up Spybot S&D File Scanner! I presume this is an issue with my computer rather than the script. Any suggestions?

I'm afraid I don't have a clue! It could be that you have SpyBot's Resident TeaTimer feature enabled and this is blocking the script because it is trying to access your file system (don't worry, it only accesses files in the folder you drag on to the script in order to open and convert them as necessary). Try temporarily disabling TeaTimer before running the script.

Another possibility is that you've accidentally associated .VBS files with SpyBot so it's trying to open the script rather than Windows running it. Try my first solution and if you still have a problem, let me know which version of Windows your running and I'll dig out the instructions to check file associations for you.

**HanLouTay** · 26th January 2012, 10:48 AM

Thanks for the speedy reply!

I'm on a school network so I don't want to/can't go about fiddling with settings!

I've been using notepad ++ to paste the script in to. I tried normal notepad but it didn't give differing file types (ie VBS) to save to. Maybe this is where I am going wrong?!

thanks

**LosOjos** · 26th January 2012, 11:03 AM

Originally Posted by HanLouTay

Thanks for the speedy reply!

I'm on a school network so I don't want to/can't go about fiddling with settings!

I've been using notepad ++ to paste the script in to. I tried normal notepad but it didn't give differing file types (ie VBS) to save to. Maybe this is where I am going wrong?!

thanks

Possibly; it definitely needs a .VBS extension to work. Can you see the file extension when you view it in Explorer? If so, check that it is .VBS and simply change it if not (VBS are just plain text files really, but the VBS extension tells Windows to run the contents as a script).

It may also be that your Network Manager has disabled script running for users - it might be worth having a word with them

LinkBack

Thread Tools

Search Thread

XML to DOC/PDF (Indidual Reports from ASM)

Thanks to LosOjos from:

Thanks to LosOjos from:

My computer doesn't seem to like VBS files! Help!

Similar Threads

Move From HTML / XHTML To HTML5 [PDF]

PDF to DOC

Upgrade to WDS now thinstation reports errors

wps to doc converter

how to disable the messenger service from the server

Thread Information

Users Browsing this Thread

Posting Permissions