Script error while retreiving web query data using VBA


  • I am using Excel VBA to execute URL links to web reports.  The user selects a report from a list and VBA retreives the correct URL from a list and builds the report.  It works great for about 50 URLs.  However for one report I get no data and no error. It returns nothing.  When I go to edit the query manually from on the worksheet, the user window pops up then an error window pops up that says "An error has occured in the script on this page (Line / Char / Error / Code) Do you want to continue running scripts on this page?" If I respond, the data populates as desired.  It doesn't matter if I respond 'yes' or 'no'.

    Within Internet Explorer I have script debugging disabled and script error notifications turned off.

    This is the code to create the query table:

    With ActiveSheet.QueryTables.Add(Connection:= _
         QBookmark, Destination:=ActiveSheet.Range(strQuerySpot))
      '      .Name = QSheetName
            .FieldNames = True
            .PreserveFormatting = True
            .BackgroundQuery = True
            .RefreshStyle = xlOverwriteCells
            .SavePassword = True
            .SaveData = True
            .AdjustColumnWidth = False
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebTables = "20"
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = True
            .WebDisableDateRecognition = False
            .WebDisableRedirections = True '
            .Refresh BackgroundQuery:=False
        End With

    How can I stop the script debugging from within the web query?

    Wednesday, June 26, 2013 10:59 PM


All replies