Excel: Copy as CSV

Ever need to export cells to a CSV file? Tired of copy/pasting the cells you need into a new, blank sheet and exporting it as a CSV? Then being told you might lose some compatibility features, then needing to save-as a proper XLSX file again so you don’t lose sheets when saving again? Try this!

I assume you have Windows 10 and a modern version of Excel.

Right click on the Ribbon (toolbar) and select “Customize the Ribbon…”

In the right-hand list, find Developer and make sure it’s checked. Click OK.

In the now visible Developer tab in the Ribbon, open Visual Basic, the first icon you see.

You should now see the Microsoft Visual Basic for Applications window appear.  You should also see a VBAProject (PERSONAL.XLSB) file loaded in the left-hand side of the editor.

If you don’t, switch back to Excel and create a new, blank Excel file.  Make no changes and save it as


Make sure the file type is “Excel Binary Workbook (*.xlsb)” and the filename is ALL CAPS.

Now your editor should look similar to the following.

If your file does not have a Module1 file, right click on the VBAProject item and choose Insert->Module

Double-Click Module1 to open it in the right-hand editor.

A new blank editor will look like the following:

You might already have some code in the editor. If so, just paste the below code at the end of your existing file, or create a new Module to keep things separated.

Public Sub CopyAsCSV()

Dim oDO As DataObject
Dim rArea As Range
Dim i As Long
Dim j As Long
Dim nCount As Long
Dim sTxt As String

Dim useQuotes As Boolean
useQuotes = True

If TypeOf Selection Is Range Then
    With Selection
        For Each rArea In .Areas
            With rArea
                If useQuotes Then
                    For i = 1 To .Rows.Count
                        For j = 1 To .Columns.Count
                            If j = 1 Then
                                sTxt = sTxt + Chr(34) + .Cells(i, j).Text + Chr(34)
                                sTxt = sTxt + "," + Chr(34) + .Cells(i, j).Text + Chr(34)
                            End If
                            nCount = nCount + 1
                        Next j
                        sTxt = sTxt + vbCrLf
                    Next i
                    For i = 1 To .Rows.Count
                        For j = 1 To .Columns.Count
                            If j = 1 Then
                                sTxt = sTxt + .Cells(i, j).Text
                                sTxt = sTxt + "," + .Cells(i, j).Text
                            End If
                            nCount = nCount + 1
                        Next j
                        sTxt = sTxt + vbCrLf
                    Next i
                End If
            End With
        Next rArea
    End With
    Set oDO = New DataObject
    oDO.SetText sTxt
End If
End Sub

While you’re still here in the VBA editor, let’s make sure the MSForms 2.0 library is loaded. It wasn’t by default on my system and is required for this to work. Goto Tools>References… in the VBA Editor.

Click Browse… and then in the Add Reference dialog, open the file C:\Windows\System32\FM20.DLL . It should already be installed on your system.

You should now see the Microsoft Forms 2.0 Object Library in the list. Make sure it’s checked and click OK.

Save and Close the VBA Editor.

Get back into the Customize the Ribbon dialog.

Choose Macros from the “Choose commands from” list.


Then, browse in the right-hand list to where you want to place the new ribbon/toolbar command. I keep my macros under the Developer tab so as to not mess up any of the other default tabs. You can also create new tabs, groups, etc. by right-clicking in the right-hand list.

Click the Add >> button between the two lists.

Give the new button a more useful name and a pretty icon by selecting it in the right-hand list and clicking the Rename… button below the list.

Click OK to exit the Rename dialog, and then OK again to exit the Customize the Ribbon dialog. Your new button should now be visible in the location you chose.

Give it a shot!

Also, when did “Search with Bing…” get added to Notepad…