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

Paolo Crossi
Paolo Crossi used Ask the Experts™
on
I'm grasping data from a web site via Excel and VBA.
It is an intranet site, so, unfortunately, it is not surfable from outside the firm.

Now, I'm in a deadlock dealing with confirmation alerts.

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


As you can see above, I've to click on determined rows of a report: clicking on it, in the export section of the site I will be able to download .xls files conteining any single record.

The problem is, when the macro reaches the line
 
ele.Click

Open in new window

I've to deal with two consecutive popups:
1) the first only needs confirmation (Would you like to export the records (.xls)?);
2) about the second (Request number: '" + nNum.toString() + "'.\n\n Download from Export section.");, I have to confirm it and capture in some way the number of request to refer to it when I've to download the files in the export section (I can use a sheet, i.e. "Sheet2", to store the number of requests).


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

Open in new window




As follows the functions in the HTML code:

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);
            }
        }

Open 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.");
        }

Open in new window


Can you support me?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018

Commented:
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.
Paolo CrossiAdministrative employee

Author

Commented:
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

Open 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);
            }
        }

Open 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.");
        }

Open 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.
Top Expert 2014

Commented:
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

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Paolo CrossiAdministrative employee

Author

Commented:
@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

Open in new window

?
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
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.
Top Expert 2014

Commented:
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.
Paolo CrossiAdministrative employee

Author

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

Author

Commented:
@Fabrice Lambert: You mean something like this?

https://codingislove.com/http-requests-excel-vba/
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
@Paolo Crossi:
Precisely.

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

Author

Commented:
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
Top Expert 2014

Commented:
You are correct. It doesn't click anything.  I only corrected the logic of your If statement.
Paolo CrossiAdministrative employee

Author

Commented:
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)

Open 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")

Open in new window

               
I need some further guidelines to go ahead.
Fabrice LambertConsulting
Distinguished Expert 2017

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial