CODEWITHSUNDEEP
vbaexcelloopscsv
user53423103981023
user53423103981023

Reputation: 85

Parse through excel workbooks and save specific tabs as .csv files

I need help saving specific tabs from excel workbooks as 1) csv files, and 2) files named after the file from which they originate.

So far I have this which works in taking out the right tab from a single workbook and saving it as a .csv file. 

Sub Sheet_SaveAs()
 Dim wb As Workbook
 Sheets("SheetName").Copy      
 Set wb = ActiveWorkbook      
 With wb
   .SaveAs ThisWorkbook.Path & "\SheetName.csv"
   '.Close False
 End With
End Sub

How do I rename "SheetName" so that the file saves as, for example, Workbook1SheetName, Workbook2SheetName etc.?

I want to loop this function through a folder of many, many excel files, so unique names are necessary for the new .csv files.

I found this online which shows how to use a VBA loop, http://www.ozgrid.com/VBA/loop-through.htm. In theory it should work with my code above as long as each .csv file can have a unique name. Correct me if I am wrong.

Thanks for the help.

Upvotes: 2

Views: 808

Answers (1)

user4039065
user4039065

Reputation:

As you've discovered, when you copy a worksheet with no destination you end up with a new workbook populated by the copy of worksheet. The new workbook becomes the ActiveWorkbook property in the VBA environment. To save the workbook as a CSV file you need to retrieve the Worksheet .Name property of the sole worksheet in that new workbook.

Sub All_Sheet_SaveAs_CSV()
    Dim w As Long, wb As Workbook
    Dim fp As String, fn As String

    On Error GoTo bm_Safe_Exit
    'Application.ScreenUpdating = False   'stop screen flashing
    Application.DisplayAlerts = False    'stop confirmation alerts

    'start with a reference to ThisWorkbook
    With ThisWorkbook
        fp = .Path
        'cycle through each of the worksheets
        For w = 1 To Worksheets.Count
            With Worksheets(w)
                .Copy
                'the ActiveWorkbook is now the new workbook populated with a copy of the current worksheet
                With ActiveWorkbook
                    fn = .Worksheets(1).Name
                    .SaveAs Filename:=fp & Chr(92) & fn, FileFormat:=xlCSV
                    .Close savechanges:=False   '<~~ already saved in line above
                End With
            End With
        Next w
    End With

bm_Safe_Exit:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

Note that turning .DisplayAlerts will turn off the warnings that you would normally receive. While this is helpful with the CSV file format, it also will not warn you if you attempt to overwrite a file.

The Workbook.SaveAs method with a file format parameter of xlCSV will supply the correct file extension. There is no need to include it as part of the filename.

If a limited number of specific worksheets are intended to receive the export-to-CSV procedure, then an array of worksheet names could be cycled through rather than the worksheet index position.

Sub Specific_Sheets_SaveAs_CSV()
    Dim v As Long, vWSs As Variant
    Dim fp As String, fn As String

    On Error GoTo bm_Safe_Exit
    'Application.ScreenUpdating = False   'stop screen flashing
    Application.DisplayAlerts = False    'stop confirmation alerts

    vWSs = Array("Sheet1", "Sheet3", "Sheet5")

    'start with a reference to ThisWorkbook
    With ThisWorkbook
        fp = .Path
        'cycle through each of the worksheets
        For v = LBound(vWSs) To UBound(vWSs)
            With Worksheets(vWSs(v))
                .Copy
                'the ActiveWorkbook is now the new workbook populated with a copy of the current worksheet
                With ActiveWorkbook
                    fn = .Worksheets(1).Name
                    .SaveAs Filename:=fp & Chr(92) & fn, FileFormat:=xlCSV
                    .Close savechanges:=False   '<~~ already saved in line above
                End With
            End With
        Next v
    End With

bm_Safe_Exit:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

Upvotes: 1

Related Questions