We help IT Professionals succeed at work.

object required error

ktjamms2
ktjamms2 asked
on
I am trying to write a VB script in excel that will query some tables and return the results in a spreadsheet. Before I've used an access query and imported it into excel as a database query. Now, I'm using SQL Enterprise Manager and don't know of a way to import a query from the query analyzer, so I wrote this VB script trying to connect to my tables and import the data that way. I'm getting an error that says object required. Here is my script:

Sub CompareQry()

' Declare the QueryTable object
Dim qt As QueryTable

' Declare database varaiables
Dim SQLDB           As String
Dim SQLServer       As String
Dim SQLDbase        As String
Dim SQLUser         As String
Dim SQLPword        As String

Open App.Path & "\SQLConnect.txt" For Input As #1
Input #1, SQLServer, SQLDbase, SQLUser, SQLPword
Close #1

' Set up the SQL Statement
sqlstring = "SELECT a.item,a.qty,a.strnum,b.item as itemb, b.qty as qtyb,b.strnum " _
& "FROM     firsttbl a " _
& "inner join [server1].STORE.dbo.tablename b " _
& "on a.strNum = b.strnum and " _
& "a.Item = b.Item " _
& "and a.qty <> b.qty " _
& "Where a.strNum = '09'  order by a.item"

' Set up the connection string
connstring = "Provider=sqloledb;Server=" & SQLServer & ";User Id=" & SQLUser & ";Pwd=" & SQLPword & ";Database=" & SQLDbase

' Implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1
With ActiveSheet.qt.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
 .Refresh
 
 End With

End Sub

Thanks for all of your help!
KT

Comment
Watch Question

You don't declare connstring as a string, but that will only error if you have an Option Explicit on.  Which line does it give the "object required" message on when you run it?
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Your error is most likely here

ActiveSheet.qt.Add

Because ActiveSheet does not have something called qt

You may want

Set qt = ActiveSheet.QueryTables.Add(....  
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Also, this line

Open App.Path & "\SQLConnect.txt" For Input As #1

Should be

Open Application.Path & "\SQLConnect.txt" For Input As #1

However, Application.Path refers to where Excel is installed, not where the workbook is, so it will look somewhere like
C:\Program Files\Microsoft Ofice\Office 12\

Author

Commented:
Not really sure what you mean cyberkiwi?
I declared:
' Declare the QueryTable object
Dim qt As QueryTable
 should I change to look like this?:

Set qt = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
With ActiveSheet.qt.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
 .Refresh

Author

Commented:
I tried this:
With ActiveSheet.qt.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
 .Refresh

Still get object required error

and this still gets error:
Set qt = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
With ActiveSheet.qt.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
 .Refresh

Author

Commented:
OK, my text file is in the path:
C:\Program Files\Microsoft Ofice\Office 14\SQLConnect.txt

Author

Commented:
I have an Office 12 and and Office 14 folder. I'm using Office 2010 version

Author

Commented:
I saved my spreadsheet to the path:
C:\Program Files\Microsoft Ofice\Office 14\Excel Stuff\
and I copied my text file to that folder too

Author

Commented:
This is what I have now: - still getting object required error

Sub CompareAur()

' Declare the QueryTable object
Dim qt As QueryTable

' Declare database varaiables
Dim SQLDB           As String
Dim SQLServer       As String
Dim SQLDbase        As String
Dim SQLUser         As String
Dim SQLPword        As String

Open Application.Path & "\Excel Stuff\SQLConnect.txt" For Input As #1
Input #1, SQLServer, SQLDbase, SQLUser, SQLPword
Close #1

' Set up the SQL Statement
sqlstring = "SELECT a.item,a.qty,a.strnum,b.item as itemb, b.qty as qtyb,b.strnum " _
& "FROM     WhsInvtQtys a " _
& "inner join [ServerName].DBName.dbo.psWhsInvtQtys b " _
& "on a.strNum = b.strnum and " _
& "a.Item = b.Item " _
& "and a.qty <> b.qty " _
& "Where a.strNum = '09'  order by a.item"

' Set up the connection string, reference an ADODB connection
connstring = "Provider=sqloledb;Server=" & SQLServer & ";User Id=" & SQLUser & ";Pwd=" & SQLPword & ";Database=" & SQLDbase

' Implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1

'Set qt = Active.Sheet.QueryTable.Add(Connection:=constring, Destination:=Range("A1"), Sql:=sqlstring)
With ActiveSheet.QueryTable.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
 .Refresh
 
 End With

End Sub

Author

Commented:
With this I get error : Object doesn't support this property or method

How do I display an Immediate window or some way to see where the error is occuring?
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)

