maphew

Excel Export CSV without changing XLS

June 11, 2024
12:37 PM

How to export from Excel with click of a button and not lose formulas and additional content or change the active file name and type.

Excel pops up a series of dialogs with every CSV save, which is a major impediment to workflow speed. Furthermore can easily lead to lost data, formulas & other features as non-csv attributes are stripped out. It's worth solving this problem.

Tested and working with

Microsoft® Excel® for Microsoft 365 MSO (Version 2405 Build 16.0.17628.20006) 64-bit

Download addin: Export_to_csv_in_one.xlam

Install the Add-In

  1. Open Excel.

  2. Go to File > Options.

  3. In the Excel Options dialog, select Add-Ins from the left pane.

  4. At the bottom of the dialog, in the Manage dropdown, select Excel Add-ins and click Go....

  5. In the Add-Ins dialog, click Browse....

    1. select Export_to_csv_in_one.xlam file, and click OK.

  6. Ensure the add-in is checked in the list of available add-ins and click OK.

Access the Macro from the Add-In

Once the add-in is installed, the macro will be available for use. You can also create a button in the Quick Access Toolbar (QAT) to run the macro easily.

Creating a Button in the Quick Access Toolbar

  1. Go to File > Options.

  2. In the Excel Options dialog, select Quick Access Toolbar from the left pane.

  3. In the "Choose commands from" dropdown, select Macros.

  4. Find the macro (it will be listed under the add-in name) and select it.

  5. Click Add >> to move the macro to the Quick Access Toolbar list on the right.

  6. Optionally, customize the button icon:

    • Select the macro in the right list.

    • Click Modify... and choose an icon.

    • Click OK.

  7. Click OK to close the Excel Options dialog.

Built with ChatGPT 4o assistance.

Sub SaveSheetAsCSV()
    Dim FilePath As String
    Dim SheetName As String
    Dim WorkbookPath As String
    Dim TempFilePath As String
    Dim UTF8FilePath As String
    Dim wb As Workbook

    ' Set the workbook object to the active workbook
    Set wb = ActiveWorkbook

    ' Get the active sheet name
    SheetName = ActiveSheet.Name

    ' Get the path of the active workbook
    If Len(wb.Path) > 0 Then
        WorkbookPath = wb.Path
    Else
        WorkbookPath = Environ("USERPROFILE") & "\Documents"
    End If

    ' Set the file path and name using the sheet name
    FilePath = WorkbookPath & "\" & SheetName & ".csv"

    ' Save the active sheet as a temporary CSV file
    TempFilePath = Environ("TEMP") & "\" & SheetName & ".csv"
    ActiveSheet.Copy
    ActiveWorkbook.SaveAs FileName:=TempFilePath, FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close False

    ' Convert the CSV file to UTF-8 encoding
    UTF8FilePath = FilePath
    ConvertCSVToUTF8 TempFilePath, UTF8FilePath

    ' Delete the temporary CSV file
    Kill TempFilePath

    MsgBox "Exported sheet '" & SheetName & "'" & vbNewLine & vbNewLine & FilePath, vbInformation
End Sub

Sub ConvertCSVToUTF8(ByVal sourceFile As String, ByVal destFile As String)
    Dim fsT As Object, tsT As Object, tsU As Object
    Dim text As String
    Dim line As String
    Dim lines() As String
    Dim i As Integer

    ' Read the content of the original CSV file
    Set fsT = CreateObject("Scripting.FileSystemObject")
    Set tsT = fsT.OpenTextFile(sourceFile, 1, False, -2) ' -2 means system default

    ' Read all text from the source file
    text = tsT.ReadAll
    tsT.Close

    ' Split the text into lines
    lines = Split(text, vbCrLf)

    ' Write the content to a new file with UTF-8 encoding
    Set tsU = fsT.CreateTextFile(destFile, True, False) ' True for overwrite, False for no BOM
    For i = LBound(lines) To UBound(lines)
        ' Ensure that each line ends with CRLF
        line = lines(i)
        If Len(line) > 0 Then
            tsU.Write line & vbCrLf
        End If
    Next i
    tsU.Close
End Sub

─────────────────────────────────────────────────────────────────────

Earlier work

Sub Save_as_CSV()
    ActiveSheet.Copy
    ActiveWorkbook.SaveAs Filename:= "C:\Users\bug_bunny\data.csv" _
        , FileFormat:=xlCSV, CreateBackup:=False
    ActiveWindow.Close
End Sub

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.

From < https://exceldevelopmentplatform.blogspot.com/2019/08/vba-export-worksheet-to-csv.html>

I get Index out of range error on this one. Seems to forget to copy the data first, or something.

Sources

Florian Lindstaedt, James Roberts - https://www.linkedin.com/pulse/20140725234224-54506939-excel-to-csv-export-instead-of-save-as/