General Consultant Discussion

 View Only

  • 1.  Converting multi-sheet Excel to .csv

    Posted 01-14-2020 10:47
    Has anyone converted a multi-sheet Excel file to a .csv file?  I've Googled and read up on some options, but they seem above my pay grade using macros and VB.  I know each sheet can be individually saved, but that's the slow way and I'm looking for a way to take one Excel file with multiple sheets and convert all its sheets to a .csv file all at once.

    ------------------------------
    Jane Scanlan
    Partner, Next Level Manufacturing Consulting Group
    Next Level Manufacturing Consulting Group
    Chanhassen MN
    952-210-7758
    ------------------------------


  • 2.  RE: Converting multi-sheet Excel to .csv

    Posted 01-14-2020 13:42
    Edited by Dan Burleson 01-15-2020 03:19
      |   view attached
    I found this macro which seems to work to save each worksheet as a CSV to the same folder. (also attached - no viruses I promise - I created the attached workbook from scratch and pasted in this code)

    Sub ExportSheetsToCSV()
        Dim xWs As Worksheet
        Dim xcsvFile As String
        For Each xWs In Application.ActiveWorkbook.Worksheets
            xWs.Copy
            xcsvFile = CurDir & "\" & xWs.Name & ".csv"
            Application.ActiveWorkbook.SaveAs Filename: = xcsvFile, _
            FileFormat: = xlCSV, CreateBackup: = False
            Application.ActiveWorkbook.Saved = True
            Application.ActiveWorkbook.Close
        Next
    End Sub


    ------------------------------
    Dan Burleson
    Software Consultant
    Connex Software
    Corvallis OR
    541-224-6642
    ------------------------------

    Attachment(s)

    xlsm
    SaveMultWStoCSV.xlsm   14 KB 1 version


  • 3.  RE: Converting multi-sheet Excel to .csv

    Posted 01-15-2020 13:55
    Does it all need to go into one CSV? Or one CSV per sheet?

    ------------------------------
    Robert Wood
    DDF Consulting Group, Inc.
    Ocala FL
    352-615-5898
    ------------------------------



  • 4.  RE: Converting multi-sheet Excel to .csv

    Posted 01-15-2020 14:03
    @Robert Wood the preference would be to go to one .csv but if that is not possible separate .csv's would be acceptable.​

    ------------------------------
    Jane Scanlan
    Partner, Next Level Manufacturing Consulting Group
    Next Level Manufacturing Consulting Group
    Chanhassen MN
    952-210-7758
    ------------------------------

    Original Message


  • 5.  RE: Converting multi-sheet Excel to .csv

    Posted 01-15-2020 14:46
    The macro that Dan posted above would create separate CSVs for each worksheet.​​ If it's fewer than 1,048,576 rows, a similar macro could be written to combine the worksheets into one and save as a single CSV.

    If there are more than that, then someone would probably have to use VBA code to write out the lines to the CSV file. It's way more complicated and would take a bit to get going if someone doesn't have the code sitting ready.

    Dan's solution should take care of the basic need in the quickest fashion.

    ------------------------------
    Robert Wood
    DDF Consulting Group, Inc.
    Ocala FL
    352-615-5898
    ------------------------------

    Original Message


  • 6.  RE: Converting multi-sheet Excel to .csv

    Posted 01-15-2020 15:04
    Thank you @Robert Wood and @Dan Burleson.  I haven't had a chance to load the macro yet, but I hope to try it by the end of this week.  I sure appreciate the help and input!!​​

    ------------------------------
    Jane Scanlan
    Partner, Next Level Manufacturing Consulting Group
    Next Level Manufacturing Consulting Group
    Chanhassen MN
    952-210-7758
    ------------------------------

    Original Message


  • 7.  RE: Converting multi-sheet Excel to .csv

    Posted 01-16-2020 01:30
    Edited by Dan Burleson 01-16-2020 01:30
      |   view attached
    I missed that you wanted a consolidated CSV. I added a copy command to merge the files into the simple solution I posted earlier. (attached)

    ------------------------------
    Dan Burleson
    Software Consultant
    Connex Software
    Corvallis OR
    541-224-6642
    ------------------------------

    Attachment(s)

    xlsm
    SaveMultWStoCSV.xlsm   19 KB 1 version
    Original Message


Related Content