External query of SQL server

[Linn Kubler]

Hi,

I want to use Excel 2003 to query data from a SQL 2000 server. If I create
the ODBC connection with the query wizzard it works fine. But I would like
to avoid having to install that on everyone's computers so I'm writing an
application to run this report. I'm thinking I can then save it with a
workbook and pass it around that way.

At any rate, I'm having a problem retrieving the data now. I setup my macro
like this:

Dim lsSortBy, strMessge As String
Dim oQt As QueryTable

' Setup the connection string
DefineConnection

' Setup the SQL select command
DefineSQL

' Create the destination workbook
Set NewBook = Workbooks.Add

' Setup the report destination worksheet
With NewBook.Worksheets("sheet1")
.Range("A1").Value = "Inventory Valuation Report"
.Range("A2").Value = Now()
' Run the query
Set oQt = .QueryTables.Add( _
Connection:=strConn, _
Destination:=.Range("A4"), _
Sql:=strSQL)

oQt.Refresh

End With

What I get is cell A4 says, "ExternalData_1". Any ideas what's going on? I
don't get any error messages so I think everything is working. I'm prompted
for the username and password for accessing the SQL server and if I type the
wrong one in I do get an error, so I'm thinking that part is working. How
should I trouble shoot this?

Thanks in advance,
Linn

 

[Linn Kubler]

Never mind, I found it. There was a couple typos in my SQL Select
statement.

Thanks,
Linn