Passing arrays to and from a script function in a webpage using VBA

Question

Hi I am trying to import data from the webpage http://www.investing.com/indices/us-spx-500-futures-historical-data What I would like to do is a macro that navigates to this webpage and select a different date range from the calendar.

I do not understand how to set the Date in the calendar. I can open it but I am not able to "replicate" the click of the mouse to select the date The code I developed up to now is:

Sub Problem()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.application")
IE.Visible = True
IE.navigate "http://www.investing.com/indices/us-30-historical-data"
'
PauseTime = 4 ' Set duration to wait in seconds.
Start = Timer ' Set start time.
Do ' While Timer < Start + PauseTime
DoEvents ' allow other processes to work (the browser to have time to load the webpage)
Loop Until IE.ReadyState = READYSTATE_COMPLETE Or Timer > Start + PauseTime
IE.Document.getElementByID("datePickerIconWrap").Click
'
End Sub

I looked in the source code of this page and I found two interesting functions, the DatePickerGetDate and DatePickerSetDate I tried to run both the scripts using

IE.Document.parentWindow.execScript "widgetHolCalendar.DatePickerGetDate", "javascript"
IE.Document.parentWindow.execScript "widgetHolCalendar.DatePickerSetDate", "javascript"

No error message is given by the code but nothing is changed so I am not sure that the code is really executing something. If I had correctly understood the code, to set a new date I have to call DatePickerSetDate with 2 arguments

DatePickerSetDate(date, shifTo)

where date is an Array of 2 elements and shifTO is a boolean. I have no idea on how to pass Arrays to this a script using vba.

Also, when I call the function DatePickerGetDate I would like to get the result and save in a vba Array

Can anyone help?

If am understood you right, you want do develop an IE Add-On with VBA? Well, at your problem too hook the click is simple: Just inject JavaScript code in the current focused tab (which should be your just opened page). I think with IE.Document.parentWindow.execScript you can insert JS Code into the current tab. You have also divide into content scripts and background scripts (= in this case your VBA application). I think you should learn more on how about developing browser Add-Ons. — alpham8
– alpham8, Commented Jan 15, 2014 at 21:37
Sorry alpham8, I am not sure to understand. What I want to do is to change the date in the calendar in that page using a macro in excel. I do not want to develop an Add-on. As for using JS Code in execScript, can you give a simple example on how to do something like that? — MeSS83
– MeSS83, Commented Jan 15, 2014 at 22:16
Oh sorry, Is misunderstood you. You can only use the execScript function in JS if you are in a browser extension context. In past I developed macros using VBA for MS Office, too. And I have never heard about a method to use JavaScript implicit in VBA code. I think you are mixing up two different things. If you found JS code on this page, that doesnot have something to do with an Excel VBA macro code... What calendar do you mean? Is that something like drawing an chart from input data passed throw an wizard in Excel??? — alpham8
– alpham8, Commented Jan 15, 2014 at 22:36
If you open the webpage there is a box with a date range above the table: if you click on that box you can select a different range for the date, let say that instead of 12/16/2013-01/15/2014 you need 11/16/2013-01/15/2014. Once you select the new range for the date you can use the apply button to reload the table. What I need to do is to automatize this process. I need to find some way to select the new date range. — MeSS83
– MeSS83, Commented Jan 15, 2014 at 22:49
OK now. So, your logic doesnot work correctly at the moment. I know my advice is against the rules, but: Have you ever thought instead of using VBA only use JavaScript with the jQuery and jQuery UI framework? With this (and the plugin jQuery datatables) you are able to do all what you wished to do. Two datepickers for the date range, the datatables plugin (www.datatables.net) for the data in the table are shown and an apply button with an onclick event. With datatables you are able to reload your data content in periods or in user fired events with POST parameters to get the right output back. — alpham8
– alpham8, Commented Jan 15, 2014 at 23:00

Tim Williams · Accepted Answer · 2014-01-16 05:06:01Z

3

This worked for me (picking up from when the page has fully loaded...)

Dim f As String

'set the new dates (you just need to plug the required strings in here...
f = "$('#widgetHolCalendar').DatePickerSetDate(['11/05/2013', '11/12/2013'], true);"
IE.document.parentWindow.execScript f, "jscript"

'trigger the page function which refreshes the table
f = "historical_submit();"
IE.document.parentWindow.execScript f, "jscript"

answered Jan 16, 2014 at 5:06

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MeSS83 Over a year ago

Thanks Tim, it worked perfectly for me, I spent a weeks trying to solve this problem!

