VBA - Multiline Textbox to SQL IN statement

Question

As the title states, I have a multiline textbox that I need to copy/paste multiple values into ( that'd exist on each line ) and have them turn into an array, which would build an SQL In statement. I'm already doing this with a listbox that works perfect, but can't figure out doing it with a textbox

My textbox is named form_payment_inv with multiline enabled.

Example of a copy/paste

1234
5678

Desired output would be

IN ('1234','5678')

zedfoxus · Accepted Answer · 2015-10-09 20:32:36Z

1

You could do something like this:

Private Sub CommandButton1_Click()
    ' Split the text box's multiple lines by \r\n
    Dim myarr() As String
    myarr() = Split(TextBox1.Text, vbCrLf)

    ' Find total items contained in the array and put single quotes around them
    Dim TotalItems As Integer
    TotalItems = UBound(myarr)        
    Dim I As Integer
    For I = 0 To TotalItems
        myarr(I) = "'" & myarr(I) & "'"
    Next I

    ' create the IN statement
    Dim SQL_IN As String
    SQL_IN = Join(myarr, ",")
    MsgBox SQL_IN
End Sub

The result will be '1234','5678'

answered Oct 9, 2015 at 20:32

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeff Beagley Over a year ago

I'm getting "Expected Array" on TotalItems = UBound(myarr)

Collectives™ on Stack Overflow

VBA - Multiline Textbox to SQL IN statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related