I am trying to use one query but with multiple paremeters depending on the cell. For example I have a list of dates in Column A and I want to run the query for each dates shown in column B, so the paramter for B1 is A1, B2 is A2 etc.. How do I do this in Excel?
I suppose you could have a bunch of different queries – one for each cell. I think that would be hard to maintain and probably lead to a huge Excel file. I would use a helper sheet that stored one query and use formula to bring over the data I need.
First, create a query on a sheet that contains all the records for the table you’re interested in. For this example, I’m using the Invoices table from Northwind.mdb (actually mine is called Nwind.mdb, but I don’t know what the difference is). Place the query in A3, or if you already have it in A1, insert a couple of rows above the query. We’ll need them later. If you need help creating this query, see Querying External Data in Excel.
Next, create a new sheet and put your dates in column A. Back on the query sheet, put some formulas in B1 and B2.
These will serve as the parameters for this query. It will limit the rows that are brought in, but there will still be more rows than you need. I needed the TEXT function because the ODBC driver couldn’t understand Excel’s dates.
Now I’ll make the query a parameter query. I do this in VBA, but you can use Query Manager, Query Editor, or you can edit the SQL statement directly in MSQuery. In the Immediate Window of the VBE, I type ?activecell.QueryTable.CommandText (with a cell in the query selected, or course), and see that the SQL statement is:
To add the parameters, I type this into the Immediate Window:
In the end, the SQL statement should be:
Now when I refresh the query table, Excel will prompt me for parameter values. Just type something that looks like a date and don’t worry about what it returns. If all has gone well, the Parameters button on the external data toolbar will now be enabled. Click it to open the Parameters dialog.
I change Parameter1 to point to cell B1 and Parameter2 to point to cell B2. Now the query is set to return a limited number of rows, but it will contain all the rows you need.
The last step is make the formula on the non-query sheet. With dates in column A, I put this formula in column B to return the first CustomerID for that date.
and I get this:
Dick,
I think the challenge is to get a Range of Cells as input Parameters
Eg. A1:A10 contain Customer ID 1001,2004,1959,3489 etc
How do you get the value from Cells rather than Cell
The only way I think it is possible is through code..of course you cant have a huge range of cells…beyond certain point you get “Query too complex” error
Option Explicit
Sub QSC_PARM()
‘Queries the Customer Master
Dim Ar As String, Ar1 As String, Ar2 As String, Ar3 As String
Dim SorSht As String, QryNm As String
Dim DestSht As Worksheet
Dim i As Long
SorSht = “Sheet1$”
Set DestSht = Sheet2
QryNm = “QRY_PARM”
Ar1 = “ODBC;DSN=Excel Files;DBQ=” & Range(“VBA_FPN”).Value & “;DefaultDir=” & Range(“VBA_FP”).Value & “;DriverId=790;MaxBufferSize=2048;PageTimeout=5;”
‘ VBA_FPN and VBA_FP – Names for File Paths
Ar = “SELECT *”
Ar2 = Ar & “” & Chr(10) & “FROM ‘” & Range(“VBA_FPN1?).Value & “‘.'” & SorSht & “‘ ‘” & SorSht & “‘” & Chr(10) & Chr(13) & “WHERE (‘” & SorSht & “‘.ID=” & Range(“ID”)(1).Value & “)”
For i = 2 To Range(“ID”).Count
Ar2 = Ar2 & ” OR (‘” & SorSht & “‘.ID=” & Range(“ID”)(i).Value & “)”
Next i
With DestSht.QueryTables(QryNm)
Ar3 = Replace(Ar1, “ODBC”, “OLEDB”, 1, 1)
.Connection = Ar3
.CommandText = Ar2
.Connection = Ar1
.CommandText = Ar2
End With
DestSht.QueryTables(QryNm).Refresh BackgroundQuery:=False
End Sub
Shame we don’t have SQL.Request any longer.
Sam: You might want to use the “In” operator to concatenate rather than string ORs together.
Sam,
Application.Volatile (True)
Dim strGroupNumberString As String
Dim rngGroupsInList As Range, rngCurrentID As Range
Dim intGroupCount As Integer, intCounter As Integer
intCounter = 0
If rngFirstGroup.Value = “” Then
InListSqlString = “”
Exit Function
End If
If Not (IsEmpty(rngFirstGroup.Offset(1, 0))) Then
Set rngGroupsInList = Range(rngFirstGroup, rngFirstGroup.End(xlDown))
intGroupCount = rngGroupsInList.Rows.Count
For Each rngCurrentID In rngGroupsInList
strGroupNumberString = strGroupNumberString & “‘” & rngCurrentID.Value & “‘”
intCounter = intCounter + 1
If intGroupCount intCounter Then strGroupNumberString = strGroupNumberString & “, “
Next rngCurrentID
InListSqlString = strGroupNumberString
Else
InListSqlString = “‘” & rngFirstGroup.Value & “‘”
End If
End Function
I use this to generate a string from a vertical range which are user inputs. I’ve got notes to the users about the need fot the range to be contiguous. I insert it in the SQL string I build as the argument for an “In” in the WHERE clause. It’s not elegant, I’m sure, but it works well.
Brett
Dick, Brett..
Thanks for those suggestions.
I just modified the code…changed WHERE/OR to WHERE IN and used a String to Array Function….ran a query of about 5000 Customer ID’s typed in a range of cells… works great… thanks for this post..
Sub QSC_PARM()
‘Queries the Customer Master
Dim Ar As String, Ar1 As String, Ar2 As String, Ar3 As String
Dim SorSht As String, QryNm As String
Dim DestSht As Worksheet
Dim i As Long
SorSht = “Sheet1$”
Set DestSht = Sheet2
QryNm = “QRY_PARM”
ReDim RngArr(1 To Range(“ID”).Count)
Ar1 = “ODBC;DSN=Excel Files;DBQ=” & Range(“VBA_FPN”).Value & “;DefaultDir=” & Range(“VBA_FP”).Value & “;DriverId=790;MaxBufferSize=2048;PageTimeout=5;”
Ar = “SELECT *”
Ar2 = Ar & “” & Chr(10) & “FROM ‘” & Range(“VBA_FPN1”).Value & “‘.'” & SorSht & “‘ ‘” & SorSht & “‘” & Chr(10) & Chr(13) & “WHERE ‘” & SorSht & “‘.ID IN (“
Ar3 = Range(“ID”)(1).Value
For i = 2 To Range(“ID”).Count
Ar3 = Ar3 & “, “ & Range(“ID”)(i).Value
Next i
Ar2 = Ar2 & Ar3 & “)”
With DestSht.QueryTables(QryNm)
Ar3 = Replace(Ar1, “ODBC”, “OLEDB”, 1, 1)
.Connection = Ar3
.CommandText = StringToArray(Ar2)
.Connection = Ar1
.CommandText = StringToArray(Ar2)
End With
DestSht.QueryTables(QryNm).Refresh BackgroundQuery:=False
End Sub
Function StringToArray(Query As Variant) As Variant
Const StrLen = 127
Dim NumElems As Integer
Dim Temp() As String
Dim i As Long
NumElems = (Len(Query) / StrLen) + 1
ReDim Temp(1 To NumElems) As String
For i = 1 To NumElems
Temp(i) = Mid(Query, ((i – 1) * StrLen) + 1, StrLen)
Next i
StringToArray = Temp
End Function
Thanks for the concise info on how to get parameters from cells into table queries of external data in Excel. I lucked out, your post came up first on Google!
There is a trick for doing a SQL query for a fixed range of products/customers from inside Excel without programming:
I will show this for a sql server query. I will omit the standard steps from this post, but i will show the specifics.
Step 1:
Use a fixed range/list to put all customer id’s. For e.g. A1-A10:
CUST1
CUST2
CUST3
….
CUST10
Step2
Create a formula in adjectant cell B1:
=”|”& A1 & “|” (equals “|CUST1|”)
Create a formula in B2
=B1 & A2 & “|” (equals “|CUST1|CUST2|”)
Fill the formula in B2 down for all the customers (till A10)
Create a Query (maybe on another sheet):
SELECT * FROM CUSTOMERS WHERE ? LIKE ‘%|’ + CUSTOMER_ID + ‘|%’
Refresh the query and select as the parameter the cell A10
You should only see a list of customers that you entered in A1-A10.
Note the following
For SQL server the limit is 8000 characters long, which is can contain quite a number of customer id’s, but depending on the length of the customer id not hundreds or more (check the length of the text in A10).
This trick also works even if not all customer ids are filled.
Also, this is not a super fast query.
Several variations on this theme can be used with different database platforms and range of parameters.
Oops! As the parameter for the query you should of course select B10
DM Unseen,
I just tried your method…it gave me an error “Too few parameters expected 1? …. could you kindly post a file containing the query….
sam
First, this example is for SQL Server (and your error indicates Access), I have not studied to create this for Access, Although it is certainly possible.
I’ll try to get something, but this will be on monday. I’m not sure how to upload it here btw.
DM Unseen,
Thanks in advance for your time….if it is not possible to post it here could you kindly e-mail it to sgbhide At gmail dot com
sam
DMU: You can mail it to me if you want me to host a file.
All,
I’ve been Ill this week, so it will be next week I’ll send something to Dick.
Get well, DMU.
Brilliant! I have looked at numerous suggestions involving VBA to achieve a parameter list (using an “IN” clause), and this is such a simple alternative solution. Thanks!