Export each sheet to a separate csv file

by

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

More Related Contents:

  1. Run Excel Macro from Outside Excel Using VBScript From Command Line
  2. How to set recurring schedule for xlsm file using Windows Task Scheduler
  3. Save each sheet in a workbook to separate CSV files
  4. Code for looping through all excel files in a specified folder, and pulling data from specific cells
  5. Bad data selection using VBScript , on date field values
  6. How do I use awk under cygwin to print fields from an excel spreadsheet?
  7. SumIf using Excel VBA
  8. Test or check if sheet exists
  9. Copy from one workbook and paste into another
  10. How to copy Outlook mail message into excel using VBA or Macros
  11. For Loop not fully cycling in excel VBA [duplicate]
  12. Populate unique values into a VBA array from Excel
  13. Excel VBA App stops spontaneously with message “Code execution has been halted”
  14. How to remove all non alphanumeric characters from a string except period and space in excel?
  15. Closing Excel Application using VBA
  16. How to clear memory to prevent “out of memory error” in excel vba?
  17. Checking if a worksheet-based checkbox is checked
  18. Faster way to remove ‘extra’ spaces (more than 1) from a large range of cells using VBA for Excel
  19. Extract Data from Word Document to an Excel SpreadSheet
  20. VBA equivalent to Excel’s mod function
  21. Pointers needed for speeding up nested loop macro in VBA
  22. ‘1004’: “The sort reference is not valid.”
  23. Select entire column in table using Excel VBA
  24. Reducing WithEvent declarations and subs with VBA and ActiveX
  25. Excel VBA – select multiple columns not in sequential order
  26. How to check whether certain sheets exist or not in Excel-VBA? [duplicate]
  27. Application.WorksheetFunction.Match method
  28. Excel – Using COUNTIF/COUNTIFS across multiple sheets/same column
  29. Call a Unix Script from Excel Vba
  30. Excel telling me my blank cells aren’t blank

Leave a Comment