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
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.
ReplyDeleteThis blog is for programmers who can read code, see the pattern and amend the code for their purposes.
ReplyDeleteThank you! Your code help me a lot. I've just started learning VBA for a few months. Your blog may keep me inspired.
ReplyDelete