Exporting to CSV

Question

I want to export to CSV and not "Save As", so create a button which will be enabled by a macro. When clicked, it should create a .csv file of the first sheet in a specified directory and also specified name. And my original worksheet should be preserved and not Saved As.

@Davesexcel OP is only talking about one sheet and hasn't really given any reason for wanting to reinvent this particular wheel. — John Coleman, Nov 28 '15 at 11:21
That's right , so there is no reason not to use saveas.......... — Davesexcel, Nov 28 '15 at 11:28
Maybe this question will help: http://stackoverflow.com/q/21568837/4996248 — John Coleman, Nov 28 '15 at 12:58
@JohnColeman, Save as will make the current file to be saved as that, which will result in problems while saving the sheet as *xlsm*. — aneeshjajodia, Nov 29 '15 at 12:00

score 3 · Answer 1 · 2015-11-29T16:49:31.120

3

If the point is keeping the original workbook untouched then why not get creative. We can copy the sheet to another workbook and the save as .csv

Option Explicit

Sub ExportOneSheet()

    Const strFILE_NAME As String = "C:\Users\Tom\Desktop\tes.csv"

    Dim shToExport As Worksheet

    ' Set the sheet to copy
    Set shToExport = ActiveWorkbook.Sheets("Sheet1")

    ' Make a copy of the sheet, when called without argument
    ' it will create a new workbook
    shToExport.Copy
    Set shToExport = ActiveWorkbook.Sheets("Sheet1")

    ' If the file exists the delete it. This will esure that
    ' there is no previous file so the replace file thing will not show
    If Not Dir$(strFILE_NAME, vbNormal) = vbNullString Then
        Kill strFILE_NAME
    End If

    ' Use Save As and your original workbook stays untouched.
    shToExport.SaveAs strFILE_NAME, XlFileFormat.xlCSV
    shToExport.Parent.Close True

End Sub

I hope this helps :)

edited Nov 29 '15 at 16:49

answered Nov 28 '15 at 13:56

Let me try this, will get back to you if this works. – aneeshjajodia Nov 29 '15 at 12:01
Well, this works perfectly bro, as intended. **Thanks a lot**. Would be even more glad if there was a way to skip the *Yes* or *No* buttons for **Replacement of the existing** file. There might be a way through, right? – aneeshjajodia Nov 29 '15 at 12:30
I modified the Answer to delete the file is there is one already. So now you will not be prompted to replace the file. – Nov 29 '15 at 16:50
Some error in this part **If Not Dir$(strFILE_NAME, vbNormal) = vbNullString Then** – aneeshjajodia Nov 29 '15 at 18:16
note that the full path to the file is now in this constant strFILE_NAME make sure to change it to your computer file path. – Nov 29 '15 at 18:36

Gary's Student · Answer 2 · 2015-11-28T13:34:51.727

If you do not have commas embedded in cells, this may be enough:

Sub CSV_Maker()
   Dim r As Range
   Dim sOut As String, k As Long, M As Long
   Dim N As Long, nFirstRow As Long, nLastRow As Long

   Sheets(1).Select

   ActiveSheet.UsedRange
   Set r = ActiveSheet.UsedRange
   nLastRow = r.Rows.Count + r.Row - 1
   nFirstRow = r.Row
   Dim separator As String
   separator = ","

   MyFilePath = "C:\TestFolder\"
   MyFileName = "whatever"
   Set fs = CreateObject("Scripting.FileSystemObject")
   Set a = fs.CreateTextFile(MyFilePath & MyFileName & ".csv", True)

   For N = nFirstRow To nLastRow
       k = Application.WorksheetFunction.CountA(Cells(N, 1).EntireRow)
       sOut = ""
       If k = 0 Then
           sOut = vbCrLf
       Else
           M = Cells(N, Columns.Count).End(xlToLeft).Column
           For mm = 1 To M
               sOut = sOut & Cells(N, mm).Value & separator
           Next mm
           sOut = Left(sOut, Len(sOut) - 1)
       End If
       a.writeline (sOut)
   Next

   a.Close
End Sub

This helps mate. But the one mentioned by **Fred** above, is damn simpler. — aneeshjajodia, Nov 29 '15 at 12:32

Exporting to CSV

2 Answers2