Click here to Skip to main content
15,878,430 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

VB
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
Posted
Updated 17-Jul-14 23:49pm
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900