CODEWITHSUNDEEP
excelcsvvbscriptvba
Adam Mills
Adam Mills

Reputation: 85

Export each sheet to a separate csv file

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

Upvotes: 6

Views: 22705

Answers (2)

brettdj
brettdj

Reputation: 55682

A 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

Upvotes: 15

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38745

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.

Upvotes: -1

Related Questions