dcsimg
Results 1 to 26 of 26

Thread: [RESOLVED] VBA click button in a webpage

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    19

    Resolved [RESOLVED] VBA click button in a webpage

    Hi experts..

    I am trying to spool one excel report from website by giving From date and To Date are as inputs iam able to give enter dates with my code but unable to clicking on export button

    Below is the HTML source code for Export Report Button
    HTML Code:
    <input type="submit" name="ctl00$ContentPlaceHolder1$btnExportXLS" value="Export Report" onclick="if(!Validate()) return false;" id="btnExportXLS" tabindex="3"class="button blue" onmousedown="return isTxtwithfocus(event, this)" style="width:101px;float: none; margin-top: 2px;">
    And this is my code
    Code:
    Sub User_Wise_Productivity()
    
    Dim From_Date AsStringDim To_Date AsString
    
    From_Date = InputBox("Enter report From date in (Ex: DD.MM.yyy)format")
    To_Date = InputBox("Enter report To date in (Ex: DD.MM.yyy)format")
    
    Set browser = CreateObject("internetexplorer.application")
    browser.Visible =True
    browser.navigate ("My URL")
    
    Do
    DoEvents
    LoopUntil browser.readyState =4
    
    browser.document.getElementById("txtUserName").Value ="Myid"
    browser.document.getElementById("txtPassword").Value =“Mypsw”
    
    browser.document.getElementById("selFund").selectedIndex ="21"
    browser.document.getElementById("btnSubmit").Click
    
    Do
    DoEvents
    LoopUntil browser.readyState <>4
    
    browser.navigate ("MY URL Page2")
    
    Do
    DoEvents
    LoopUntil browser.readyState <>4
    
    Dim reprt AsString
    reprt ="11"
    
    With browserSet Post =.document.getElementById("ddlrptlist")ForEach elem In Post.getElementsByTagName("option")If elem.Value = reprt Then elem.Selected =True:ExitForNext elem
    
    .document.getElementById("ddlrptlist").FireEvent ("onchange")
    
    EndWith
    
    Do
    DoEvents
    LoopUntil browser.readyState <>4
    
    browser.document.getElementById("txt_0").Value = From_Date
    browser.document.getElementById("txt_1").Value = To_Date
    
    Application.Wait (Now + TimeValue("0:00:02"))
    
    browser.document.getElementById("btnExportXLS").Focus
    browser.document.getElementById("btnExportXLS").Click
    
    EndSub

    last two highlighted lines for button...

    Any help greatly appreciated. thanks in advance.

  2. #2
    Hyperactive Member
    Join Date
    Aug 2016
    Posts
    293

    Re: VBA click button in a webpage

    maybe you can used browser.document.getElementById("btnExportXLS").submit to test.

    I can't test

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,752

    Re: VBA click button in a webpage

    What language is this? It doesn't look much like VBA.

    By the way, looping around a DoEvents is a busy wait, which is a terrible thing to be doing. In this case, it will only be for a few seconds, so it will just suck power briefly, but it's still a terrible thing to do. However, the better option depends on which language you are using.
    My usual boring signature: Nothing

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,882

    Re: VBA click button in a webpage

    It doesn't look much like VBA.
    application.wait looks like vba

    i would try like
    Code:
    set btn = browser.document.getElementById("btnExportXLS")
    btn.Click
    that way you can break and see if btn is a valid element object

    you could also try to submit the form containing the button

    as the button has a mousedown event, you may also need to fire that in addition to the click
    you need to look at what the form submit does and also the isTxtwithfocus event
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    19

    Re: VBA click button in a webpage

    Hi xxdoc123

    thanks for the reply.. this line of code is working fine.

    browser.document.getElementById("btnExportXLS").Click

    . not getting any error but excel report is not getting generate.. and there is no page load problem. if see at HTML code of button. there is onclick function**"Validate()"** in that. my guess iss after validating form with this function then only excel file will get spool

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    19

    Re: VBA click button in a webpage

    Hi Westconn1,

    thanks for the reply.. have tried your code, not getting error but excel file not getting generate.. there ia function " validate" in that.. please advise how to call or execute that function.

    Code:
    set btn = browser.document.getElementById("btnExportXLS")
    btn.Click

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    19

    Re: VBA click button in a webpage

    Hi Shaggy Hiker,

    I am looking for help about VBA, any help with the code is greatly appreciated.. thank you.

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    19

    Re: VBA click button in a webpage

    Hi Hi xxdoc123,

    i can share my macro file.. may be you'll come to know if you run it from you end.
    Attached Files Attached Files

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,882

    Re: VBA click button in a webpage

    my guess iss after validating form with this function then only excel file will get spool
    there is an example of fireevent in your original code, you can test this with the events for the button to see if any make a difference


    my guess some of the events are to prevent automated download of the files
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    19

    Re: VBA click button in a webpage

    Hi westconn1,

    please help with the code.

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,882

    Re: VBA click button in a webpage

    i tested some code, i was able to click the download button, but the download was blocked internet explorer

    if i clicked the button manually i was presented with a download open /save dialog which would also be difficult to automate

    you can test to see if it helps at all
    Code:
    Set btn = browser.document.getElementById("btnExportXLS")
    btn.fireevent ("onmousedown")
    btn.fireevent ("onclick")
    btn.Click
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    19

    Re: VBA click button in a webpage

    Hi westconn1,

    Thanks for the code, its working perfect now excel file is getting spooled. one more help on the same is any possibility to manipulate open /save dialog popup, i want to click on open button. thanks in advance.

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,882

    Re: VBA click button in a webpage

    i have successfully done this previously in vb6, you may need to have a separate process with a timer to find the window when it opens and click the button as required

    it may be possible to do this within vba using a timer, but i am not sure if the timer will fire while the file save is open as a modal dialog

    see if you can find any previous example in this forum

    edit:
    if you search on findwindow in this forum you will get many hits (some nothing to do with this topic, but many are related), with ideas and code that does or used to work, but no longer does, same applies to any code i post here, it may not work on later versions of internet explorer, which may use windowless controls, i have no idea what version you are using, or are likely to upgrade to at any time
    i doubt that you can get the dowload url directly to download the excel without the dialog
    Last edited by westconn1; Dec 10th, 2018 at 03:50 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    19

    Re: VBA click button in a webpage

    Hi Westconn1,

    Thanks for the reply, i am using send keys for the same, and iam able to click on open button with send keys but and its working but one problem after clicking open button
    Code:
    Application.DisplayAlerts = False
    is not working.

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,882

    Re: VBA click button in a webpage

    what alert are you getting?
    is it an excel alert or someother?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    19

    Re: VBA click button in a webpage

    Name:  Untitled.jpg
