Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Excel 2010 Query Tables

This is a discussion on Excel 2010 Query Tables within the Excel Questions forums, part of the Question Forums category; I'm trying to query a SQL 2000 Database. This is the basics of what I have which has gone through ...

  1. #1
    New Member
    Join Date
    Feb 2011
    Posts
    6

    Default Excel 2010 Query Tables

    I'm trying to query a SQL 2000 Database. This is the basics of what I have which has gone through several iterations.

    Sub GetReceivingLastYear(EndDate As Date)
    Dim QT As Excel.QueryTable
    Dim CN As ADODB.Connection
    Dim Period As Integer
    Dim connString As String
    Dim SQL As String
    Dim Clipboard As New DataObject
    Dim Tmp As QueryTable

    Worksheets("LYReceived").Range("A5:K8").ClearContents

    Set Tmp = Worksheets("LYReceived").QueryTables(1)

    If Tmp Is Nothing Then
    MsgBox ("Need to Create QT")

    Else
    MsgBox ("already exists")
    Set Y = Nothing
    End If


    Set CN = New ADODB.Connection
    Set Rs = New ADODB.Recordset
    connString = "connection string obscurred"

    CN.Open connString

    SQL = "Select * from some table"


    Rs.Open SQL, CN, adOpenForwardOnly

    Set QT = Worksheets("LYReceived").QueryTables.Add(Rs, Destination:=Worksheets("LYReceived").Range("A5"))
    QT.Name = "qtLYR"
    QT.Refresh

    Rs.Close
    Set Rs = Nothing

    CN.Close
    Set CN = Nothing

    End Sub

    Unfortunately, whenever I run this, and the dozens of variations of it, I continue to get Connection, connection1, connection2.... pilling up in my connections for my spreadsheet.

    I've done everything I can to remove them in code, but nothing seems to work.

    Can anyone assist me with this?

  2. #2
    New Member
    Join Date
    Feb 2011
    Posts
    1

    Default Re: Excel 2010 Query Tables

    Use an ODBC DSN instead, where you have more control over the connection info. Then code this way:


    ' Declare the QueryTable object
    Dim qt As QueryTable
    ' Set up the SQL Statement
    sqlstring = "select au_fname, au_lname from authors"

    ' Set up the connection string, reference an ODBC connection
    ' There are several ways to do this
    ' Leave the name and password blank for NT authentication
    connstring = _
    "ODBC;DSN=pubs;UID=;PWD=;Database=pubs"

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

  3. #3
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    21,371

    Default Re: Excel 2010 Query Tables

    What messages are you getting from the code? You don't appear to be deleting the existing qt, just adding new ones.
    Rory
    Microsoft MVP - Excel
    Posting guidelines | Forum rules | FAQs | HTML Maker

    Please use CODE tags when posting code. Either select the code and then press the # button or type them manually:
    [CODE] Your code goes here [/CODE]

    Excel matters

  4. #4
    New Member
    Join Date
    Feb 2011
    Posts
    6

    Default Re: Excel 2010 Query Tables

    If I delete the QT nothing happens to the connection. It remains in the Workbook Connections screen.

    Is there any way that I can delete all connections that begin with "Connection" via VBA to clean them up after my code as run?

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    21,371

    Default Re: Excel 2010 Query Tables

    How are you deleting the QT?
    Rory
    Microsoft MVP - Excel
    Posting guidelines | Forum rules | FAQs | HTML Maker

    Please use CODE tags when posting code. Either select the code and then press the # button or type them manually:
    [CODE] Your code goes here [/CODE]

    Excel matters

  6. #6
    New Member
    Join Date
    Feb 2011
    Posts
    6

    Default Re: Excel 2010 Query Tables

    Quote Originally Posted by BuckWoody View Post
    Use an ODBC DSN instead, where you have more control over the connection info. Then code this way:
    I've tried that, and just tried it again. It doesn't remove the connections. They just build up.

    Incidentally, it doesn't matter if I close and re-open the file, the old connections remain.

    It has to do with how I'm adding a QT and a connection with it, but not being able to delete the connection afterward.

  7. #7
    New Member
    Join Date
    Feb 2011
    Posts
    6

    Default Re: Excel 2010 Query Tables

    Quote Originally Posted by rorya View Post
    How are you deleting the QT?
    Right before the End Sub I placed:

    QT.Delete

    Am I doing it wrong?

  8. #8
    Board Regular
    Join Date
    Jun 2002
    Posts
    1,858

    Default Re: Excel 2010 Query Tables

    I'm confused about your whole approach. I view query tables and ADO recordsets as two exclusive items.

    I could:

    1) Create a query table in Excel (not VBA) using the built in tools and have it refresh whenever I want
    or
    2) I could create an ADO recordset, then loop through it and write the output to a range of cells.

    You seem to be doing both at once and I'm not sure why.

    I think you would be better served to create a query table using the built-in Excel tools, then when you need to change the SQL, use VBA to change the connection string property of the query table and then refresh it.

  9. #9
    New Member
    Join Date
    Feb 2011
    Posts
    6

    Default Re: Excel 2010 Query Tables

    Quote Originally Posted by ChrisM View Post
    I'm confused about your whole approach. I view query tables and ADO recordsets as two exclusive items.

    I could:

    1) Create a query table in Excel (not VBA) using the built in tools and have it refresh whenever I want
    or
    2) I could create an ADO recordset, then loop through it and write the output to a range of cells.

    You seem to be doing both at once and I'm not sure why.

    I think you would be better served to create a query table using the built-in Excel tools, then when you need to change the SQL, use VBA to change the connection string property of the query table and then refresh it.
    Thanks ChrisM. Could you perhaps point me toward a link with a good example of this?

  10. #10
    Board Regular
    Join Date
    Jun 2002
    Posts
    1,858

    Default Re: Excel 2010 Query Tables

    well......!!!! Now I'm feeling silly. My answer was based on my experience from Excel 2000/2003. I opened up a new file in 2007 and was going to type up an answer for you, then quickly found out that the way 2007+ handles data connections is really confusing to me! On top of that, I looked in one of my work files, and I have like 50 empty data connections in there, so I actually have the same end-problem as you!

    So, sorry, not ready to give you an answer yet, need to figure this out myself first!

    ps: I still maintain you don't need both a recordset and a query table though, but can't prove it in 2007 at the moment.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com