VBA Web Query Returns Blank Page

[burns14hs]

Hello All -

I'm having a problem and hoping the smart people in this forum can help me fix it. I'm attempting to use VBA to pull data with a web query. When I first ran the macro, everything worked fine. Then I changed the date (which changes the variables in the url to pull different data) and it returned a blank page. Changing the date back to the original worked again. Then I tried a handful of other dates (including the one that didn't work earlier) and they all worked fine. I think I've got everything solved. Save and close, reopen, and nothing works again... just pulling a blank page for data. If I copy the url string and paste it into IE, I never get a blank page, just get the data that I'm looking for. Any ideas on the problem and how to fix it? Unfortunately, the website is internal so there isn't much sharing I can do to help you guys recreate the issue.

Sub Build_WQ()

'Delete all Web Queries
    Call Delete_WQ
'Holds web query link
    Dim WQ_Hx As String

'Turn off Screen Updating
    Application.ScreenUpdating = False
    
'Status Bar Message
    Application.StatusBar = "Be patient while web query loads..."

'Clears the contents of PPR Sheet
    Worksheets("Gensuite Pull").Cells.Clear
       
'Built Web Query Link Address for hour that corresponds with loop
        WQ_Hx = Sheets("Hidden").Range("A1").Value

'Creates WebQuery based on link value in WQ_Hx variable
            www = "URL;" & WQ_Hx
            With Worksheets("Gensuite Pull").QueryTables.Add(Connection:=www, Destination:=Worksheets("Gensuite Pull").Range("A1"))
                .Name = "WebQuery"
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .RefreshOnFileOpen = False
                .BackgroundQuery = False
                .RefreshStyle = xlOverwriteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .Refresh BackgroundQuery:=False

            End With
 
'Status Bar Message
    Application.StatusBar = "Building Gensuite Data Tables...Complete"
'Reset Status Bar
    Application.StatusBar = False
    

End Sub

The only other idea I have that could be causing the problem is that when I paste the url manually into IE, the page redirects twice before showing the data back on the original url again. I feel like this could be the cause of the problem, but doesn't seem to explain why it was working (mostly) during testing and not working later. Any help is much appreciated.

 

[burns14hs]

The only other idea I have that could be causing the problem is that when I paste the url manually into IE, the page redirects twice before showing the data back on the original url again. I feel like this could be the cause of the problem, but doesn't seem to explain why it was working (mostly) during testing and not working later. Any help is much appreciated.

As a follow up to this statement above, I have added

.WebDisableRedirections = "  "

to my code using both True/False and neither seems to help

 

[burns14hs]

Further Update:

If, upon opening the workbook, I attempt to run the macro it returns a blank page still.

If, upon opening the workbook I manually select Sheets("Gensuite Pull").Range("A1") which is where the webquery is stored and manually right-click-> edit query-> import then I can run the macro at will with any date changes and it will work until I close/reopen the workbook.

If, upon opening the workbook I run a macro to refresh the existing query (pulled by recording myself doing the above manually):

    Sheets("Gensuite Pull").Select
    Range("A1").Select
    With Selection.QueryTable
        .Connection = "URL;" & www
        .CommandType = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = False
        .WebConsecutiveDelimitersAsOne = False
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

I error out on the line .CommandType = 0

I can delete that line and rerun the macro but the webquery returns the results of "Note: Since your browser does not support JavaScript, you must press the Continue button once to proceed." When I dig deeper into what is happening there, it is that when manual, it reopens to the previous url, but when using webquery it opens to a "home" url which has some java incompatibility issues apparently and requires an "OK" button click to proceed.


I'm really at a loss here for any potential solutions anymore other than manually importing the web query since the automation appears to not work at all. I'd really appreciate if the smartest person in the world would read this and help me out

 

[burns14hs]

Hello All -

I'm bumping this back up to the top as I believe I have finally tracked down a root cause to the issue and maybe somebody has a solution for that. When using Excel 2013 and running a Web Query, the default browser that it uses is IE7 which is too old to support the newer java and flash required by my website. Does anybody know how to change the default Web Query browser to IE11 instead? I haven't had much luck in using Google to find an answer

 

[John_w]

I don't think you can change the Web Query browser.

Instead, you probably need to use IE automation and HTMLDocument parsing to import the data. You could also try programming the XMLhttp/WinHttpRequest object. This forum should have many examples of both approaches.

 

[burns14hs]

Thanks John_w... you helped me to redirect focus and use ie automation instead of a web query to fix the issue.