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.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,760
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
How many unique values do you have for CIFNO
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,424
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
 

gen_bidv

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

Watch MrExcel Video

Forum statistics

Threads
1,109,027
Messages
5,526,329
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top