Every once in a while I am provided data from client in Excel and want to use that information in SQL (SSMS, MySQL, …) to build some WHERE clause in a query. So I go back and forth, copy/paste, over and over. It’s tedious and time consuming!

There has to be a better approach.

Now, if you’re running Excel 365 then there’s a new function you can actually employ TextJoin(), but I’m not going to be discussing that here.

I wanted, needed, a universal solution for when on-site at clients, some of whom are running Excel 2003.

The solution is a very simple VBA Function that simply loops through the rows of a specified column and concatenates the values into a CSV (comma separated value) listing and so, the following was created:

'---------------------------------------------------------------------------------------
' Procedure : MakeCSVListingFromColumnValues
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Generate a CSV from the specified column of data in Microsoft Excel
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sColumnLetter : Column to create a CSV listing from
' sDelim        : Delimiter to use around the values
' lStartRowNo   : Starting row of data
'
' Usage:
' ~~~~~~
' ? MakeCSVListingFromColumnValues("C")
'   Returns ->
'
' ? MakeCSVListingFromColumnValues("C", "'")
'   Returns ->
'
' ? MakeCSVListingFromColumnValues("C", "'", 2)
'   Returns ->
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2024-10-29              Initial Public Release
'---------------------------------------------------------------------------------------
Public Function MakeCSVListingFromColumnValues(sColumnLetter As String, _
                                               Optional sDelim As String = "", _
                                               Optional lStartRowNo As Long = 1) As String
    On Error GoTo Error_Handler
    Dim lLastRow              As Long
    Dim i                     As Long
    Dim sOutput               As String

    lLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, Columns(sColumnLetter).Column).End(xlUp).Row

    For i = lStartRowNo To lLastRow
        sOutput = sOutput & sDelim & Range(sColumnLetter & i).Value & sDelim
        If i < lLastRow Then sOutput = sOutput & ", "
    Next
    MakeCSVListingFromColumnValues = sOutput

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: MakeCSVListingFromColumnValues" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

So for numeric values, I’d simply do:

? MakeCSVListingFromColumnValues("C")

but for textual/string values I’d do:

? MakeCSVListingFromColumnValues("C", "'")

Retaking the previous case, if the 1st 2 rows were a heading and the values only started on the 3rd row, then I’d do:

? MakeCSVListingFromColumnValues("C", "'", 3)

in all cases, I would then be able to copy/paste in one go the entire CSV from the VBA/VBE Immediate Window.
 

Formal Example(s)

Given the Excel column

We could extract a list by doing:

? MakeCSVListingFromColumnValues("A", "'")

which would output:

'Name', 'Alec', 'Pearl', 'Palmer', 'Noelani', 'Ila', 'Clarke', 'Dorothy', 'Ferris', 'Jenette', 'Regan', 'Ivy', 'Lysandra'

Now, to omit the 1 row which is a column header, we’d simply do:

? MakeCSVListingFromColumnValues("A", "'", 2)

which would output:

'Alec', 'Pearl', 'Palmer', 'Noelani', 'Ila', 'Clarke', 'Dorothy', 'Ferris', 'Jenette', 'Regan', 'Ivy', 'Lysandra'

and if we didn’t want the values surrounded by single quotes, then we’d simply do:

? MakeCSVListingFromColumnValues("A", , 2)

which would output:

Alec, Pearl, Palmer, Noelani, Ila, Clarke, Dorothy, Ferris, Jenette, Regan, Ivy, Lysandra

 

Think Add-in

This is a great little block of code to add to an add-in so such functionality is only a click of a button away. At least, that’s what I’ve done!