Hi,
I have a code below which I used select statement to query data from the database, the "WHERE" clause will obtain the range of input from the sheet in column M. When I run this code, there's no error, however, there was no result display either. From trawling in the net, some of the result mentioned that I have to concatenate the input in the column, which I did. But still no result display.
Code
- Sub getdata()
- Dim Item, sql As String
- Dim i, LR As Long
- ilastrow = Sheet1.Cells(Rows.Count, "M").End(xlUp).Row
- job = ""
- For i = 2 To LR
- job = job & Sheet1.Range("M" & i).Value & ","
- Next
- job = Left(job, Len(job) - 1)
- Set cn = CreateObject("ADODB.Connection")
- cnstrg = "Provider=SQLOLEDB; Server=""; Database=""; Integrated Security=;"
- cn.Open cmstrg
- sql= "SELECT JOB FROM HOUSE WHERE NAME ='" & job & "'"
- Set rs = CreateObject("ADODB.Recordset")
- With rs
- .ActiveConnection = cn
- .Open sql
- Sheet1.Range("S2").CopyFromRecordset rs
- .Close
- End With
- cn.Close
- Set rs = Nothing
- job = ""
- End Sub