6th line from the end.  It is plural "QueryTables"

If you run the sub from within VBE (press F5 with cursor in there), it should stop at exactly the line with the error

Author

Commented:
I made the change to QueryTables! But still getting Run-time error 1004: Application-defined or object-defined error. Notice I commented out the Set qt line because I wasn't sure how that should be.
Thanks, KT

Author

Commented:
After I click OK to the error message, II pressed f5 and nothing happens?
Brad Sims, CCNACyberSecurity Analyst

Commented:
If you have the variable qt Dimmed (which you do) you don't have to use the word 'Set' in front.  It would just be:
qt = Active.Sheet.QueryTable.Add(Connection:=constring, Destination:=Range("A1"), Sql:=sqlstring)

Also, F8 is used to step through VBA code.  I think CyberKiwi was typing too fast :)

Author

Commented:
I'm not sure I'm getting this. This is what I have now: - still getting error 'invalid use of property' at the
qt =ActiveSheet.Sheet.QueryTables.Add....

Sub CompareAur()

' Declare the QueryTable object
Dim qt As QueryTable

' Declare database varaiables
Dim SQLDB           As String
Dim SQLServer       As String
Dim SQLDbase        As String
Dim SQLUser           As String
Dim SQLPword        As String

Dim sqlstring            AS String

Open Application.Path & "\Excel Stuff\SQLConnect.txt" For Input As #1
Input #1, SQLServer, SQLDbase, SQLUser, SQLPword
Close #1

' Set up the SQL Statement
sqlstring = "SELECT a.item,a.qty,a.strnum,b.item as itemb, b.qty as qtyb,b.strnum " _
& "FROM     Mytable1 a " _
& "inner join [server].database.dbo.Mytable2 b " _
& "on a.strNum = b.strnum and " _
& "a.Item = b.Item " _
& "and a.qty <> b.qty " _
& "Where a.strNum = '99'  order by a.item"

' Set up the connection string, reference an ADODB connection
connstring = "Provider=sqloledb;Server=" & SQLServer & ";User Id=" & SQLUser & ";Pwd=" & SQLPword & ";Database=" & SQLDbase

' Implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1

qt = Active.Sheet.QueryTables.Add(Connection:=constring, Destination:=Range("A1"), Sql:=sqlstring)
With ActiveSheet.qt.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
 .Refresh
 
 End With

End Sub

Author

Commented:
OK, I changed
' Declare the QueryTable object
Dim qt As QueryTables

To:
' Declare the QueryTable object
Dim qt As QueryTable

But still getting Run-time error '424' Object required

Do I need to set something up in my References?

Author

Commented:
I'm new to this making a connection business, but if I try to Dim my connection variable I get an error:
'User-defined type not defined'
with this declaration:
Dim connstring  As ADODB.Connection
Brad Sims, CCNACyberSecurity Analyst

Commented:
I think the issue is you have an extra 'dot' in this line:

qt = Active.Sheet.QueryTables.Add(Connection:=constring, Destination:=Range("A1"), Sql:=sqlstring)

should be:

qt = ActiveSheet.QueryTables.Add(Connection:=constring, Destination:=Range("A1"), Sql:=sqlstring)

Author

Commented:
That was not the whole problem :-(
I removed the extra 'dot' but still getting an error on that line:
Application-defined or object-defined error

Also, I should be able to use the 'Dim connstring  As ADODB.Connection' without an error?
Brad Sims, CCNACyberSecurity Analyst
Commented:
I assume you have the references set for ADODB connections?  Under Toos - References (in the VB Editor), you have to have a reference set in order to use that.  It's starting to sound like that is part of the problem.

untitled.bmp

Author

Commented:
OK, I set the reference. Now, I am still getting yet another error on the same line:
qt = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)

error:
Invalid procedure call or argument.

Really not sure what could be missing now?
Brad Sims, CCNACyberSecurity Analyst

