-1

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?

Aaron C
  • 135
  • 3
  • 12
  • 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 Apr 07 '16 at 10:05
  • My main issue is being able to read the data and get it into an array using VBA – Aaron C Apr 07 '16 at 10:07

1 Answers1

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
Community
  • 1
  • 1
  • thanks, if im doing this in a WPF application do i have to open the excel sheet that i want to read data from? – Aaron C Apr 07 '16 at 10:09
  • 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 –  Apr 07 '16 at 10:23