sql against a spreadsheet

I need to return a recordset from an excel spreadsheet and total certain fields before I upload the data to an AS400 file.  I already have the worksheet defined in VB. I need to know if its possible to use sql against the spreadsheet in this way..  Thanks in advance.

God Bless America

LVL 2
mmcmillenAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Arthur_WoodCommented:
Are you using VB to open the spreadsheet, or are you using VBA (within Excel, itself) - that is VBA in an Excel macro?
0
 
rovermCommented:
In Excel you can use the QueryTables.Add method to add data to a sheet:

Dim qt As QueryTable
sqlstring = "select 96Sales.totals from 96Sales where profit < 5"
connstring = _
    "ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;Database=96Sales"
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
        Destination:=Range("B1"), Sql:=sqlstring)
    .Refresh
End With

(example from Excel help)

D'Mzzl!
RoverM
0
 
mmcmillenAuthor Commented:
I am opening excel as an excel8 object
0
 
mmcmillenAuthor Commented:
Thanks everyone for their time .

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.