Web Query Error Handling

**instarac** · 12-01-2011, 02:10 PM

I have a Excel 2007 workbook that imports data using web queries from a long (600+)series of web pages into an Excel 2007 workbook. I use the "Refresh All" option under the Data menu to update the data.

There are occasional errors each time the data refresh process is run such as: "The Web query returned no data. To change the query... etc." or "Unable to open... etc."
When these occur, a dialouge box with an "OK" button pops up and the data refresh process stops until I click OK.

The inability to access data on a few pages is inconsequential to my application. I'm looking for an error handling option that would ignore these web page access errors and let the data refresh process run continuously through the long list of webpages to the end.
I don't want to delete or edit the web queries either because the errors are often transient.

Anybody have a suggestion?

**instarac** · 12-01-2011, 08:11 PM

Where's the help?

**abousetta** · 12-01-2011, 08:52 PM

Could you provide more information like are you doing this manually or using vba? Which websites? Maybe a dummy workbook?

All these can help someone who might be interested in your problem is assisting you.

abousetta

**instarac** · 12-03-2011, 11:19 AM

The web queries were created in the Excel workbook via a VBA macro - see Macro8 in the attached workbook, B3.xlsm. This is only done once.

For all updates, the "Refresh All" operation in the Excel Data menu is used. As I've said, the refresh process becomes interrupted occasionally requiring manual intervention and I'm looking for a way to tell Excel to ignore the occasional errors and complete the entire sequence of web queries.

**abousetta** · 12-03-2011, 01:22 PM

Maybe try this:

Sub test()

Dim myarray, i As Integer, conString As String

On Error Resume Next
    
    myarray = Array("A", "AAPL", "ACH", "ACLS", "ACTS", "ADI", "ADTN", "ADY", "AEIS", "AERL", "AFOP", _
                    "AGYS", "AIXG", "AKAM", "ALLT", "ALN", "ALOG", "ALTR", "ALU", "ALVR", "AMAP", _
                    "AMAT", "AMBO", "AMCC", "AMCF", "AMCN", "AMD", "AMKR", "AMSC", "AMT", "ANAD", _
                    "ANEN", "AOB", "AOSL", "APH", "APKT", "ARMH", "ARRS", "ARUN", "ARW", "ARX")

    For i = 0 To 29
        
        Worksheets(myarray(i)).Activate
        If Err.Number <> 0 Then
            Sheets.Add After:=Sheets(Sheets.Count)
            ActiveSheet.Name = myarray(i)
        End If
            
        conString = "URL;http://finance.yahoo.com/q/ks?s=" & ActiveSheet.Name & "+Key+Statistics"
        
        Worksheets(myarray(i)).Cells.Clear
        
        With Worksheets(myarray(i)).QueryTables.Add(Connection:=conString, Destination:=Range("$A$1"))
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = """yfncsubtit"",8,10,11,13,15,17,19,21,23"
            .Refresh BackgroundQuery:=False
        End With
        
    Next i

End Sub

It will automate the process via vba. When it hits a snag it should should continue running until it stops. I guess you can tell which sheets had a problem by looking at the summary sheet and re-running those specific sheets again.

Hope this helps.

abousetta