How to get data from SQL Server with comparisons value in the Sheet

[gen_bidv]

Dear all,


I have an unresolved problem:
- Currently I have an Excel file (Sheet 1), which has a column called CIFNO (this sheet including many lines - more than tens of thousands of lines).
- Data needs to be retrieved in SQL Server, that table (DDMAST) also has a CIFNO column.

Currently I retrieve the data from SQL Server by making a connection to SQL Server from Excel, in the Connection Property setting the following statement (command text) like this:

Select * from "BRANCHDATA". "Dbo". "DDMAST" where CIFNO in (123,456,789)

However, the worst thing about this is that the parameter in the above statement is now "static" and as such, to get enough data, I have to copy all CIFNO in Sheet1 again and past it in to command text (it is very complex because Sheet 1 has more than tens of thousands of lines).
So is there a way to put the value of CIFNO column in Sheet 1 into the reference of the other statement, or can you write VBA function to handle this?

Please tell me if you have solutions for this,


Sincerely thank.

 

[jimrward]

How many unique values do you have for CIFNO

 

[gen_bidv]

How many unique values do you have for CIFNO

Hi Jim, I have plenty values of CIFNO (each row have one CIFNO value),

Tks,

 

[John_w]

Try this macro, which updates the IN (x,y,...) list with CIFNO values and refreshes the SQL query. It assumes the CIFNO values are in column A, starting at A2, on Sheet1, and the SQL query (connection) is the first connection on Sheet2.

Public Sub Update_SQL_IN_List_and_Refresh_Query()

    Dim CIFNOs As String
    Dim qt As QueryTable
    Dim p1 As Long, p2 As Long
    
    With Worksheets("Sheet1")
        CIFNOs = Join(Application.Transpose(.Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Value), ",")
    End With
    
    Set qt = Worksheets("Sheet2").ListObjects(1).QueryTable
    
    p1 = InStr(1, qt.CommandText, " IN (", vbTextCompare)
    If p1 > 0 Then
        p1 = p1 + Len(" IN (") - 1
        p2 = InStr(p1, qt.CommandText, ")")
        qt.CommandText = Left(qt.CommandText, p1) & CIFNOs & Mid(qt.CommandText, p2)
        qt.Refresh
    End If
    
End Sub

 

[gen_bidv]

Try this macro, which updates the IN (x,y,...) list with CIFNO values and refreshes the SQL query. It assumes the CIFNO values are in column A, starting at A2, on Sheet1, and the SQL query (connection) is the first connection on Sheet2.

Public Sub Update_SQL_IN_List_and_Refresh_Query()

    Dim CIFNOs As String
    Dim qt As QueryTable
    Dim p1 As Long, p2 As Long
    
    With Worksheets("Sheet1")
        CIFNOs = Join(Application.Transpose(.Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Value), ",")
    End With
    
    Set qt = Worksheets("Sheet2").ListObjects(1).QueryTable
    
    p1 = InStr(1, qt.CommandText, " IN (", vbTextCompare)
    If p1 > 0 Then
        p1 = p1 + Len(" IN (") - 1
        p2 = InStr(p1, qt.CommandText, ")")
        qt.CommandText = Left(qt.CommandText, p1) & CIFNOs & Mid(qt.CommandText, p2)
        qt.Refresh
    End If
    
End Sub

Thank you - John, I'll try your solution and hope that can solve my problem,

Rgds
Gen_bidv