locked
QueryTables produces inconsistent results RRS feed

  • Question

  • Hi All,

    I am writing a VB macro for Excel to update the prices and on-hand quantities of a list of part numbers from an excel worksheet.  I have tried both 2000 and 2010 and gotten about the same results.

    Basically, I create a worksheet named "web query" and on it create a querytable.  The connection string is created specifically for this one site I'm searching.  The process is repeated for each part in the list.  For debug purposes, there are say four or five parts.

    The odd part is depending on which part # I start with, I get different results.  But, if I connect directly to the website using my connection string, I always get the same result.  When the QueryTable refresh fails, it returns one area, that's two rows tall by 1 column tall.  A good query returns 40-50 rows tall by 10-20 column wide, depending upon the exact part number.

    Any thoughs are much appreciated.

    Here's part of the query code:

        With o.QueryTables.Add(Connection:=s, Destination:=o.Range("B2"))
            .BackgroundQuery = False
            .WebFormatting = xlRTF
            .WebSelectionType = xlAllTables
            .RefreshPeriod = 0
            .MaintainConnection = False
            .Refresh

    Regardless of whether the query is successful or not, the entire worksheet is deleted once the information is read from it.

    I should go on to say, that the first few part numbers searched *always* returns correct information.  It is around the third or fourth number in the list where this happens.  There are no run-time errors, just the returned information is blank.

    Thanks,

    Scott


    Scott



    • Edited by Scott14414 Monday, June 18, 2012 11:57 PM
    Monday, June 18, 2012 11:53 PM

Answers

  • OK, I think I have it figured out.  The short reply is in response to a improperly formatted part number, and several other query problems.  I was comparing the results returned by IE to what I'm getting from the QueryTable, which under normal conditions are the same, however, subtle changes to the part number, even legitimate ones, produce this response.

    Scott


    Scott

    • Marked as answer by Quist Zhang Wednesday, June 27, 2012 9:38 AM
    Tuesday, June 19, 2012 2:47 PM

All replies

  • I am not familiar with what you are doing there.

    I follow Microsoft's tutorial on using ADO with Excel and VBA. http://support.microsoft.com/kb/257819

    Tuesday, June 19, 2012 1:03 AM
  • Thanks for the reply, but I'd rather not rewrite all my code.

    Scott


    Scott

    Tuesday, June 19, 2012 11:56 AM
  • OK, I think I have it figured out.  The short reply is in response to a improperly formatted part number, and several other query problems.  I was comparing the results returned by IE to what I'm getting from the QueryTable, which under normal conditions are the same, however, subtle changes to the part number, even legitimate ones, produce this response.

    Scott


    Scott

    • Marked as answer by Quist Zhang Wednesday, June 27, 2012 9:38 AM
    Tuesday, June 19, 2012 2:47 PM