• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Export multiple worksheets in a file to multiple csv files

jk51

Member
Hi,
Do you know how to export multiple worksheets in an excel workbook to individual csv files in a folder using VBA excel? I am current using excel 2010.
For example I have 50 worksheets (sheet1, sheet2, sheet3...., sheet50) in a excel workbook file. Click the button "Browse and export" to run the macro, ask you where you want to export , browse file directory and select the folder then all csv files export in that folder. Note the csv file name of csv file should be same as the individual worksheets. e.g. sheet1.csv, sheet2.csv, sheet3.csv….., sheet50.csv.

Thank you.

Bw

Mr Singh
 
Hi !

Use Excel function SaveAs, activating Macro recorder
you'll get a base of code …

Use FileDialog(msoFileDialogFolderPicker) to choose a folder
(to see in VBA inner help and in threads of any forum).

You can roll Worksheets via a For Each loop …
 
Thanks Marc for your reply. I am new to VBA excel. Is there an example program to show me like my query?

Thanks.
Mr Singh
 
Yes, you can easily start with a search on each keyword.​
As reading VBA inner help should let you start your code as tutorials too …​
 
Hi,

Here is my code but need help to add FileDialog(msoFileDialogFolderPicker) to choose a folder. This program manually edit the file directory :

***

Public Sub SaveAllSheetsAsCSV()
On Error GoTo Heaven
' each sheet reference
Dim Sheet As Worksheet
' path to output to
Dim OutputPath As String
' name of each csv
Dim OutputFile As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
' Save the file in current directory
OutputPath = "c:\output"

If OutputPath <> "" Then
Application.Calculation = xlCalculationManual
' save for each sheet
For Each Sheet In Sheets
OutputFile = OutputPath & Application.PathSeparator & Sheet.Name & ".csv"
' make a copy to create a new book with this sheet
' otherwise you will always only get the first sheet
Sheet.Copy
' this copy will now become active
ActiveWorkbook.SaveAs FileName:=OutputFile, FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
Next
Application.Calculation = xlCalculationAutomatic
End If
Finally:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Exit Sub
Heaven:
MsgBox "Couldn't save all sheets to CSV." & vbCrLf & _
"Source: " & Err.Source & " " & vbCrLf & _
"Number: " & Err.Number & " " & vbCrLf & _
"Description: " & Err.Description & " " & vbCrLf
GoTo Finally
End Sub

***

Thanks

Mr Singh
 
Two points :
• use code tags as advised in the frame above the answer frame
as exists an icon too specific for code
• indent your code …​
Code:
Sub SaveAllSheetsAsCSV()
        Dim OutputPath As String, WSheet As Worksheet
    With Application.FileDialog(msoFileDialogFolderPicker)
             .ButtonName = "Select"
        .InitialFileName = "C:\"
            .InitialView = msoFileDialogViewList
                  .Title = Space$(27) & "Select a folder :"
                If .Show = 0 Then Exit Sub
              OutputPath = .SelectedItems(1) & Application.PathSeparator
    End With
        Application.Calculation = xlCalculationManual
        Application.DisplayAlerts = False
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        On Error GoTo Heaven
    For Each WSheet In Worksheets
        WSheet.Copy
        ActiveWorkbook.SaveAs OutputPath & WSheet.Name & ".csv", xlCSV, CreateBackup:=False
        ActiveWorkbook.Close
    Next
Heaven:
        Application.Calculation = xlCalculationAutomatic
        Application.DisplayAlerts = True
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    If Err.Number Then MsgBox "Couldn't save all sheets to CSV." & vbLf & "Source: " & Err.Source & vbLf & _
                              "Number: " & Err.Number & vbLf & "Description: " & Err.Description
End Sub
Do you like it ? So thanks to click on bottom right Like !
 
Excellent Marc. Works perfect.

One more thing is it possible to export multiple worksheets to dbf v4?

Thank you.
Mr Singh
 

Yes until 2003 Excel version ! You can search on Web for an add-in …

Or just using Libre Office …
 
Back
Top