Web Query Fails - Returned No Data

poolhall

Active Member
Joined
Jan 9, 2009
Messages
350
I have a little VBA script for Excel that I've been using for about 6 months without any problem until yesterday. The script uses a web query, and after it failed, I started debugging it and found that the query retrieves nothing.

Then I tried to manually use the Web Query Wizard in Excel and got this error: "This web query returned no data. To change the query, click OK, click arrow on the name box in the formula bar, click the name of the external data range for the Web query, right-click the selection, and then click Edit Query".

The page I was trying to retrieve is this: http://www.boxrec.com/list_bouts.php?human_id=14793&cat=boxer . Other pages from this website are also impossible to retrieve with a web query, though they look the same for me as before. I tried to play with web query setting but to no avail; still getting the same web query returned to data error.

Another user of the this VBA script is getting the same error.

Any ideas what causes the error and how to fix it?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
I think the failure is because the cache-control header returned by the web server includes 'no-store, no-cache', which means that Excel (using Internet Explorer internally) doesn't cache the web page on your local drive and therefore there is no local data for the web query to retrieve. With you saying that the web query worked fine until recently it looks like the web site/host owners have changed this cache setting for some reason.

Running the following macro will display the server headers:
Code:
Sub Show_Server_Headers()
    Dim request As Object
    
    Set request = CreateObject("WinHttp.WinHttpRequest.5.1")
    request.Open "GET", "http://www.boxrec.com/list_bouts.php?human_id=14793&cat=boxer", False
    request.send
    MsgBox request.getAllResponseHeaders, , "All Headers"
    
End Sub
There may be a way to override the cache-control settings in IE, but I doubt it. Alternatively, have a look at this code which shows how to extract a HTML table using VBA: http://www.mrexcel.com/forum/showthread.php?t=390072
 
Upvote 0
John w, thanks a lot.

You know it seems the cache control settings is not a problem here. To verify that, I saved the source of the page as an htm document and pointed the Excel Web Query Wizard to the local file. Still got the same error!

So I started looking at the page source and saw that they have a static flash ad at the very top of the webpage and this flash was contained into the < body > < / body > html tags. I just removed those tags and was able to retrieve the local web page into Excel!

But the question now is how I should proceed at this point? I don't really want to use the method of extracting HTML tables, that you pointed me to, because the web pages I work with have different number of tables every time and it would require a lot of code changes.

Is it possible somehow to save web page source locally, delete those tags from it, and then to retrieve the page content?
 
Last edited:
Upvote 0
I was able to resolve it.

In case someone need will need it, here's the macro:

Code:
Dim Name As String
Function SpecialFolderPath() As String
     
    Dim objWSHShell As Object
    Dim strSpecialFolderPath
    
    On  Error GoTo ErrorHandler
    ' Create a shell object
    Set objWSHShell = CreateObject("WScript.Shell")
    '  Find out the path to the passed special folder,
    '  just change the "Desktop" for one of the other options
    SpecialFolderPath = objWSHShell.SpecialFolders("Desktop")
    ' Clean up
    Set objWSHShell = Nothing
    Exit Function
ErrorHandler:
     
    MsgBox "Error finding " & strSpecialFolder, vbCritical + vbOKOnly, "Error"
End Function

Sub GetAndFixWebPage()
    Dim FileNum As Long
    Dim FileData() As Byte
    Dim MyFile As String
    Dim WHTTP As Object
    
    BoxrecURL = InputBox(Prompt:="Enter Boxrec URL for the boxer")
    If BoxrecURL = "" Then
        Sheet1.Select
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
        
    With Application.WorksheetFunction
    Name = .Substitute(BoxrecURL, "http://www.boxrec.com/", "")
    End With
'    Debug.Print BoxrecURL
    
    On Error Resume Next
        Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
        If Err.Number <> 0 Then
            Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
        End If
    On Error GoTo 0
    
    MyFile = BoxrecURL
    
    WHTTP.Open "GET", MyFile, False
    WHTTP.Send
    FileData = WHTTP.ResponseBody
    Set WHTTP = Nothing
    
    FileNum = FreeFile
    Open SpecialFolderPath & "\temp12345.htm" For Binary Access Write As #FileNum
        Put #FileNum, 1, FileData
    Close #FileNum
    
    Dim fso As Object
    Dim fil As Object
    Dim txt As Object
    Dim str_change As String
    Dim FileName As String
    FileName = SpecialFolderPath & "\temp12345.htm"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fil = fso.getfile(FileName)
        Set txt = fil.OpenAsTextStream(1)
        str_change = txt.ReadAll
        txt.Close
        str_change = Replace(str_change, "", vbNullString, , , vbTextCompare)
        str_change = Replace(str_change, "", vbNullString, , , vbTextCompare)
        With fil.OpenAsTextStream(2)
            .Write str_change
            .Close
        End With
    
End Sub
This will save a web page on the Desktop and remove from its source the misplaced HTML tags body, which were preventing the web page from importing to Excel. After it's done, I use regular Web Query to import data the local web page.

Thanks all those people who I took their code from :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top