Converting Microsoft XML file format to Excel file

Question

I have requirement that I'm not really sure on how to go about it. I have a file Doc.xml, this is in Microsoft XML format. I need to create a VB script that will change/convert the Doc.xml to Doc.xlsx, so when the user tries to open the file it will open as an Excel file.

One of the requirements is that this script will be run from the Windows Scheduler.

Any ideas or recommendation will be really appreciated.

This is the script I created and is working, but when I try to change the SaveAs extension to ".csv" the file is not being saved correctly. I guess I need to find out what the code is for saving in CSV.

Dim objXLApp, objXLWb, objXLWs

Set objXLApp = CreateObject("Excel.Application")

objXLApp.Visible = True

Set objXLWb = objXLApp.Workbooks.Open("C:\Users\jmejia\Desktop\XML_F\ZOOSHR_130622.xml")

'Do nothing with File, just open it to be save agains as a new file format

objXLWb.SaveAs "C:\Users\jmejia\Desktop\XML_F\ZOOSHR_130622.xlsx", 51

objXLWb.Close (False)

Set objXLWs = Nothing
Set objXLWb = Nothing

objXLApp.Quit
Set objXLApp = Nothing

Did you try writing the vbscript to load the file and save is a different format. It's a 10 line script, tops. At least show us that you made some effort. — David Heffernan
– David Heffernan, Commented Jul 1, 2013 at 19:18

Steve Barnes · Accepted Answer · 2013-07-01 19:04:03Z

1

If your file was created and exported from Excel > 2006? then it will have the tags in it such that double clicking in explorer on a windows machine with any Excel that supports xml format will automatically open it in Excel.

Your file is likely to start with something like:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

edited Jul 1, 2013 at 19:04

answered Jul 1, 2013 at 18:56

Steve Barnes

28.5k6 gold badges68 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kishore Relangi · Accepted Answer · 2014-12-17 12:27:18Z

0

Const xlXLSX = 51

REM 51 = xlOpenXMLWorkbook (without macro's in 2007-2013, xlsx)
REM 52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2013, xlsm)
REM 50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro's, xlsb)
REM 56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)

dim args
dim file
dim sFile
set args=wscript.arguments

dim wshell
Set wshell = CreateObject("WScript.Shell")

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open( wshell.CurrentDirectory&"\"&args(0))

objExcel.DisplayAlerts = FALSE

objExcel.Visible = FALSE

objWorkbook.SaveAs wshell.CurrentDirectory&"\"&args(1), xlXLSX

objExcel.Quit

Wscript.Quit

answered Dec 17, 2014 at 12:27

Kishore Relangi

2,02217 silver badges19 bronze badges

Collectives™ on Stack Overflow

Converting Microsoft XML file format to Excel file

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related