Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Using VBA to parse delimited values from a single cell and store in string variable?

    I have a cell (B2) that may contain 1 or more values separated by a comma (example: 1,2,3). I am storing the value of this cell in a VBA string variable (myVar) and passing to a SQL statement:

    Code:
    SELECT * FROM myTable WHERE mailing IN(myVar)
    mailing is a string value in the DB so in order for the IN() to work, each comma separated value must be wrapped in tick marks and store into a new variable ('1','2','3'). I dont need to populate the cell with the transformed results, just store in the variable that is passed to the SQL statement.

    i've tried using SPLIT() but that keeps giving me an error, likely b/c of it returning an array.

  2. #2
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Talking

    ah, figured it out!

    Code:
    Dim MyString As String
    Dim MyString2 As String
    Dim MyValue As String
    Dim finalValue As String
    
    MyString = "1,2,3"
        
    Dim C1 As Integer
    Dim C2 As Integer
        
    MyString2 = MyString & ","
    C2 = InStr(C1 + 1, MyString2, ",")
    While C2 > 0
       MyValue = Mid(MyString2, C1 + 1, C2 - C1 - 1)
       finalValue = finalValue + "'" + MyValue + "',"
       C1 = C2
       C2 = InStr(C1 + 1, MyString2, ",")
    Wend
    
    finalValue = Left(finalValue, Len(finalValue) - 1)
    MsgBox (finalValue)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •