Automate javascript Confirm("Submit?") through VBA

[ramesh110289]

Had a query while coding VBA in excel. I am working on automating the inputs given to a web page and auto clicking the submit button to order reports.

I am able to successfully maneuver to the page, provide inputs and click on Submit button. But on clicking the submit (the web page is internally coded using javascript) there is a confirm(“Submit?”) which asks for clicking on “Ok” or “Cancel” for which the input needs to be OK by default. I have tried providing Application.SendKeys “{ENTER}” but it doesn’t seem to work since this is a totally new window.

Any pointers on how to automate this part would be really helpful.

 

[Chihiro]

Why not use XML to Get/Post form info? It will be faster and more stable than IE automation.

Refer to links below.
Sample code that I did for scraping table data (with form submission).
http://chandoo.org/forum/threads/wh...ils-in-website-through-vba.30640/#post-182735

VBScript, but same principle applies to VBA
https://support.microsoft.com/en-ca/kb/290591

General Web Scraping Tutorial
http://analystcave.com/web-scraping-tutorial/

 

[ramesh110289]

Thanks Chihiro Sir for your swift response. I truly agree XML is indeed a an agile way to go about.

The only limitation here is the website has three inputs to be provided and on clicking the submit button it in turn calls a javascript function (UISubmitAction()) without any inputs. Also within the javascript function the first line has this validation confirm("Submit?"). So technically it goes out of the scope of current webpage.

Would there be any way to handle this particular instance?

 

[Marc L]

Depends on the webpage, as they are several ways to click a button
or submit an action !

See samples from this forum and anywhere else …

Just see how many tab keys are necessary to activate button
to use SendKeys method …
Often when there is no result using SendKeys,
the coder did not well observe how the webpage works !

Another issue with piloting Internet Explorer :
a code working on a computer may not work on another one !
​

 

[Chihiro]

Without knowing the site, I can't give you specific advise.

But generally speaking, most javascript (related to form submission) is used to pass parameters to Web Service. If you debug using FireBug or similar tools. You can probably trace what parameters are sent to get response.

Edit: For clarity.

 

[Marc L]

I forgot to mention another way stronger than SendKeys
(but with same observation limitation and advanced skills level) :
UI Automation library …

Yes, without an access to the webpage, as blind we can't help further.
But with tutorials and samples everywhere on the Net …

As Chihiro yet wrote, better is to use a request than piloting IE
(or Chrome via its own tool or Firefox through selenium vba tool) …
​

 

[ramesh110289]

Thanks all for the quick responses. Your comments have been very useful.

When i was searching over the internet whether there had been any previous instances i could get an exact same replica of the situation in the below link,

https://social.technet.microsoft.co...ion-pressing-ok-on-a-confirm-popup?forum=ITCG

When i tried to replicate the solution provided i am not able to comprehend what wsh object is for. It gives me the error stating object is not defined.

Can anybody please help me to debug the same.

 

[Marc L]

Yes,
but without an access to the webpage,
as blind we can't help further.

Depends on the webpage, as they are several ways
to click a button or submit an action !

How many tab keys are necessary to activate button ?

Without knowing the site, I can't give you specific advise.

You can point out the form object and submit for example …

And your link is not for VBA ‼ See the last link of Chihiro …
​

 

[ramesh110289]

Thanks Marc.

I seem to have lost the plot. The issue here is even if i use XML the same issue is going to land up.

Let me put it in very simple terms. I am ordering a report from a webpage. I provide three inputs and press Submit button. When i click on View code and check the on click event of the submit button it redirects to a js function (UISubmitButton()).

Inside the js function the second line is confirm ("Submit?"). So when the dialog box pops up it just stays on. There is no way the focus is returning to the code.

<input type="button" id="UI_SubmitButton" value="Submit" class="ReportUIButton" onclick="UISubmitButton()">

Something like this inside the JS,

function UISubmitButton() {
if (lProcessing == true) return;
lProcessing = true;

if (confirm("Submit?")) {....}

How can i click Ok by default?

 

[Marc L]

As you don't ever reply to question neither give us the webpage link,
as you can activate a button by tab key (so easy with SendKeys),
as you do not join your actual code,
as you do not clearly expose the final purpose with Excel
(as for a report often the best way is a request catchable with
webbrowser inner inspector tool, see Chihiro's link),
as …

so all we can say is to point the element by its ID and
invoke its onclick property or run the JScript code associated
or just invoke click by running a JQuery code.
All this material is in many tutorials and forums about HTML / Web,
as here it's only an Excel forum …
​

 

[ramesh110289]

Thanks Mark..!! The webpage link is a restricted one..!! That's why i could'nt share. Moreover i am a novice in VBA developing. I just thought i could get some help on it..!!

Fact 1 : This is a webpage usually opened through IE.
Fact 2 : I have the element pointed by it's ID (Submit button) and it successfully goes inside the JS function
Fact 3 : Inside javascript function there is a confirm() method.

The question is how can you control something inside a JS function through VBA

Couldn't explain it much better.

 

[Chihiro]

From the looks of it the JS function is just confirming "OK" click and then sending parameter. So what you need to do is monitor "NET" section in FireBug or other similar tools and find POST or GET string.

Using https://www.amfiindia.com/net-asset-value as example...

You can see in image below that JavaScript was used to Post request using form (depending on the site, you'll need to set "Persist" so that you can trace multiple scripts)


Under Post section, you can see what string is needed to get desired response.


Under HTML you can find out the URL to post the request to.


So combining info, resulting code to get response would be something like....

    Dim xmlhttp As Object
    Dim PostData As String, response As String

    Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP")

    PostData = "MFName=0-53&OpenScheme=Growth&CloseScheme=Growth&IntervalFund="
    xmlhttp.Open "Post", "https://www.amfiindia.com/modules/NAVList", False
    xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    xmlhttp.Send PostData

    response = xmlhttp.responseText

 

[ramesh110289]

Thanks a ton Chihiro for all the efforts. I can certainly look to check and implement the solution.

Highly appreciate your help.!

 

[Marc L]

I totalllllllly agree with Chihiro's previous post
as it's the efficient way, just use the same request as webbrowsers do …

As it is not a VBA coding question but a Web / HTML skills subject,
the easy way for those whom do not know how to use their webbrowser
is first to observe how the webpage works, and in particular if after
some Tab key the button is activated and then just hit Enter key.
In this case, ♪ absolute beginners ♫ take the SendKeys way …

As often the tiny window to confirm any action is a child of IE window,
you can first find the handle of the child window (Windows API skills)
and invoke its button via UIAutomation library.
​

 

[ramesh110289]

Thanks Chihiro & Marc..!! I just tried implementing it. The javascript uses an AJAX Post method and due to the limitations i have in work place I am not able to use debugging softwares. I am not able to track the URL which is being posted.

I totally agree with the last paragraph which Mark had mentioned getting track of child window and then invoking the button. But the issue here is once it goes to the .js the focus is completely lost and it just waits for the user to provide input OK or Cancel..!!

 

[Marc L]

I never had a concern with Ajax, whatever with piloting IE or a request,
it's just a question of observation … And Ajax means a request backward !

Focus lost is not a concern if some Tab keys give focus on button …

Edit : I forgot a way in my previous post, instead of SendKeys,
pilot the mouse …
​

 

[Marc L]

due to the limitations i have in work place
I am not able to use debugging softwares

You misread : no question of any debugging software
but just webbrowser inner inspector tool, as yet explained in Chihiro's link !
Read also webbrowser inner help …
With an efficient request you do not need any way following under …​

once it goes to the .js the focus is completely lost

Several ways :

- Tab keys
- Click button by executing a JQuery (JScript)
- Invoke button via UIAutomation library
- Pilot mouse cursor
- After a while run a JScript code
- …​

For very beginners easy way is the keys : see if manually
with some Tab key hits, the focus comes to desired button,
then use SendKeys method …

If it is only for an unique specific computer, pilot mouse cursor.
Once you find right coordinates to place cursor on button,
just invoke mouse left click …

Last way is semi-automatic by using two procedures :
the fisrt one until window appears and the second to be launched
after button manually clicked …
​

 

[Marc L]

Microsoft material :

• Using the F12 developer tools
• Meet the Microsoft Edge Developer Tools

• Analyzing your webpage's network traffic …
​

 

[Marc L]

Any news : do you fix it ?
​