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 (http://wiki.interfaceware.com/612.html)

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.