export all worksheets to separate csv files

[rabidfly]

I'd like to export all worksheets from an excel file to individual CSV files. It should run via wscript and take 2 input options: 1. path-to-excel-file 2. path-to-destination-folder

I'm a systems admin, not a programmer, so I'm technically advanced, but VBA is beyond my skills. :D

Thanks,
Dan

[Admin]

Hi rabidfly,

Welcome to ExcelFox !!!

Open a blank workbook, hit Alt + F11, Go to Insert > Module and paste this code there in the white pane.

Hit Alt+Q to close the VBE window.

Now hit Alt + F8, select 'ExportAllShtsToCSV' and click on RUN

Sub ExportAllShtsToCSV()
    
    Dim ExcelFilePath   As String
    Dim DestFolder      As String
    Dim wbkExcel        As Workbook
    Dim i               As Long
    
    With Application.FileDialog(3)
        .AllowMultiSelect = False
        .ButtonName = "Select"
        .Filters.Add "Excel Files", "*.xls;*.xlsb;*.xlsx"
        .InitialFileName = ThisWorkbook.Path
        If .Show = -1 Then
            ExcelFilePath = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    
    
    With Application.FileDialog(4)
        .AllowMultiSelect = False
        .ButtonName = "Select"
        .InitialFileName = ThisWorkbook.Path
        If .Show = -1 Then
            DestFolder = .SelectedItems(1)
        Else
            Exit Sub
        End If
        DestFolder = DestFolder & Application.PathSeparator
    End With
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    Set wbkExcel = Workbooks.Open(ExcelFilePath, 0)
    
    With wbkExcel
        For i = 1 To .Worksheets.Count
            .Worksheets(i).SaveAs DestFolder & .Worksheets(i).Name, 6
        Next
    End With
            
    wbkExcel.Close 0
    Set wbkExcel = Nothing
            
    MsgBox "Done"
            
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
            
End Sub

[rabidfly]

Wow, thanks for that! but, that wasn't what I needed. I need to run this as part of a batch file.

I found the following code to work great: Convert an xls file to CSV

I run it like so: wscript xls2cvs.vbs C:\SourceFile.xls C:\DestFile.csv

if WScript.Arguments.Count < 2 Then
    WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
    Wscript.Quit
End If

Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
Dim oSheet

If oBook.Sheets.count = 1 Then 
  'save a single sheet
  oBook.SaveAs WScript.Arguments.Item(1), 6
else
  'save multiple sheets
  i=1
  aname=split(Wscript.Arguments.Item(1),".",-1,1)
  For Each oSheet In oBook.WorkSheets
    fname = aname(0) & "_sheet" & Cstr(i)
    oSheet.SaveAs fname, 6
    i=i+1
  Next   
End If

oBook.Close True
oExcel.Quit
WScript.Quit
set oSheet = Nothing
set oBook = Nothing
set oExcel = Nothing

[Admin]

Thanks for sharing

[Mo33er]

This script does exactly what I need however I'd like to make it even easier for the user I have in mind.

Ideally I'd make it a macro within the template file I want to give them. Then by them running the macro it would save the CSV's in the same subfolder.

If the source file with the sheets was called "folder\template.xlsm" then running the macro would put the sheets into their separate csv's like this

folder\template.sheet1.csv
folder\template.sheet2.csv
etc.

I'm confident this can be done...I'm just not up to the job!

Thanks - Rob.