maphew

Excel Export CSV without changing XLS

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.

 

trilium-meta: this page features rich text, an attached non-standard file type, link to local file system, and code formatted text block.

 

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

 

Also saved to Z:\Tools\scripts as 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....
  6. Navigate to Z:\Tools\scripts, select Export_to_csv_in_one.xlam file, and click OK.
  7. 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.

 

Code

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/

 

Created with OneNote.