Open IE and Interact with Web Form using Excel VBA

Question

I'm trying to create an excel spreadsheet with a landing page where I input a specific week number, period number and/or year.
I would then click a button to open an instance of Internet Explorer, navigate to the web form, input the data from the cells in the spreadsheet and then load up the reports selected.

Code for the web form





















Performance Reporting



















  





  
    
    Store Performance 
  















  
  


 Reporting Selection




  
  
      
          
            
            

          

          
            
            
            
            
            
          
    
      
          
           Selected Location: 1111
              
          
            
              Yesterday
               
                           
               
               
          
          
            
              
              
                            
              
              
          
          
            
              
               
                           
               
              

          
          
            
              
              
                            
              
              
          
          
            
              Week
               
                           
              Enter Week 
              

          
          
             
               
                           
               
              Enter Financial Year 
              

          
          
            
              
              
                            
              
              
          
          
            
              
            
              Period
               
                           
              Enter Period 
              

          
          
             
               
                           
               
              Enter Financial Year 
              

          
         
            
              
              
                            
              
              
          
         
            
              
              
                            
              
              
          
          
            
              Year 
               
                           
              Enter Financial Year

VBA code

Sub Get_Data()
    Dim i As Long
    Dim IE As Object
    Dim objElement As Object
    Dim objCollection As Object
    Dim HWNDSrc As Long
    Dim dates As Object
    
    Set IE = CreateObject("InternetExplorer.Application")
    
    With IE
        .Visible = True
        .navigate "http://INTRANETSITE/Summary_Select.asp"
    
        Do Until .readyState = 4 And Not .Busy
            DoEvents
        Loop
    
        .document.all.Item("radioGroup1")(3).Checked = True   'Select the 4th checkbox down on the list
        .document.forms(0).submit                             'Submit the form to generate the result
    End With
    
    Pause (2)
        
    With IE
        .navigate "http://INTRANETSITE/ReportDetail2.asp?"
        
        Do Until .readyState = 4 And Not .Busy
            DoEvents
        Loop
    End With
End Sub

The code above navigates to the web form and does select the 4th radio button which is for period reports, then submits the form and displays the first page.

How do I interact with the text boxes to also amend which week or period I want to select reports for?

Also, when it tries to navigate to ReportDetail2.asp it returns the following error and then crashes the VBA code.

Method 'Navigate' of object 'IWebBrowser2' failed

The long term goal of this is to navigate through 20+ pages of reports, pulling the data from each table into a tab on the spreadsheet, so if it can't navigate between the reports, even before we get to the code to pull the data, then that's a problem.

QHarr · Accepted Answer

Try something like the following. I am showing the different options you might consider so remember to comment out those you don't want to use.

You need to add a reference to Microsoft Internet Controls via VBE > Tools > References or use late bound call of

Dim ie As New InternetExplorer : Set ie = CreateObject("InternetExplorer.Application")

VBA:

Option Explicit
Public Sub MakeSelections()
    Dim ie As New InternetExplorer
    Const URL As String = "yourURL"
    With ie
        .Visible = True
        .navigate URL

        While .Busy Or .readyState < 4: DoEvents: Wend

        With .document
            .querySelector("[value='1']").Checked = True 'Yesterday. CSS attribute = value selector
            .querySelector("[value='3']").Checked = True 'Week
            .querySelector("[value='4']").Checked = True 'Period
            .querySelector("[value='6']").Checked = True 'Year
            .getElementById("week").Value = 6 'week value
            .getElementById("weekyear").Value = 2018 'financial year value
            .getElementById("period").Value = 6 'period value
            .getElementById("periodyear").Value = 2018 'period financial year value
            .getElementById("yearyear").Value = 2019 'financial year value
            .querySelector("[value='Display Report']").Click 'display report
        End With
        Stop '<== Delete me later
        '.Quit '<== Remember to quit application
    End With
End Sub

Open IE and Interact with Web Form using Excel VBA

Answers (1)

Related Questions