Excel and vba: surfing the web and dealing with problematic alert popups

If I understand, you're trying to write code to extract a single line from Excel document you're reaching via a URL.

This type of code will be very fragile if you can ever get it to work.

How I approach this type of task.

1) If no local copy of document, download entire document to a local cache.

2) If a local copy exists with a different timestamp than the remote document, download entire document to a local cache.

3) Display local cache version for selection of row(s) of data to extract.

4) Run LibreOffice in headless mode against the document to extract rows.

This approach produces easy to understand + modify code, especially #4 as the entire extraction part of code becomes one call to fire up LiberOffice with row(s) to extract.

A side effect of LiberOffice is simple conversion between various document formats also.

Sorry, I'm going to try being clearer.

I usually grasp data from website without great problems.

In this case is a  .aspx  site, maybe a little bit harder to manage, but anyway I achieve to surf and grasp a lot of data.

Now, the problem arises when my macro - this is just a snippet of the long code I've written - clicks in the rows of a table:

Dim elements As Object
    Set elements = HTMLdoc.getElementsByClassName("Row")
Dim ele As Object

    For Each ele In elements
        If ele.innerText = "AZ25" Or "AZ2Z" Or "AZ28" Or "AZA0" Or _
            "AZA1" Or "AZ49" Or "AZ51" Or "AZ58" Or "ZA7Z" Or _
            "AZ27" Or "AZ50" Or "AZ59" Or "AZ52" Then
            ele.Click
            Exit For
        End If
    Next ele

Select allOpen in new window

That click produces two consecutive alerts that block my process (At this moment I've to confirm OK manually, but, of course, I need to automate the entire process):
1) Would you like to export the records (.xls)?
2) Request number: 1234. Download from Export section .

So, my basic question is:
How can I automatically click OK to confirm the two messages?


Then, parsing the HTML code of the page, I've detected the two following functions: I believe they can help you to understand the nature of the two alerts, and this is why I'm reporting them here.

function RowSelected (sender, args) {

            var sSite = document.getElementById("cSite").control._value;
            if (sSite == "")
                sSite = 0;
            var sTeam = document.getElementById("cTeam").control._value;
            if (sTeam == "")
                sTeam = 0;
            var Export_Type = ".xls";
            if (confirm('Would you like to export the records (.xls)?')) {
                xmlDocF = new ActiveXObject("Microsoft.XMLDOM");
                xmlDocF.async = false;
                xmlDocF.load("azClient.xml")
                WebMethod(xmlDocF, "SaveExport")
                NewField(xmlDocF, "idMarket", document.getElementById("hidIdMarket").value)
                NewField(xmlDocF, "type", 'output')
                NewField(xmlDocF, "date_from", document.getElementById("DayPicker1").value)
                NewField(xmlDocF, "date_to", document.getElementById("DayPicker2").value)
                NewField(xmlDocF, "id_type", args.getDataKeyValue("code1"))
                NewField(xmlDocF, "idSite", sSite)
                NewField(xmlDocF, "idTeam", sTeam)
                NewField(xmlDocF, "idLogin", document.getElementById("hidLogin").value)
                NewField(xmlDocF, "Export_Type", Export_Type)
                NewField(xmlDocF, "idMarket", document.getElementById("hidIdMarket").value)
                NewField(xmlDocF, "level", '1')
                WsAdminService.wsadmin.GeneralMethodOutsourcer(xmlDocF.documentElement.xml, OnCompleteCreaExport, OnFail);
            }
        }

Select allOpen in new window

function OnCompleteCreateExport(result) {
            xmlDoc = new ActiveXObject("Microsoft.XMLDOM");
            xmlDoc.async = "false";
            xmlDoc.loadXML(result);
            x = xmlDoc.getElementsByTagName("ZZ1");

            var nNum = x[0].childNodes[0].getAttribute("VALUE");
            if (nNum > 0)
                alert("Request number: '" + nNum.toString() + "'.\n\n Download from Export section .");
            else
                alert("Double request: '" + -(nNum).toString() + "'.\n\n Download from Export section.");
        }

Select allOpen in new window

At the moment, we can overlook on capturing the request number: the important aspect is clicking OK in the two alerts and keeping on.

You need a Select Case statement:

Dim elements As Object
    Set elements = HTMLdoc.getElementsByClassName("Row")
Dim ele As Object

    For Each ele In elements
        Select Case ele.innerText
            Case "AZ25" , "AZ2Z" , "AZ28" , "AZA0" , _
            "AZA1" , "AZ49" , "AZ51" , "AZ58" , "ZA7Z",  _
            "AZ27" , "AZ50" , "AZ59" , "AZ52"
            ele.Click
            Exit For
        End Select
    Next ele

Select allOpen in new window

@aikimark: maybe now, with your valuable suggestion, the code is more efficient.

But the point is: how can I close the two popups coming up with any

ele.Click

Select allOpen in new window

?

Looks like instead of simulating a click on the element, you'll need to send a GET or POST request to the server, to gain access to the underlying document.

the code is more efficient

No.  That part of your code actually does what you want it to do.  Your original if statement did not do what you wanted.  Efficiency had nothing to do with my comment.

@aikimark: anyway, I've just tested your code and it doesn't click anything.

@Fabrice Lambert: You mean something like this?

https://codingislove.com/http-requests-excel-vba/

@Paolo Crossi:
Precisely.

You can even use the xmlHttp object to retrieve your first document instead of instanciating an internet explorer object.

Not easy at all, but maybe not impossible.

I'm working on these points, until now with few success.

1)      set up the login operation
2)       which URL do I have to consider as reference point? The home page? The page from where I manually get the request?  
3)       insert the parameters of the request (date_from/date_to/site/etc...)
4)        send the request
5)       manage the response

You are correct. It doesn't click anything.  I only corrected the logic of your If statement.

If I have to aim to a link, I can't find it.
Anyway, whatever I have to aim to, it doesn't seem something static.

I mean: the file is generated after my choices, it is not provided a priori.

Looking at the function at the post number 1 called function RowSelected , I atleast have to select:

                NewField(xmlDocF, "date_from", document.getElementById("DayPicker1").value)
                NewField(xmlDocF, "date_to", document.getElementById("DayPicker2").value)
                NewField(xmlDocF, "idSite", sSite)

Select allOpen in new window

Then the table is generated, and then clicking on the row that seems to correspond to this parameter the file number request is generated (see function OnCompleteCreateExport(result) in post number 1):

NewField(xmlDocF, "id_type", args.getDataKeyValue("code1")

Select allOpen in new window

               
I need some further guidelines to go ahead.

Guess you'll have to reproduce (more or less) what the JavaScript do.