Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Aug 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL in VBA / Excel (Excel)

    Hi. I have done a little programming in VB, but I'm pretty new to VBA. In VB, I could connect to an Excel file as a database using ADO. Now I'd like to write some VBA code in Excel to connect to data that is in the same Excel file as the code, and extract data using SQL queries. How do I open a connection to "this workbook"?

    I'm using Excel 2000, but I'd imagine I'll be doing this in other versions of Excel as well.

    Thanks
    Mike

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL in VBA / Excel (Excel)

    There's quite a lot of information in the VBA Help. Go to the Answer Wizard and enter SQL.
    Gre

  3. #3
    New Lounger
    Join Date
    Aug 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL in VBA / Excel (Excel)

    Thanks for the reply. I apologize if I missed it, but all the information I can find seems to be for connecting to external databases; I'd like to connect to data that is in the same Excel file as the VBA code.

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL in VBA / Excel (Excel)

    I seem to be missing a step here. Are you attempting to populate a SQL database from Excel, or............??? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Gre

  5. #5
    New Lounger
    Join Date
    Aug 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL in VBA / Excel (Excel)

    Sorry. Just because I understand what I'm trying to say doesn't mean I don't have to explain it clearly.

    I have a single Excel file with several tables of data in several worksheets. I would like to use VBA to perform SQL queries on that data, then dump the results into a new table in the same Excel file. I have done this previously with external databases, but never within the same file. Right out of the gate I'm stumped, because I can't figure out how to open the connection to data within the same Excel file.

    Thanks for helping me with this!

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL in VBA / Excel (Excel)

    There is a tool in Excel called MSQuery - which runs some SQL queries. The normal way to access this is Data|Get External Data|New Database Query. Although this talks of external data sources, it will connect to the Excel file from which it is run. I believe that you may have similar results with VBA driven queries.

    There are a number of other Data-Mining tools available in Excel - such as Filters and Pivot Tables - which can be accessed through VBA. I just wonder whether it might make more sense to use those tools. I must admit that, for me, SQL is best used with Access. Using SQl in Excel, for me, tends for me to be something required by necessity rather than choice.
    Gre

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: SQL in VBA / Excel (Excel)

    The code I have seen connects to the file on disk (e.g., <post#=282794>post 282794</post#>). I don't know if ADO will be able to operate on an XLS you already have open in Excel.

  8. #8
    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 in VBA / Excel (Excel)

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

    I know its frustrating trying to articulate what you have been working with for some time, to some people who are trying to understand the situation. I have read some of the messages, and replies in this thread, and I still have some questions.

    Why don't you use Excel to do what SQL is going to do?

    Like if you want to compare two cells and do something, then you can use the Excel Built-In functions such as the IF function:

    This is in English:

    If Cell A1, on Sheet 1 is equal to Cell B17 on Sheet "My Sheet", then add 1 to Cell C2 Sheet 5, otherwise give me a phrase "Not Equal".

    here it is in Excel Functions...

    =IF(Sheet1!A1='My Sheet'!B17,Sheet5!C1+1,"Not Equal")

    This formula can be any where, but the cells mentioned, otherwise it will produce what we call a Circular Reference, and that is a topic for another thread.

    Sure SQL can do a whole lot and fast, but if you are in Excel, use its powers before going out of it.

    Just my thougths

    Wassim
    <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>

  9. #9
    New Lounger
    Join Date
    Aug 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL in VBA / Excel (Excel)

    Thanks, Unkamunka, Jscher, and Wassim, for your replies.

    Unkamunka - I dabbled a bit with MSQuery based on your suggestion, but Excel hangs each time. I'll look into that one some more, though - it looks promising.

    Wassim - I'd love to do this with Excel's built-in functions, but I haven't found a way. I'm trying to retrieve all records from a table that meet multiple criteria (like vlookup, but with multiple criteria and returning more than one result), then join it to related data in other tables. This is the kind of stuff SQL was born to do, and I'm hoping to avoid reinventing the wheel with my own VBA code. I've run SQL from VBA/Excel before, but always on external data. I was hoping it was just a small step to apply it "internally"

    I could make this work the same way, by putting the data into an external file, performing the queries, and dumping the results into the open Excel file, but it would be a lot better if I could keep it contained in a single file.

    I have a hunch that I could do something with "Advanced Filter", or maybe some array functions, but these are a bit beyond my experience. (Any suggestions? Are these approaches a dead-end, or worth looking into?)

    Thanks for your time and expertise, everybody!

    Mike

  10. #10
    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 in VBA / Excel (Excel)

    Hi Mike,
    ADO should be able to connect to the current workbook and work on it - something like:
    <pre>Sub ConnectToCurrentWB()
    Dim cnn As ADODB.Connection, rstNew As ADODB.Recordset
    Dim strSQL As String
    Set cnn = New ADODB.Connection
    With cnn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";" & _
    "Extended Properties=Excel 8.0;"
    .Open
    End With
    strSQL = "Select * FROM [Sheet1$] WHERE Field1 = Criterion1 and Field2 = Criterion2"
    Set rstNew = cnn.Execute(strSQL)
    Sheets(2).Range("A1").CopyFromRecordset rstNew
    rstNew.Close
    Set rstNew = Nothing
    cnn.Close
    Set cnn = Nothing
    End Sub
    </pre>

    should allow you to create data on other sheets based on criteria you specify.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    New Lounger
    Join Date
    Aug 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL in VBA / Excel (Excel)

    "Data Source=" & ActiveWorkbook.FullName & ";" & "Extended Properties=Excel 8.0;"

    That was the step I was looking for. Thank you, Rory! It worked like a charm.

    Mike

  12. #12
    New Lounger
    Join Date
    Aug 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL in VBA / Excel (Excel)

    Hi, Everybody.
    Just a quick follow-up for anyone using this information. It turns out that this approach causes a memory leak in Excel. Setting the connection and recordset variables to Nothing doesn't fix it, and the only way to reclaim the memory is to close Excel. This is documented in MS Knowledgebase article 319998: http://support.microsoft.com/default.aspx?...p;Product=xlw2K. They provide some workarounds; I'm currently working on incorporating them.

    Mike

    (Edited by HansV to activate URL - see <!help=19>Help 19<!/help>)

Posting Permissions

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