Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytables parameters
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytables parameters
Perhaps because you have misspelled Parameters?
-- Vasant "Dwaine Horton" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytables parameters
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. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytables parameters
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. -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytables parameters
"Jamie Collins" wrote in message oups.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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytables parameters
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 :) -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
QueryTables Add | Excel Programming | |||
Error when trying to use QueryTables.Add | Excel Programming | |||
With ActiveSheet.QueryTables.Add | Excel Programming | |||
QueryTables Interrupt if URL is Down | Excel Programming |