Tuesday 27 August 2019

VBA - Export a worksheet to CSV

I just had a need to export a worksheet to CSV (comma separated value) file and was surprised that one must create a new workbook and copy over the sheet. I would have thought Microsoft would have written a nice 'ExportToCSV' method by now. Anyway the code samples of StackOVerflow did not satisfy me so I wrote my own, here is my code.

Option Explicit

Sub TestExportSheetToCsv()
    Dim sDir As String
    sDir = "C:\Users\Simon\source\repos\PythonBaseHttpPlusQueryParams\PythonBaseHttpPlusQueryParams\"
    
    ExportSheetToCsv ThisWorkbook, sDir, "DomainToBrokerMap"
    ExportSheetToCsv ThisWorkbook, sDir, "BrokerQuerySelectors"
    ExportSheetToCsv ThisWorkbook, sDir, "MarketDataItems"
    ExportSheetToCsv ThisWorkbook, sDir, "BrokerDrilldown"
    
End Sub

Sub ExportSheetToCsv(ByVal wbSrc As Excel.Workbook, ByVal sDir As String, ByVal sSheetName As String)
    Application.ScreenUpdating = False
    Dim wbExport As Excel.Workbook
    Set wbExport = Workbooks.Add

    Dim wsExport As Excel.Worksheet
    Set wsExport = ThisWorkbook.Worksheets.Item(sSheetName)
    wsExport.Copy , wbExport.Worksheets.Item(1)

    Application.DisplayAlerts = False
    wbExport.Worksheets.Item(1).Delete
    Application.DisplayAlerts = True
    
    Dim sExportFileName As String
    sExportFileName = sDir & sSheetName & ".csv"
    
    If Len(Dir(sExportFileName)) > 0 Then
        Kill sExportFileName
        Debug.Assert Len(Dir(sExportFileName)) = 0
    End If
    
    wbExport.SaveAs sExportFileName, FileFormat:=xlCSV, CreateBackup:=False
    
    Debug.Print "Exported " & sExportFileName
    
    wbExport.Close False
    Application.ScreenUpdating = True
End Sub


3 comments:

  1. Found this post from comment on https://stackoverflow.com/questions/37037934/excel-macro-to-export-worksheet-as-csv-file-without-leaving-my-current-excel-sh. When I put this code in a module only the `TestExportSheetToCsv` macro shows up. When run it yields "subscript out of range". Debug says line `Set wsExport = ThisWorkbook.Worksheets.Item(sSheetName)` is where error occurs. Not surprising I guess because the hardcoded sDir path doesn't exist on my machine. Deleting test macro Sub doesn't help.

    ReplyDelete
  2. This blog is for programmers who can read code, see the pattern and amend the code for their purposes.

    ReplyDelete
  3. Thank you! Your code help me a lot. I've just started learning VBA for a few months. Your blog may keep me inspired.

    ReplyDelete