Re: Querytables parameters
Vasant Nanavati
--
Vasant
"Dwaine Horton" <Dwaine...@discussions.microsoft.com> wrote in message
news:C6E697B5-BD14-4B4E...@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
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.
--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com
Jamie Collins
Dick 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" <jamiec...@xsmail.com> wrote in message
news:1114526366.5...@o13g2000cwo.googlegroups.com...
>
> Dick 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.
--
Dick Kusleika
MVP Excel
Daily Dose of Excel
http://www.dicks-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 :)