4

i have an excel workbook which has got some 125 worksheets and i need to be able to save each of those 125 worksheets into csv files(so that i end up with a total of 125 csv files)

am currently able to do this manually(file>save as>csv file type) but only in such a manner: 1st worksheet-->1st csv 2nd worksheet-->2nd csv . . and so on

is there a way to save the entire workbook at once- as opposed to saving just the currently active sheet,then moving on to the next one....?

user221238
  • 75
  • 1
  • 5
  • 13

2 Answers2

0

I tried using the script on this page but had issues with the Copy command failing and I'm not sure why. So I made a simplified version of the script which does the job nicely for me. It creates a CSV file for each worksheet in the workbook, named the same as the workbook, but with the worksheet name appended.

To run the script, press Alt + F11 to bring up the Visual Basic tool and then choose Insert - Module. Copy and paste the script below and press F5 to run it. This should result in what you were looking for in the original question.

Sub ExportSheetsToCSV()
    Dim xWs As Worksheet
    Dim xcsvFile As String
    Dim originalFileName As String
    originalFileName = ActiveWorkbook.Name
    For Each xWs In Application.ActiveWorkbook.Worksheets
        xWs.Activate
        xcsvFile = ActiveWorkbook.Path & "\" & originalFileName & "_" & xWs.Name & ".csv"
        Application.ActiveWorkbook.SaveAs Filename:=xcsvFile, _
        FileFormat:=xlCSV, CreateBackup:=False
    Next
End Sub
Allen Pestaluky
  • 787
0

From Use VBA Macro to Save each Excel Worksheet as Separate Workbook by Sorceri:

Keeping the worksheet in the existing workbook and creating a new workbook with a copy

Dim path As String
Dim dt As String
dt = Now()
path = CreateObject("WScript.Shell").specialfolders("Desktop") & "\Calendars " & Replace(Replace(dt, ":", "."), "/", ".")
MkDir path
Call Shell("explorer.exe" & " " & path, vbNormalFocus)

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets 'SetVersions
    If ws.Name <> "How-To" And ws.Name <> "Actg_Prd" Then
        Dim wb As Workbook
        Set wb = ws.Application.Workbooks.Add
        ws.Copy Before:=wb.Sheets(1)
        wb.SaveAs path & ws.Name, Excel.XlFileFormat.xlOpenXMLWorkbook
        Set wb = Nothing
    End If
Next ws

Another solution can by found at Create a workbook from every worksheet in your workbook:

This macro will copy every visible worksheet in a new workbook and save the workbook with the name of the sheet in a newly created folder in the same path as the workbook with this macro.

Community
  • 1
DavidPostill
  • 156,873
  • thnks davidpostill....i tried the first of the 2 macros and it worked flawlessly. it took in one workbook with abt 125 sheets and spit out 125 seperate workbooks. but still, how do i go about saving each one of them as csv – user221238 Sep 07 '14 at 07:35
  • i created a macro(not excel macro) which uses previously recorded keyboard and mouse input along with various other environment variables, using a software called jitbit macro recorder and hence solved this problem :) but its mostly luckware coz things can go awry even if theres a very minor change in environment....................it was nice if the excel macros posted above also saved those excel documents – user221238 Sep 07 '14 at 07:40
  • the excel macro by david is perfect. however a few more lines of code to the macro can give it the ability to also save the files that it generates so am marking it as the best one – user221238 Sep 07 '14 at 07:49
  • or maybe the macro also saves(wb.SaveAs path & ws.Name, Excel.XlFileFormat.xlOpenXMLWorkbook) but i dont know where to – user221238 Sep 07 '14 at 08:19
  • update: the 2nd excel macro by david did the job for me with 100% accuracy – user221238 Sep 07 '14 at 09:03