Querytables parameters

[Dwaine Horton]

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?

--

Vasant

"Dwaine Horton" <DwaineHorton@discussions.microsoft.com> wrote in message
news:C6E697B5-BD14-4B4E-83D7-D1F66D579F73@microsoft.com...
> 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?

[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.


--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

Dwaine Horton wrote:
> 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?

[Jamie Collins]

**** Kusleika wrote:
> 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]

"Jamie Collins" <jamiecollins@xsmail.com> wrote in message
news:1114526366.556270.212430@o13g2000cwo.googlegroups.com...
>
> **** Kusleika wrote:
>> 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>.

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.

--
**** Kusleika
MVP Excel
Daily Dose of Excel
http://www.*****-blog.com

[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


--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com