CODEWITHSUNDEEP
vba
erasmo carlos
erasmo carlos

Reputation: 682

VB: create comma separated string from array

I am trying to convert a list of array values to a comma separated string, with single quotes.

my array name is: 

info.arr_fonts(
               (0)times
               (1)verdana
               (2)arial
               (3)tahoma
               (4)helvetica
              ) - values are dynamic, can be +/-.
enter code here

I would like to write them into a string variable like:

"times, verdana, arial, tahoma, helvetica"

can someone show me how this can be done? I tried something simple like: 

Dim strFonts As String
strFonts = Join(info.FontArray, ",")

But that does not add the single quotes around each word.

UPDATE

Dim arrFontNames As Variant
Dim strFonts As String
Dim lCtr As Long

arrFontNames = Array("Doremi", "Times-Roman", "Helvetica", "Jivetalk", "Jive")

strFonts = Join(arrFontNames, ",")

content of strFonts: 

"Doremi,Times-Roman,Helvetica,Jivetalk,Jive"

I need to pass the strFonts as a parameter to a stored procedure. The stored procedure needs to receive it lie this: 

'Doremi,Times-Roman,Helvetica,Jivetalk,Jive'

Will the double quotes from VBA convert to single quotes once it executes the stored procedure, or do I need to do some string manipulation still?

Upvotes: 1

Views: 12529

Answers (1)

cyboashu
cyboashu

Reputation: 10443

add the quotes by loop.

Sub test()

    Dim lCtr As Long

    For lCtr = LBound(info.FontArray) To UBound(info.FontArray)
        info.FontArray(lCtr) = "'" & info.FontArray(lCtr) & "'"
    Next

    strFonts = Join(info.FontArray, ",")

End Sub

As per your edit to the question. for a single param in stored proc all you need to do is strFonts = "'" & Join(info.FontArray, ",") & "'"

Upvotes: 2

Related Questions