Using Excel column data to create sql statement that queries database

Question

I am looking for any advice on how i can read a single column in excel that contains 500 user_id's and query a database to display results in a WPF application. A user can own or rent so the SQL would look like;

SELECT * FROM users WHERE own= 'user_id' or rent= 'user_id'

This is fine for one user but i want to read each user_id and concatenate it to the SQL statement to pull out all results from the database. Any one have any easy way of doing this?

What exactyly is your question? How to read values from an excel table using vba? How to construct an sql statement from VBA? How to constructs a single sql stetement that queries all users in the excel table in one go? How to connect to mysql from VBA to get the data? Additionally, I do not understand how WPF and VBA are used in conjunction with each other. — Shadow
– Shadow, Commented Apr 7, 2016 at 10:05
My main issue is being able to read the data and get it into an array using VBA — Aaron C
– Aaron C, Commented Apr 7, 2016 at 10:07

Community · Accepted Answer · 2017-05-23 10:28:14Z

1

Replace the range as necessary, credit to brettdj on the join - Simple VBA array join not working

Sub test()

    Dim strQuery As String
    Dim strVals As String

    Dim rngTarget As Range
    Set rntTarget = Range("A1:A7")

    Dim varArr
    Dim lngRow As Long
    Dim myArray()
    varArr = rntTarget.Value2

    ReDim myArray(1 To UBound(varArr, 1))

    For lngRow = 1 To UBound(varArr, 1)
        myArray(lngRow) = varArr(lngRow, 1)
    Next

    strVals = "('" & Join$(myArray, "','") & "') "

    strQuery = "SELECT * FROM users WHERE own in " _
        & strVals & "or rent in " & strVals


End Sub

edited May 23, 2017 at 10:28

CommunityBot

11 silver badge

answered Apr 7, 2016 at 10:06

user6028892

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

2 Comments

Aaron C Over a year ago

thanks, if im doing this in a WPF application do i have to open the excel sheet that i want to read data from?

user6028892 Over a year ago

No experience with WPF but replacing = Range("A1:A7") above to something like = workbooks(1).worksheets("mysheets").Range("A1:A7") is a good shout if the worksheet is not open

Collectives™ on Stack Overflow

Using Excel column data to create sql statement that queries database

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related