如何使用VB脚本将多个XML文件转换为Excel文件 [英] how to covert multiple XML files to Excel Files using VB Script

问题描述

我需要使用VBScript将多个XML文件转换为Excel文件。我已经使用下面的脚本来转换XML文件，但是在创建Excel文件时我将其包含在内：

（a）列名称是使用像/ Report / Answers这样的完整路径创建的/答案/答案

（b）还有其他聚合字段，如/ Report / Answers / Answer / Answer /＃agg

（c）列名以相反的顺序显示，即从底层到顶层。



脚本如下所述： < br $> b $ b

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

请提供有关如何修改要排除的代码的输入路径并单独显示列名称以及如何从文件中删除agg列

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

推荐答案

这篇关于如何使用VB脚本将多个XML文件转换为Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！