Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    All Over, CA, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL & VBA (2000)

    Is it possible to use SQL commands to query an Excel table instead of Access?

    I have been looking around but everyone seems to only give examples using Access or some other DB product. I want to use a table on an Excel Spreadsheet. It seems pointless to use Access for a single table.

    I have searched here and a few other places but don't find an example. Any help would be appreciated.

    I would prefer to not load a bunch of add-ins, but if necessary I will.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: SQL & VBA (2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> jturner

    OK let me understand it... You have a "table" in a MS-Excel workbook, and you want to use Excel's VBA to run SQL commands to "work" with that "Table"?

    The answer to this question would be yes, you would set up Excel as a DNS source and then you an use all sorts of SQL commands.

    But the good answer is <font color=blue> WHY</font color=blue>

    If you already have it in MS-Excel, you can use Excel's capabilities to filter the data, or subtotal the data, or sort the data and what have you, and to be honest with you you don't even need VBA to do these kind of tasks, unless you want to do them repeatadly.

    If you would tell us "What you are trying to do", maybe we can get you a better answer or a neat approach on how to do what you are trying.

    HTH

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Lounger
    Join Date
    Mar 2002
    Location
    All Over, CA, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL & VBA (2000)

    I am putting together a little application that relies heavily on 3-5 conditions or criteria that do about 25-30 counts and sums. SQL is so clean and easy. Arrays work to a point, but they are so messy. I want to add 4 or 5 rows of data a day, push a button and recalculate 25 counts and sums and present it in reports and charts.

    I guess the bottom line is that I need the db capabilities to handle the data entry of the rows and Excel's advanced charting.

    I was looking at keeping the data in Access and charting with Excel. I was trying to decide which one was the better to use as the front end that calls the other. I keep looking at the fact that I do not need a relational db, just a single flat table.

    Why couldn't I do a DAO call right inside my VB app, without calling other spreadsheet or database files? Can you do a SQL query on a recordset that is a defined range name?

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: SQL & VBA (2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> jturner

    OK lets talk:

    If you get the data into a worksheet, then for these 3 to 5 conditions you will use 3 to 5 cells, one for each, and then conclude what you want to do based on these cell's conditions.

    Or if you want to use 3 to 5 worksheets that would contain the data that would match the first condition in the first worksheet, then the data that would match the second condition in the second worksheet, mind you the data now matches the first and the second conditions as well, and then you will have the data that would match the third condition in the third worksheet and so on and by the time you reach your 5th worksheet that data is what matches for the 5 conditions you have.

    The chart range would be on the 5th worksheet. and that is all she wrote. To filter and copy data is much simpler than returning a recordset and then fusing with that as well.

    But its your project and you can do what you want with it. <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

    HTH

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL & VBA (2000)

    Have you checked out the data, filter, advanced filter option ?

    Also, Data, filter, autofilter provides a number of options you should be able to use relatively effortlessly.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: SQL & VBA (2000)

    Hi,
    Here are two functions that should do what you're after - one uses DAO, the other ADO:
    <pre>Function ADOGetExcelData(strFilePath As String)
    Dim cnnDB As ADODB.Connection, rst As ADODB.Recordset, x As Integer
    Set cnnDB = New ADODB.Connection
    With cnnDB
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Extended Properties") = "Excel 8.0"
    .Open strFilePath
    End With
    Set rst = New ADODB.Recordset
    With rst
    .Open "SELECT * FROM data_range", cnnDB
    While Not .EOF
    For x = 0 To .Fields.Count - 1
    Debug.Print .Fields(x).Value
    Next
    .MoveNext
    Wend
    .Close
    End With
    cnnDB.Close
    Set rst = Nothing
    Set cnnDB = Nothing
    End Function
    Function DAOGetExcelData(strFilePath As String)
    Dim db As DAO.database, rst As DAO.Recordset, x As Integer
    Set db = OpenDatabase(strFilePath, False, True, "Excel 8.0;")
    Set rst = db.OpenRecordset("SELECT * FROM data_range")
    With rst
    While Not .EOF
    For x = 0 To .Fields.Count - 1
    Debug.Print .Fields(x).Value
    Next
    .MoveNext
    Wend
    End With
    Set rst = Nothing
    Set db = Nothing
    End Function
    </pre>

    You can adapt them so that you can pass them a range name and SQL string if you require.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: SQL & VBA (2000)

    Have you thought about
    1. Use a dynamic name that automatically resizes as data rows are added
    2. Use this range name as the datasource for an Excel pivottable
    3. Use the pivottable features to do your counts and sums (its worth learning)
    4. Use Excels pivotchart features
    ..then your button just needs to say [Refresh]

    zeddy

  8. #8
    Lounger
    Join Date
    Mar 2002
    Location
    All Over, CA, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL & VBA (2000)

    The application is a lot like jounal posting against a GL. A bunch of transactions keep coming in filling up date, symbol, buysell type, shortlongtype, qty(positive and negative numbers), price, commsion, costs columns. Then I have to produce a page full of totals, counts and averages in a bunch of different combinations.

    I wrote a custom data entry screen and wanted a button to pop up the analysis screens. I was looking at pivot tables as they look programable with VBA. Since SQL doesn't seem to be the end-all, I guess I will look intoo this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •