Export each sheet to a separate csv file

Question

I need to programmaticaly via a VBA/VBS script export all worksheets (4 in total and I know the names) to worksheet named csv files in the same folder, without loading excel and running the macro manually.

Ideally the script would take in the source filepath/filename.xls and the export filepath as command line arguments.

I have read many Excel VBA scripts for doing just that within Excel and I have seen some for loading an excel workbook to export the first sheet. However when I try to blend the two I get this error:

(1,12) Expected End of Statement

Dim source As Workbook
Set source = Application.Workbooks.Open(WScript.Arguments.Item(0), ReadOnly:=True)
For Each sheet In source.Sheets
.SaveAs Filename:= WScript.Arguments.Item(1) & Source.Sheets.Name, FileFormat:=xlCSV
Next sheet
wb.Close

Answer 1

A vbs to run this code would look something like this.

1. The vbs file can be executed from the commandline
2. The folder name is redundant as if the file exists (the FSO object tests for this) then the folder it resides in must also exist
3. The code automates Excel to separate the sheets

two key points to note compared to your VBA above

• you can't Dim a vbs object as a string, Workbook etc (hence your initial error). You can only Dim them

• you can't used a named constant such as xlCSV in vbscript, hence the use of 6 below as the CSV format

  Dim strFilename  
  Dim objFSO  
  Set objFSO = CreateObject("scripting.filesystemobject")  
  strFilename = "C:\temp\test.xlsx"  
  If objFSO.fileexists(strFilename) Then  
    Call Writefile(strFilename)  
  Else  
    wscript.echo "no such file!"  
  End If  
  Set objFSO = Nothing  
  
  Sub Writefile(ByVal strFilename)  
  Dim objExcel  
  Dim objWB  
  Dim objws  
  
  Set objExcel = CreateObject("Excel.Application")  
  Set objWB = objExcel.Workbooks.Open(strFilename)  
  
  For Each objws In objWB.Sheets  
    objws.Copy  
    objExcel.ActiveWorkbook.SaveAs objWB.Path & "\" & objws.Name & ".csv", 6  
    objExcel.ActiveWorkbook.Close False  
  Next 
  
  objWB.Close False  
  objExcel.Quit  
  Set objExcel = Nothing  
  End Sub  
  

Answer 2

To get you started:

Given an Excel workbook containing a sheet Demo like

-------------------------------
SELECT * FROM Demo
-------------------------------
|F1|F2 |F3        |F4        |
| 1|1.1|12/10/2011|text elm 1|
| 2|2.2|12/11/2011|text elm 2|
| 3|4.4|12/12/2011|text elm 3|
-------------------------------

and an ADODB.Connection with a ConnectionString like:

Provider=MSDASQL.1;Extended Properties="DBQ=<FullPathToYourXls>;Driver={Microsoft
 Excel Driver (*.xls)};

all you need to .Execute is a SELECT INTO statement like

SELECT * INTO [Demo.csv] IN '<PathToYourCsvFolder>' 'Text;' FROM Demo

to get:

type ..\data\ExcelCsv2\Demo.csv
"F1";"F2";"F3";"F4"
1;1,10;10.12.2011 00:00:00;"text elm 1"
2;2,20;11.12.2011 00:00:00;"text elm 2"
3;4,40;12.12.2011 00:00:00;"text elm 3"

(german locale)

The SELECT INTO statement will create the appropriate section

[Demo.csv]
ColNameHeader=True
CharacterSet=1252
Format=Delimited(;)
Col1=F1 Integer
Col2=F2 Float
Col3=F3 Date
Col4=F4 Char Width 50

in the schema.ini file automagically.