Results 1 to 5 of 5

Thread: export all worksheets to separate csv files

  1. #1
    Junior Member
    Join Date
    Apr 2012
    Posts
    5
    Rep Power
    0

    export all worksheets to separate csv files

    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

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    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

    Code:
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Apr 2012
    Posts
    5
    Rep Power
    0
    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

    Code:
    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
    Last edited by rabidfly; 04-22-2012 at 09:10 PM.

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Thanks for sharing
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Junior Member
    Join Date
    Nov 2012
    Posts
    1
    Rep Power
    0

    How can I modify this script to work on itself?

    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.

Similar Threads

  1. Split Workbook into Separate Workbooks VBA
    By Admin in forum Download Center
    Replies: 12
    Last Post: 08-08-2018, 09:33 PM
  2. Macro To Close All CSV Files
    By Howardc in forum Excel Help
    Replies: 5
    Last Post: 03-15-2014, 05:24 PM
  3. Macro to export sheet as CSV
    By Howardc in forum Excel Help
    Replies: 2
    Last Post: 07-25-2012, 08:59 PM
  4. Separate text with comma
    By sanjeevi888 in forum Excel Help
    Replies: 3
    Last Post: 06-25-2012, 06:02 PM
  5. Collate Data from csv files to excel sheet
    By dhiraj.ch185 in forum Excel Help
    Replies: 16
    Last Post: 03-06-2012, 07:37 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •