Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 908
Adam Mills Avatar asked Dec 08 '11 17:12

Adam Mills


1 Answers

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  
    
like image 60
brettdj Avatar answered Oct 17 '22 02:10

brettdj