How to take multiple inputs in SQL from an excel sheet

I am trying to find a way to take multiple customer names from an excel sheet to be used in WHERE clause so that I can track the order for those customers! eg:

Customer_Name | Quantity | Total purchase price

ABC | 100 | 500

XYZ | 200 | 1000

DEF | 300 | 1500

Now, I am using an excel file, from which I am getting data from SQL Server and writing a SQL query to filter out the data I need(original data is very big to load). Now I want to take multiple customer name from a col which is in a different worksheet in the same workbook in excel, which can change depending upon the customers name entered by the user!

SELECT Customer_Name, Quantity, Total Purchase Price 
FROM TABLE1
WHERE Customer_Name='ABC'
OR Customer_Name='XYZ'
OR (and so on);

It works for each Customer when I put it manually but I want it to be dynamic. If someone puts 1000 customer names for example, it takes the input from those columns in the query. I know a bit of VBA(have worked on small projects), so if I can do it easily from a simple VBA code, I am all ears!

Thank you, I am new, please let me know if I can explain it better.

Best regards

Solution

I recommend building a comma separated string with the customer names and using a WHERE IN ().

The following code assumes the customer names are in column A. Furthermore a column header is assumed. Start the For loop at i = 1 to include the first row.

Sub GetCustomerNames()
    Dim lastRow As Long
    Dim i As Long
    Dim s As String
    Dim sSql As String
    
    lastRow = Range("A1").End(xlDown).Row
    
    For i = 2 To lastRow
        s = s & "'" & Cells(i, "A") & "',"
    Next i
    If Len(s) > 0 Then s = Left$(s, Len(s) - 1) 'remove last '
    
    sSql = "SELECT Customer_Name, Quantity, Total_Purchase_Price FROM TABLE1 WHERE Customer_Name IN (" & s & ")"
    
    Debug.Print sSql
End Sub

Output:

SELECT Customer_Name, Quantity, Total_Purchase_Price FROM TABLE1 WHERE Customer_Name IN ('ABC','XYZ','DEF')