XML to Excel XLS conversion

[fritz]

I have seen this question asked before on many newsgroups and websites
but no one was able to answer this.

The group I am working in is nearly finished a new reporting process
that outputs all our groups reporting into excel files on a LAN
drive. We are using SAS for report generation and would like to take
advantage of the extra features in the ExcelXP tagset. For non SAS
folks, it allows us to create spreadsheets in Excel XML format.

The problem is the size of the output files compared to a binary xls
file. They are just way too big. Some reach about 100 Mbytes in
size. The only solution I have seen on the web is to the "Save As.."
an XLS file inside Excel. This is an automated reporting process that
will generate any where between 50 and 600 spreadsheets per day. The
"Save as" feature is not going to cut it. We don't have the budget
for that many temps to open and close files. Leaving it as XML will
probably give our LAN admin a heart attack.

Does any one know of a command line utility that can convert XML
spreadsheets to a binary XLS file?

If not has any one using SAS been able to get around this size issue
with the ODS method? Proc export will do it but we loose a lot of
features with the output of the spreadsheet.

 

[Joerg]

You could use VB Script to run Excel, loop through all files of a
Sourcefolder (that's were you store your XML files) and let Excel save them
into a Targetfolder as normal XLS files. Since Excel needs to be opened only
once and doesn't have to be visible, the whole process is pretty
quick..well, at least quicker than doing this stuff manually. I have written
a quick and dirty script, which I think does what you need. Adapt it as you
please. Put the script into a text file with extension '.vbs'. Let me know
if it works.
Cheers
Joerg Mochikun

Dim xlApp, xlWkb, SourceFolder,TargetFolder,file
Set xlApp = CreateObject("excel.application")
set fs = CreateObject("Scripting.FileSystemObject")
Const xlNormal=1
SourceFolder="c:\temp\XMLfiles"
TargetFolder="c:\temp\XLSfiles"

'Hide Excel
xlApp.Visible = false

'Process each file in SourceFolder
for each file in fs.GetFolder(SourceFolder).files
'Open file in SourceFolder
Set xlWkb = xlApp.Workbooks.Open(file)
'Get Filename
BaseName= fs.getbasename(file)
'Concatenate full path. Extension will be automatically added by Excel
FullTargetPath=TargetFolder & "\" & BaseName
'Save as XLS file into TargetFolder
xlWkb.SaveAs FullTargetPath, xlNormal
'Close file
xlWkb.close
next

Set xlWkb = Nothing
Set xlApp = Nothing
Set fs = Nothing

msgbox "Finished. Have a nice day!"

 

[fritz]

Your solution is probably the one that works best. I haven't seen any
fancy command line utility that does this in a batch file. So your
solution seems the most workable. Thanks for the code. I will
definately play with it.

We are looking into other options as well. PDF might be a possible
output format that we can do, and save on size.