how to covert multiple XML files to Excel Files using VB Script

See more:

VB

script

I have a requirement to convert multiple XML files to Excel files using VBScript. I have used the below script to convert the XML files, but on creation of the Excel files I encouter this:
(a) The column names are getting created with the full path like "/Report/Answers/Answer/Answer"
(b) There are additional fields for aggregation like "/Report/Answers/Answer/Answer/#agg"
(c) The column names are displayed in the reverse order i.e. bottom to top level.

The Script is as mentioned below:

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

Kindly provide me inputs on how to modify the code to exclude the path and display the column names alone and also how to remove the agg columns from the file