Querytables parameters

[Guest]

I am trying to use the parameters feature of query tables and keep getting a
type mismatch. Below is code:

Dim param1 As QueryTable.Paramters
Dim param2 As QueryTable.Parameters
Dim qt As QueryTable

Set qt = Sheets("sheet1").QueryTables(1)
qt.Sql = "select wodate from natop.dbo.workmast where (wodate between ?
and ?)"
Set param1 = qt.Parameters.Add("Start Parameter", xlParamTypeVarChar)
param1.SetParam xlConstant, "01/01/2005"
Set param2 = qt.Parameters.Add("End Parameter", xlParamTypeVarChar)
param2.SetParam xlConstant, "04/01/2005"
qt.Refresh

The mismatch is coming on my first dim statement. What am I doing wrong?

 

[Vasant Nanavati]

Perhaps because you have misspelled Parameters?

 

[Dick Kusleika]

Dwaine

Dim param1 as Parameter

When you use a dot operator in a Dim statement, you're saying
ObjectLibrary.Object. In this case, you're saying that QueryTable is an
object library and it's not. Both the QueryTable object and the Parameter
object are objects in the Excel object library. You could say this

Dim param1 As Excel.Parameter

but it's totally unnecessary.

Also, you'll want those variable to be dimmed as Parameter not Parameters.
Parameters, the plural, is a collection object that can contain multiple
Parameter objects. For what you're doing, you just want the singular
Parameter.

Finally, fix the mispelling like Vasant said.

 

[Jamie Collins]

Both the QueryTable object and the Parameter
object are objects in the Excel object library. You could say this

Dim param1 As Excel.Parameter

but it's totally unnecessary.

'Optional' could be a better way of putting it <g>.

By omitting the library name you are making an assumption, in this case
that a reference to Excel has been set and that it has a higher
precedence than any other referenced library with a Parameter class
e.g. ADODB. I wager that, in contrast, for variables of type
ADODB.Recordset you *always* include the library name, yet how often do
you reference another library with a Recordset class? Convention has a
lot to answer for.

I often use Excel.<class> in code for which I think it may get some
future re-use in a non-Excel project.

Jamie.

--

 

[Dick Kusleika]

'Optional' could be a better way of putting it <g>.

No doubt. I've always assumed that Excel had a higher precedence than
anything else, but I probably shouldn't.

By omitting the library name you are making an assumption, in this case
that a reference to Excel has been set and that it has a higher
precedence than any other referenced library with a Parameter class
e.g. ADODB. I wager that, in contrast, for variables of type
ADODB.Recordset you *always* include the library name, yet how often do
you reference another library with a Recordset class? Convention has a
lot to answer for.

You'd win that wager. I never thought about it, but I'll bet I reference
other libraries with Recordset and have just gotten lucky in the order.

I often use Excel.<class> in code for which I think it may get some
future re-use in a non-Excel project.

That's one I've never considered. My apps would be a heck of a lot harder
to port to, say, VB6.

 

[Dick Kusleika]

You'd win that wager. I never thought about it, but I'll bet I
reference other libraries with Recordset and have just gotten lucky
in the order.

I don't know why I would bet that. It's probably not true. I don't know
what libraries have Recordset, but I do know that I've never had both ADO
and DAO referenced in the same project. I still won't be changing my habit
though