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

gen_bidv

New Member
Joined
Feb 22, 2019
Messages
3
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.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
How many unique values do you have for CIFNO
 
Upvote 0
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.

Code:
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
 
Upvote 0
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top