Commented:
Ok, bear with me for a little while, I'm failry new to QueryTables, but I think we're close to solving your issue.

I think the issue is the last two lines:

qt = Active.Sheet.QueryTables.Add(Connection:=constring, Destination:=Range("A1"), Sql:=sqlstring)
With ActiveSheet.qt.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)

So I think the code sees the "qt" in the second line as a whole other line.  In other words, it's probably reading it as:

qt = Active.Sheet.QueryTables.Add(Connection:=constring, Destination:=Range("A1"), Sql:=sqlstring)
With ActiveSheet.Active.Sheet.QueryTables.Add(Connection:=constring, Destination:=Range("A1"), Sql:=sqlstring).Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)

Try skipping the qt and just selecting a range on the activesheet Range("A1").Select, and then run your querytable line

With ActiveSheet.Querytables.Add(...

Author

Commented:
I did this:
commented out the qt = ActiveSheet.QueryTables.Add(....
Added:
ActiveSheet.Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
 .Refresh
Got Run-time error '5': Invalid proceedure call or argument on the last line
Brad Sims, CCNACyberSecurity Analyst
Commented:
Ok, with some more research, I found an issue in one of my books:

"One notable quirk of parameter queries is that QueryTables will not support them if you use the OLE
DB provider used in the previous two sections. Instead, you must switch to the ODBC driver. This is a
simple matter of changing the first argument of the connection string from OLEDB to ODBC and providing ODBC connection information".

This is from Excel 2007 VBA Programmers Reference - Chapter 21.  Your connection string is using OLEDB which may be part or all of the issue.  Change that and let me know if it works.

Author

Commented:
Sooo..what you're saying is that I have to set up an ODBC connection in my machine's control panel to the servers first and then change my code to:
connstring = "Provider=ODBC;Server=" & SQLServer & ";User Id=" &  SQLUser & ";Pwd=" & SQLPword & ";Database=" & SQLDbase
Brad Sims, CCNACyberSecurity Analyst

Commented:
That's what they say, the choice is up to you.  Like I said, I am new to querytables so if you don't believe that's the issue then let me know.  I will have some other experts drop by here and take a look if you'd prefer to do that.
Most Valuable Expert 2011
Top Expert 2011

Commented:
Assuming your original connection was OK, your code should have been (as cyberkiwi said):
Sub CompareQry()

' Declare the QueryTable object
Dim qt As QueryTable

' Declare database varaiables
Dim SQLDB           As String
Dim SQLServer       As String
Dim SQLDbase        As String
Dim SQLUser         As String
Dim SQLPword        As String

Open App.Path & "\SQLConnect.txt" For Input As #1
Input #1, SQLServer, SQLDbase, SQLUser, SQLPword
Close #1

' Set up the SQL Statement
sqlstring = "SELECT a.item,a.qty,a.strnum,b.item as itemb, b.qty as qtyb,b.strnum " _
& "FROM     firsttbl a " _
& "inner join [server1].STORE.dbo.tablename b " _
& "on a.strNum = b.strnum and " _
& "a.Item = b.Item " _
& "and a.qty <> b.qty " _
& "Where a.strNum = '09'  order by a.item"

' Set up the connection string
connstring = "Provider=sqloledb;Server=" & SQLServer & ";User Id=" & SQLUser & ";Pwd=" & SQLPword & ";Database=" & SQLDbase

' Implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1
Set qt = ActiveSheet.Querytables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
 qt.Refresh

End Sub

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Yes to http:#a32996432.  I tried OLEDB and it didn't work for me either.  This is where you got to with http:#a32983553

And also please use the code per Rory's comment in http:#a32998011 (directly above), which was correct as of http:#a32983553 aside from the OLEDB issue

If you Dim qt as QueryTable, it means that qt is a variable type pointing to a QueryTable object.  It does not make it a short form for writing "QueryTables"!  You still need to call the Add function of the QueryTables collection of the ActiveSheet object, i.e.

Set qt = ActiveSheet.Querytables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)

Author

Commented:
on my 6th comment I didn't mean to put the actual server,table,and fields...can they be removed?
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Hit the "request attention" link near the top, right around the question area.  Type what you need.

Author

Commented:
Thank-you!