none
ListObjects.Add().QueryTable RRS feed

  • Question

  • Hi,

    I am curious as to the same question as lynnnnn.  I am currently using ListObjects.Add().QueryTable but should I be using QueryTable.Add instead?  I am using Excel 2010 to do an ODBC query to Teradata.

    Here is a sample of my code:

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "ODBC;DSN=<connection>;UID=<userid>;; AUTHENTICATION=TD2", Destination:=Range( _
            "$A$1")).QueryTable
            .CommandText = Array( _
            "SELECT * FROM <database>.<table>")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .Refresh BackgroundQuery:=False
        End With

    I also have other questions:

    1) Is there a way to bring the databack without it being in a table?

    2) Is there a way to pass a password to the query and have the password encrypted?

    3) If 2 is not possible is there a way to enter your password once but have it used in subsequent queries?

    Any help would be great!

    Thank you.

    Tuesday, July 3, 2012 4:11 PM

Answers

  • Hi Cassfutbol,

    Can the destination be a variable, or something different then to the spreadsheet?

    No, it seems that we aren't able do it.

    1) Is there a way to bring the databack without it being in a table?

    After you clarify, I just this you are able to scan the range which filled data and remove them style after ListObjects.Add perform.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Friday, July 13, 2012 6:45 AM
    Moderator

All replies

  • Hi Cassfutbol,

    Thanks for posting in the MSDN Forum.

    I can't catch your mean of "Is there a way to bring the data back without being in a table". Would you please clarify it?

    "Is there a way to pass a password to the query and have the pasword encripted?"

    I think it depend on the DNS which your set. If your DNS required the password and you omit it in your connection string, a prompt will alert you input password.

    Have a good day,

    Tom 


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, July 4, 2012 7:21 AM
    Moderator
  • Hi Tom,

    When I run this code the data comes back with the table headings with filters on and every other row is color filled.  I can change everything via the macro.  I was just wondering if there is a way have the data come back with just headers no filters, no borders and no color filled rows.  I cannot find any documentation on the command:

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "ODBC;DSN=<connection>;UID=<userid>;; AUTHENTICATION=TD2", Destination:=Range( _
            "$A$1")).QueryTable

    As you can see I do not have a password inmy connection string.  Just wondering what the syntax would be to add the password field to the connection string.

    Thanks

    Thursday, July 5, 2012 1:09 PM
  • Hi Cassfutbol,

    On my side I use a SqlServer database(I think similar things will happen on mysql, orcal due to they need password to open database too) to reproduce your issue. It will let me enter the password. I think similar thing will happend it you set password for your access database.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, July 11, 2012 7:21 AM
    Moderator
  • Hi Tom,

    I was able to get this to work the way I wanted to.  I created a form and enter my password there and pass it as a variable.  Works really slick.

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "ODBC;DSN=" & strDatabaseName & ";UID=" & strUsername & ";UPWD=" & strPassword & "; AUTHENTICATION=TD2", _
            Destination:=Range("$A$1")).QueryTable

    I do have a couple of other questions though.

    Can the destination be a variable, or something different then to the spreadsheet?

    How do you handle, or receive, error messages from the query?

    Thank you!

    Wednesday, July 11, 2012 11:46 AM
  • Hi Cassfutbol,

    Can the destination be a variable, or something different then to the spreadsheet?

    No, it seems that we aren't able do it.

    1) Is there a way to bring the databack without it being in a table?

    After you clarify, I just this you are able to scan the range which filled data and remove them style after ListObjects.Add perform.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Friday, July 13, 2012 6:45 AM
    Moderator
  • I have yet another question.

    In this code:

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "ODBC;DSN=<connection>;UID=<userid>;; AUTHENTICATION=TD2", Destination:=Range( _
            "$A$1")).QueryTable

    what is the AUTHENTICATION=TD2?  Is this because I am logging into Teradata?  Would this be different if I wanted to login to DB2 on the mainframe?

    Thanks

    Wednesday, August 15, 2012 6:03 PM