alpham8 · Accepted Answer · 2014-01-16 01:52:33Z

Here is a customizable code example about my advice above:

<!DOCTYPE html>
<html>
<head>
    <title>Playing with Data in a Date range</title>
    <!-- load the jQuery CSS first. The order is important here. Css first, then jQuery, then ui and at last plugins
    remove the ".min" if you are on development environment (this let gets you human readable JS Lib Code) -->
    <link rel="stylesheet" type="text/css" href="http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css" />
    <script src="http://code.jquery.com/jquery-2.0.3.min.js" type="text/javascript"></script>
    <script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js" type="text/javascript"></script>
    <!-- load the datables plugin -->
    <script src="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js" type="text/javascript"></script>
    <script type="text/javascript">
        var oTable;

        function applyChangesAction(sender)
        {
            // TODO: Retrieve the data from the server and render it into the table
            // they are many ways to do it with datatables. Here is a good
            // discussion about it: http://datatables.net/forums/discussion/comment/26734

            // after doing that you should be able to refresh the whole table by simply calling this below:
            //oTable.fnReloadAjax();
        }

        $(document).ready(function()
                        {
                            // >>> BEGIN DATEPICKER LOGIC <<<
                            var iOneDayInMs = 60 * 1000 * 60 * 24;

                            $("#dtPickTo").datepicker(
                                                    {
                                                       "setDate": new Date(),
                                                       "maxDate": new Date()
                                                    }
                                            );
                            $("#dtPickFrom").datepicker(
                                                        {
                                                           "maxDate": new Date($("#dtPickTo").datepicker("getDate").getTime() - iOneDayInMs)
                                                        }
                                            );
                            // >>> END DATEPICKER LOGIC <<<

                            // >> BEGIN DATATABLES LOGIC <<
                            oTable = $("#dataTabPrices").dataTable(
                                                                    {
                                                                        "bSortClasses": false,
                                                                        "bProcessing": true,
                                                                        "bServerSide": true,
                                                                        "sAjaxSource": "http://path/to/your/json/return_script.php",
                                                                        "fnServerData": function(sSource, aoData, fnCallback)
                                                                                        {
                                                                                            // put your filter criteria data in this object below to get the requested results:
                                                                                            var oaData = { "dtPickFrom": $("#dtPickFrom"),
                                                                                                            "dtPickTo": $("#dtPickTo")
                                                                                                        };
                                                                                            $.ajax(
                                                                                                   {
                                                                                                    "dataType": "json",
                                                                                                    "type": "POST",
                                                                                                    "url": sSource,
                                                                                                    "data": JSON.stringify(aoData), // your data filtering criteria for the server side script to return only requested data
                                                                                                    "success": fnCallback // closure callback if all data is received and ready to be rendered into the table (refresh logic for button click)
                                                                                                    }
                                                                                            );
                                                                                        }
                                                                    }
                                                );
                            // >> END DATATABLES LOGIC <<
                        }
                    );
    </script>
</head>

<body>
    <p>
        <label for="dtPickFrom">From</label><input type="text" id="dtPickFrom" name="dtPickFrom" /><br />
        <label for="dtPickTo">Until</label><input type="text" id="dtPickTo" name="dtPickTo" /> <br />
        <input type="button" id="btnApplyChanges" name="btnApplyChanges" value="Apply" onClick="applyChangesAction(this);" />
    </p>
    <hr />

    <table id="dataTabPrices">
        <thead>
            <tr>
                <th>Date</th>
                <th>Last</th>
                <th>Open</th>
                <th>High</th>
                <th>Low</th>
                <th>Change &#37;</th>
            </tr>
        </thead>
        <tbody>
        </tbody>
    </table>
</body>
</html>

This is not finished, yet! It´s just a basic example on how you should do it. Read my comments, to see exactly what´s missing right now, to get it fully working. My code is tested and it works in that it could work so far...

I hope this helps!

Thanks alpham8 for all the time you spent for this code I am going to try to complete your code and I will let you know, it is very interesting, thanks again!
:-) No problem. That keeps me fit in JS coding ;-) You should please also look, if the licenses from those libraries are compatible with your use. And I also forgot to mention, that this solution is only for fat clients, not for the thin ones. In thin clients I would outsource the data working logic on the server. So this code could cause long execution times on smartphones and other mobile devices. I actually don´t know your end device scope. If your scope is a normal computer or tablet, then this code is ok. But for thin clients you should outsource some code from JS to PHP.

Collectives™ on Stack Overflow

Passing arrays to and from a script function in a webpage using VBA

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related