Passing Params to Embedded query programmatically

  • Thread starter Thread starter G
  • Start date Start date
G

G

Trying to create a loop that refreshes a query with a new parameter each
loop. I can figured out how to refresh the query by recording a macro, but
I do not see anything passing a parameter even though a popup form showed
allowing a parameter entry.

Any ideas
Thanx

Example:
Dim ParamRange as string
For i=1 to somenumber

ParamRange = "A" & i
parameter value = Range(ParamRange).value
Refresh query
copy and paste result to desired worksheet
Next
 
The result range of the query is a querytable object in VBA, and it has a
Parameters collection associated with it that can be used to set the
parameter values, so it would go something like this:

Dim ParamRange as string
For i=1 to somenumber

ParamRange = "A" & i
With Sheets("QueryTableSheetName").QueryTables("QueryTableName")
.Parameters("ParameterName").Value = Range(ParamRange).value
.Refresh
End With
copy and paste result to desired worksheet
Next
 
Back
Top