Export each sheet to a separate csv file

Solution 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