Wednesday, January 4, 2012

Convert Excel to CSV

Following VBScript works for following problem statement :
- Convert Excel to CSV
- If Excel has multiple sheets save them as ..csv
- Convert all the files in specified directory ( i.e. C:\ )

Dim oFSO
Dim oShell, oExcel, oFile, oSheet
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oShell = CreateObject("WScript.Shell")
Set oExcel = CreateObject("Excel.Application")
oExcel.DisplayAlerts = False

For Each oFile In oFSO.GetFolder("C:\\drop\\MarketShare\\2010").Files
WScript.echo "Hello"
If LCase(oFSO.GetExtensionName(oFile)) = "xls" Then
WScript.echo oFile
With oExcel.Workbooks.Open(oFile, 0, True, , , , True, , , , False, , False)
For Each oSheet In .Worksheets
oSheet.SaveAs "C:\drop\MarketShare\2010\" & oFile.Name & "." & oSheet.Name & ".csv", 6
Next
.Close False, , False
End With
End If
Next
oExcel.Quit
oShell.Popup "Conversion complete", 10