1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Web VBA issue

Discussion in 'VBA Macros' started by sms2luv, Jul 14, 2017.

  1. sms2luv

    sms2luv Member

    Messages:
    284
    Hi, I am making a VBA code to open a site to get some data and need help.

    Its a intranet site
    The site has a certifical error, so every time we open the site, we have to click on proceed to site anyway.

    The site then have a agree button for terms and conditions.

    Code I used is as below.
    Ms internet controls and ms HTML references are added.
    Code (vb):

    Dim ie as new shwdoc.internetexplorer
    Dim htmldoc as mshtml.htmldocument
    Dim htmlinput as mshtml.ihtmlelement

    ie.visible = true
    is.navigate "site name"

    Do while readystate <> readystate complete
    Loop

    Set htmldoc = ie.document

    'For clicking proceed to site
    Set htmlinput = htmldoc.getelementbyid("override")
    htmlinput.Click

    'For clicking I Agree
    Set htmlinput = htmldoc.getelementbyid("btnagree")
    htmlinput.Click

    End sub
     
    When I run this, a new ie opens and I am directed to I Agree page, which means proceed to site is working.
    But for some reason its not able to click on I Agree

    Please help
  2. sms2luv

    sms2luv Member

    Messages:
    284
  3. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,577
    From what you described. After below operation...
    Code (vb):
    Set htmlinput = htmldoc.getelementbyid("override")
    htmlinput.Click
     
    A new IE opens. Correct?

    That means you need to set new instance of IE as htmldoc before you can use it. Or you are just using previous page and code won't find "btnAgree" in the doc.
  4. sms2luv

    sms2luv Member

    Messages:
    284
    Sorry to say, but override is a hyperlink in the page and it says proceed to the site anyway.
    Image attached as sample.

    When I click on proceed anyway, I get I agree page.
    This does not open a new page.

    Attached Files:

  5. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,577
    Hmm? When you print entire content of htmldoc, "Before" you click on override. What do you get? And what do you get after you click on override?

    I ask, since the php/html you posted does not have elementid "override" in it.

    Edit: Also, I'd recommend installing certificate (get IT to do it for you if needed) or fix it by adjusting computer time and/or by deleting the site from Untrusted Publishers
    Last edited: Jul 14, 2017
  6. sms2luv

    sms2luv Member

    Messages:
    284
    As soon as ie opens, I get a message that the site has a certificate error (example image attached earlier).
    After 1 sec I get a page to agree to the terms and after that nothing happens.

    I used ie to inspect the page, and when I highlighted the "proceed to site anyway" I got an id named override.
  7. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,577
    I get that. That's not the issue.

    You are setting htmldoc = ie.document before you click override. Yes?

    You then click on override. Then it loads the page. Now, htmldoc is before the click, so isn't updated to newly loaded page.

    You have to wait for new page to load, then reset the htmldoc.
  8. sms2luv

    sms2luv Member

    Messages:
    284
    Thanks a lot for faster response.
    I tried the below thing, still did not work.
    Entire code was same, but I added
    Code (vb):
    Dowhile readystate <> readystate complete
    Loop
    After clicking on override.
    I mean above I agree button to reload the page completely.
  9. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,577
    But then you need to Set htmldoc = ie.document after clicking override as well. Since the content has changed after the click.
  10. sms2luv

    sms2luv Member

    Messages:
    284
    I tried the below code, but did not work, please help
    Error is Run time error 91
    Object variable or with block variable not set
    Htmlin2.click gets highlighted for debug

    Code (vb):


    Option Explicit


    Sub test()

    Dim ie As New SHDocVw.InternetExplorer

    Dim htmldoc As MSHTML.HTMLDocument

    Dim htmlin As MSHTML.IHTMLElement

    Dim htmldoc2 As MSHTML.HTMLDocument

    Dim htmlin2 As MSHTML.IHTMLElement


    ie.Visible = True

    ie.navigate "Site Name"


    Do While ie.readyState <> READYSTATE_COMPLETE

    Loop



    Set htmldoc = ie.document

    Set htmlin = htmldoc.getElementById("overridelink")

    htmlin.Click


    Do While ie.readyState <> READYSTATE_COMPLETE

    Loop



    Set htmldoc2 = ie.document

    Set htmlin2 = htmldoc.getElementById("btnAgree")

    htmlin2.Click


    End Sub

     
    Pasting fresh website code again, might be it had changed.
    https://www.pastiebin.com/596a37c2dc23f
  11. sms2luv

    sms2luv Member

    Messages:
    284
    Please help me team
  12. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,577
    When you print htmldoc2 to text file what do you get?
  13. sms2luv

    sms2luv Member

    Messages:
    284
    Hey team,
    I tried the below and it worked.
    Didn't understand why?
    Code (vb):

    Set htmldoc2 = ie.document

    Set htmlin2 = htmldoc.all.item("btnAgree")

    htmlin2.Click


    EndSub
     
  14. sms2luv

    sms2luv Member

    Messages:
    284
    The code only worked once and failed now.
    Please let me know, what should I print using Debug.print
    Appreciate if I can get the code.
  15. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,577
    Not Debug.Print, but write to Text File.

    Stick below code after you set htmldoc2. It will export innerHTML to text file to the same folder as your workbook. Upload it once done.

    Code (vb):
        Dim intFF As Integer: intFF = FreeFile()
        Dim oPath As String: oPath = ThisWorkbook.Path & "\" & "html2.txt"
        Open oPath For Output As #intFF
        Print #intFF, htmldoc2.Body.innerHTML
        Close #intFF
  16. sms2luv

    sms2luv Member

    Messages:
    284
    Hi Team,


    I tried 2 Thing.


    (1)

    Text file was not created.

    Got runtime error 91, Object Variable or with Block variable not set, when I debugged it stopped on htmlin2.Click

    The page stopped on “I Agree” Page.



    Code (vb):


    Option Explicit



    Sub test()

    Dim ie As New SHDocVw.InternetExplorer

    Dim htmldoc As MSHTML.HTMLDocument

    Dim htmlin As MSHTML.IHTMLElement

    Dim htmldoc2 As MSHTML.HTMLDocument

    Dim htmlin2 As MSHTML.IHTMLElement

    ie.Visible = True

    ie.navigate "Site Name"

    Do While ie.readyState <> READYSTATE_COMPLETE

    Loop

    Set htmldoc = ie.document

    Set htmlin = htmldoc.getElementById("overridelink")

    htmlin.Click

    Do While ie.readyState <> READYSTATE_COMPLETE

    Loop

    Set htmldoc2 = ie.document

    Set htmlin2 = htmldoc.getElementById("btnAgree")

    htmlin2.Click


    Dim intFF As Integer: intFF = FreeFile()

     Dim oPath As String: oPath = ThisWorkbook.Path & "\" & "html2.txt"

     Open oPath For Output As #intFF

     Print #intFF, htmldoc2.body.innerHTML

     Close #intFF


    End Sub


     

    (2)

    Added (On Error Resume Next) and ran the code, text file created

    No Error Found, but it does not pass “I Agree”Page


    I inspected I Agree button and got it

    Code (vb):



    <p class="text-center">

      <input class="btn btn-login" id="btnAgree" onclick="logon.hidePolicy();" type="button" value="Agree">

      </p>

     


    REMOVED SITE NAME FROM ATTACHMENT.

    Attached Files:

  17. sms2luv

    sms2luv Member

    Messages:
    284
    @Chihiro
    Please look into the text attachment and check if something can be done.
  18. sms2luv

    sms2luv Member

    Messages:
    284
    Please please help me in this.
    I know there are several questions in the forums to answer.
    However I was stuck here, so requesting help.
  19. Marc L

    Marc L Excel Ninja

    Messages:
    3,141

    Hi !

    Without accessing site, help is very hard to bring …
  20. sms2luv

    sms2luv Member

    Messages:
    284
    Agreed to the point, however I have attached a text file which might help and have pasted link for source code
  21. Marc L

    Marc L Excel Ninja

    Messages:
    3,141

    Text file useless as not complete and without the related button …

    Better than click as you could see in samples over the Web try onclick.
  22. sms2luv

    sms2luv Member

    Messages:
    284
    OK
    I will try on click.
    Can I inspect the button and provide you the code
  23. Marc L

    Marc L Excel Ninja

    Messages:
    3,141
    As written, a piece is not enough to see how the webpage works !

    So if onclick does not work, I've no more concrete way
    as blind without a real access to the webpage …

    Just proceed a web search to try all possible ways
    as you can start with this forum where you will see some buttons
    not work in VBA with click statement but via other way …
    ThrottleWorks likes this.
  24. sms2luv

    sms2luv Member

    Messages:
    284
    OK Sir, thanks I will try and proceed.
  25. sms2luv

    sms2luv Member

    Messages:
    284
    I am not able to get the method of onclick

    Could you please help with Syntax.
    Is it onclick. Htmlinput

Share This Page