CODEWITHSUNDEEP
xmlcsvvbscriptexport-to-csvfile-conversion
chenks
chenks

Reputation: 139

VBScript to convert XML to CSV

I have a VBScript that converts any XML file in a folder to XLS and then deletes the XML files - all works fine.

However, I know need to convert the XMLs to CSV rather than XLS.

What do I need to change in the script to allow this? Simply changing the extension of the resulting file(s) obviously doesn't work.

Dim xlApp, xlWkb, SourceFolder,TargetFolder,file
Set xlApp = CreateObject("excel.application")
Set fs = CreateObject("Scripting.FileSystemObject")

Const xlNormal=1

SourceFolder="c:\xml-to-xls\xml"
TargetFolder="c:\xml-to-xls\xls"

xlApp.Visible = false

for each file in fs.GetFolder(SourceFolder).files
  Set xlWkb = xlApp.Workbooks.Open(file)
  BaseName= fs.getbasename(file)
  FullTargetPath=TargetFolder & "\" & BaseName & ".xls"
  xlWkb.SaveAs FullTargetPath, xlNormal
  xlWkb.close
next

fs.DeleteFile("C:\xml-to-xls\xml\*.xml")

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

Thanks

Upvotes: 0

Views: 12965

Answers (2)

chenks
chenks

Reputation: 139

thanks guys... here is the finished script

Dim xlApp, xlWkb, SourceFolder,TargetFolder,file
Set xlApp = CreateObject("excel.application")
Set fs = CreateObject("Scripting.FileSystemObject")

Const xlNormal=1
Const xlCSV=6

SourceFolder="c:\xml-to-xls\xml"
TargetFolder="c:\xml-to-xls\xls"

xlApp.Visible = false

for each file in fs.GetFolder(SourceFolder).files
  Set xlWkb = xlApp.Workbooks.Open(file)
  BaseName= fs.getbasename(file)
  FullTargetPath=TargetFolder & "\" & BaseName & ".csv"
  xlWkb.SaveAs FullTargetPath, xlCSV, , , , , , 2
  xlWkb.Saved = True
  xlWkb.close
  file.Delete
next

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

Upvotes: 2

ServerGuy
ServerGuy

Reputation: 555

Updating as per the comments: Thanks guys

Const xlCSV = 6
xlWkb.SaveAs FullTargetPath, xlCSV, , , , , , 2
xlWbk.Saved = True
xlWkb.close

Upvotes: 2

Related Questions