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

Thanks Scott, I couldn't make that happen – E Purdy Jul 06 '16 at 16:20 — E Purdy, Jul 06 '16 at 16:20

score 2 · Answer 1 · edited Jul 06 '16 at 21:36

2

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"

edited Jul 06 '16 at 21:36

answered Jul 06 '16 at 16:41

Scott Holtzman

27,099
5
37
72

@ThomasInzina - The Copy method is very much a function. What is the error you get? It may need a bit more argument with `VBS` though. – Scott Holtzman Jul 06 '16 at 19:45
@ThomasInzina - see my edit. I think I know why the line didn't work. – Scott Holtzman Jul 06 '16 at 19:51
1

oExcel.ActiveWorkbook is not available in VBScript – Jul 06 '16 at 19:55
1

I am sorry I was wrong. It's ActiveWorksheet that isn't available. – Jul 06 '16 at 21:18

Dave · Answer 2 · 2016-07-06T20:19:50.997

2

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"

edited Jul 06 '16 at 20:19

answered Jul 06 '16 at 16:55

Dave

4,328
2
24
33

1

The Copy method is not a function. This line fails `Set oBookNew1 = oBook.Worksheets(1).Copy` – Jul 06 '16 at 19:43
You're right - fixed it now. (though the line you mention wasn't actually in my code anyway, I used `shIndex` rather than 1) :) – Dave Jul 06 '16 at 20:21
oExcel.ActiveWorkbook is not available in VBScript – Jul 06 '16 at 20:46
Yes it is... `ActiveWorkbook` is available via the `application` object, which is what `oExcel` was set to. I tried it in VBSEdit and the intellisense happily agreed with me – Dave Jul 06 '16 at 20:56
1

I apologize. I was wrong. It's ActiveWorksheet that isn't available. – Jul 06 '16 at 21:19
On a slightly related note, although `ActiveWorksheet` isn't available, you CAN use the identical `ActiveSheet` from any workbook level object. – Dave Jul 06 '16 at 21:34
1

That makes since. I assume the `Application.Cell(1,1)` is invalid also. Thanks – Jul 06 '16 at 21:50

score 2 · Answer 3 · 2016-07-06T21:26:01.260

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"

score 0 · Answer 4 · answered Jul 07 '16 at 10:31

0

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

answered Jul 07 '16 at 10:31

Julian

1,694
22
29

Auto convert Xls to CSV

4 Answers4

Linked