Auto convert Xls to CSV

Question

I am using the following VBS script found on stackoverflow to convert xls to csv. It works fine. I want to run it with the batch file at the bottom. I don't know how to achieve what I want. The batch file gives the csv file the same name as the xls file. Because the xls file has two worksheets in it I need to produce two csv's for each xls file

if WScript.Arguments.Count < 2 Then
    WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
    Wscript.Quit
End If
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
oBook.SaveAs WScript.Arguments.Item(1), 6
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(1))
oBook.SaveAs WScript.Arguments.Item(2), 6

oBook.Close False
oExcel.Quit
WScript.Echo "Done"

Here's the batch file

FOR /f "delims=" %%i IN ('DIR *.xlsx /b') DO ExcelToCSV.vbs "%%i" "%%i.csv"

I need to pass in 2 output .csv file names one should be nnnnn_1.csv the other should be nnnnn_2.csv to account for the 2 worksheets in the xls files.

Thanks for any help

Answer 1

If you change the VBS script to this it should work:

If WScript.Arguments.Count < 2 Then
    WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
    Wscript.Quit
End If

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))

oBook.Worksheets(1).Copy
Dim oBookNew1     
Set oBookNew1 = oExcel.ActiveWorkbook
oBookNew1.SaveAs Replace(WScript.Arguments.Item(1),".csv","_1.csv"), 6
oBookNew1.Close False

oBook.Worksheets(2).Copy
Dim oBookNew1     
Set oBookNew1 = oExcel.ActiveWorkbook
oBookNew2.SaveAs Replace(WScript.Arguments.Item(1),".csv","_2.csv"), 6
oBookNew2.Close False

oBook.Close False
oExcel.Quit
WScript.Echo "Done"

Answer 2

This script will save every worksheet in the workbook as [cvs base name] - [worksheet name].csv.

If WScript.Arguments.Count < 2 Then
    WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
    WScript.Quit
End If


Dim BaseName, oExcel, oBook, xlWorksheet, i

BaseName = Replace( WScript.Arguments.Item(0), ".cvs", "")
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(WScript.Arguments.Item(0))

For Each xlWorksheet In oBook.Worksheets
    xlWorksheet.Copy
    oExcel.ActiveWorkbook.SaveAs BaseName & " - " & xlWorksheet.Name & ".csv"
    oExcel.ActiveWorkbook.Close False
Next

oBook.Close False
oExcel.Quit
WScript.Echo "Done"

Answer 3

Similarly to Scott's answer above, I would probably change the script to work this way, since this change will just work for all sheets in a given workbook and output each to a .csv file without worrying if there are 1, 2 or 10 sheets.

If WScript.Arguments.Count < 2 Then
    WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
    Wscript.Quit
End If

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook 
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
Dim oNewBook
shIndex = 1 ' start with 1
For shIndex = 1 To oBook.Worksheets.Count     
    oBook.Worksheets(shIndex).Copy
    Set oNewBook = oExcel.ActiveWorkbook
    oNewBook.SaveAs Replace(WScript.Arguments.Item(1),".csv","_" & shIndex & ".csv"), 6
    oNewBook.Close False
Next
oBook.Close False
oExcel.Quit
WScript.Echo "Done"

Answer 4

I have written an alternative solution in Python that exports the present excel sheets of a workbook in CVS format.

You can find it here

Best, Julian