Views: 15
Size:  8.0 KB

    excel alert only

  17. #17
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,882

    Re: VBA click button in a webpage

    does the downloaded file open in the same instance of excel or a separate instance?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  18. #18

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    19

    Re: VBA click button in a webpage

    yes its open same instance of excel, and i have other code need to run after this file got open.. is ther any way once file open then only my rest of the code should run.

  19. #19
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,882

    Re: VBA click button in a webpage

    do you call displayalerts = false before downloading the file? as i did not see it in your original code i could not tell when you are calling it
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  20. #20

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    19

    Re: VBA click button in a webpage


  21. #21

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    19

    Re: VBA click button in a webpage

    after clicking on open button, immediately next line of coding is running immediately, due to this excel is not getting open

  22. #22
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,882

    Re: VBA click button in a webpage

    try moving your call to displayalerts, to the top of the procedure, before you try go to the website
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  23. #23

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    19

    Re: VBA click button in a webpage

    Hi westconn1,

    Thanks for the help, have run my code at your end,

    Have place displayalerts but nothing is got.



    oShell.SendKeys "%{O}" --- >>After this code, excel file should open but immediately below code is running



    Application.Wait (Now + TimeValue("0:00:01"))


    Dim ws As Worksheet --->> From this line code should start after excel open only
    Dim flg As Integer
    For Each ws In ActiveWorkbook.Worksheets
    If InStr(ws.Name, "User") Then
    ws.Activate
    flg = 1
    Exit For
    End If
    Next ws

    If flg <> 1 Then
    MsgBox "There is no -User Wise Productivity Detailed- sheet exist in this workbook"


    Exit Sub --- >> after existing sub excel is opening.
    End If
    Last edited by devendra.dvm; Yesterday at 07:05 AM.

  24. #24
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,882

    Re: VBA click button in a webpage

    i do not know if it will help, but you could try doevents after the sendkeys or possibly a timer delay (which is different to application.wait) either an API timer or application.ontime, to see if any of thosewill allow the workbook to load before the running further code

    with either sort of timer, you would have to move the last part of the code to the timer
    i would try doevents then applicaion.ontime
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  25. #25

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    19

    Re: VBA click button in a webpage

    Hi westconn1,

    Thanks for the help, its really appreciated.

    I have change my code bit, used send keys to click on save button instead of click on open button, then i will open last saved file from download folder, now its working fine... thanks a lot for the help and for your time.. please don't mind if i ask any help in future..

  26. #26
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,882

    Re: VBA click button in a webpage

    whatever works is good
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width