Delimited List with Quotation Marks for SQL query

Question

I have a cheat sheet I use to for turning a list of orders in a comma delimited list. I've been asked to duplicate it, but instead of simply returning the values from the list, I've been asked to place them in quotation marks for a SQL query a coworker. So a list like: A B C Should be returned as "A","B","C". Below is the code I have for creating the CSV list.

Function csvRange(myRange As Range)
Dim csvRangeOutput
Dim entry As Variant
For Each entry In myRange
    If Not IsEmpty(entry.Value) Then
        csvRangeOutput = csvRangeOutput & entry.Value & ","
    End If
Next
csvRange = Left(csvRangeOutput, Len(csvRangeOutput) - 1)
End Function

I'm totally new to VBA, but I tried playing with the code to figure it out myself. Adding more quotes, using the tilde symbol wasn't working. That basically exhausted my toolkit. Thanks for any help.

You should be more explicit about where it's going wrong. We need more to go on than "Wasn't working". — Edward Peters, Jul 26 '16 at 16:23

score 0 · Answer 1 · answered Jul 26 '16 at 16:24

You can use chr(34) as a double quote. Alternatively you can do a double, double quote ("") which is interpreted as a single double quote when done in a VBA string:

csvRangeOutput = csvRangeOutput & chr(34) & entry.Value & chr(34) & ","

or

csvRangeOutput = csvRangeOutput & """" & entry.Value & """" & ","

score 0 · Accepted Answer · answered Jul 26 '16 at 16:28

0

Try...

If Not IsEmpty(entry.Value) Then
    csvRangeOutput = csvRangeOutput & """" & entry.Value & """" & ","
End If

answered Jul 26 '16 at 16:28

xelvis42

234
2
4

Thanks xelvis! This returned the value exactly. I ultimately fell on old tricks and made a macro based on a google search for how to place a value into quotation marks, then linked it to a button for my coworker. I wish I had looked up the rule for applying 4 quotation marks on each side earlier. – bryann Jul 26 '16 at 16:46

Delimited List with Quotation Marks for SQL query

2 Answers2