VBA need to input yesterdays date in a field in IE11

Hi John,

Try something like:

 
Dim myYesterday As Date

myYesterday = Date - 1#
Debug.Print Format(myYesterday, "mm/dd/yyyy")

HTMLDoc.all.ex1_inputE1.Value = Format(myYesterday, "mm/dd/yyyy")
HTMLDoc.all.ex2_inputE1.Value = Format(myYesterday, "mm/dd/yyyy")

Change the contents of the format statement to suit your web site requirements.

Lewis

Basic example, of course you can change the date formatting to whatever you want:

Sub Johnnyv015()

Range("A1") = Format(Now - 1, "mm/dd/yyyy")

End Sub

HTML Code: 

This is the code for the box I am trying to enter this in.

HTML Code: 

I think this is the full code for this area.

correct, there is a table cell for selecting the date, or you can just type in the date and click a submit button under this.

I was thinking that entering the date and clicking the button would be a lot easier than using the table.

In my playing with this Table Cell I have found that I can click on the Table and press the left arrow, this will always put me on the previous date. Enter after this will fill the box I am looking to fill with the date needed.

Think this may be the best way to get this done?

Only reason I am reluctant to post the entire page code is, it does contain many private html links. Is there a certain piece of this code you are looking for?

Code for boxes and Table Cells

HTML Code: 

Code for button

HTML Code: 

Originally Posted by Leith Ross

Hello Johhnyv150,

Here is the promised code. You will have to adapt this to your code. This lets you select the starting and ending dates and clicks the "Get Report" button.

After doing some testing, it seems that an Input object will default to Type "text" if the Type property is missing. Although I could not find it any HTML literature, it proved to be true through testing.

Sub GetReportMacro()

    Dim IE As Object
    Dim oInput1 As Object
    Dim oInput2 As Object
    Dim oSubmit As Object
    Dim URL As String
    
        URL = "*****************"    ' Change to the site you want to visit.
        
        Set IE = CreateObject("InternetExplorer.Application")
                    
            IE.navigate URL
            IE.Visible = True
            
            While IE.Busy And IE.readyState <> 4: DoEvents: Wend
            
                With IE.document
                    Set oInput1 = .getElementById("ex1-inputEl")    ' Starting Date
                    Set oInput2 = .getElementById("ex2-inputEl")    ' Endding Date
                    Set oSubmit = .getElementById("cmdSubmit")      ' Get Report
                End With
            
            oInput1.Value = "09/20/2014"
            oInput2.Value = "09/30/2014"
                
            oSubmit.Click
            
End Sub

I have tried using this code with the Input value of "Date() -1" but I am getting the runtime error 91 when entering the "With" statement.


Any ideas on how to solve this?

Thanks,
John

Thank You Leith Ross,

This is not working for what I am trying to do. I have come up with the code below, as I have found that by simply clicking the calendar box and pressing the left key then enter this will ALWAYS select the previous date.

as of now this is what I put in, but it is not working in the calendar box.

    For Each MyHTML_Element In HTMLDoc.getElementsByTagName("div")
                If MyHTML_Element.ID = "ext-gen1054" Then MyHTML_Element.Click: Exit For
            Next

      Interaction.SendKeys "{LEFT}", True
      Interaction.SendKeys "{ENTER}", True
      
      
      
      
      While MyBrowser.readyState <> 4: DoEvents: Wend
      
               For Each MyHTML_Element In HTMLDoc.getElementsByTagName("div")
                If MyHTML_Element.ID = "ext-gen1056" Then MyHTML_Element.Click: Exit For
            Next

      Interaction.SendKeys "{LEFT}", True
      Interaction.SendKeys "{ENTER}", True
      
          
          
          For Each MyHTML_Element In HTMLDoc.getElementsByTagName("input")
                If MyHTML_Element.Type = "submit" Then MyHTML_Element.Click: Exit For
            Next

Do you know of any way to make the sendkeys function act in the calendar box?

Thanks,
John

Note: I can select the calendar box, I am just having trouble getting VBA to act in this window when it is opened.

Any ideas?

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

Originally Posted by Leith Ross

Hello John,

The "With" statement assigns the HTML objects to VBA object variables. The date would be assigned to either"Output1" or "Output2". These are the start and ending dates respectively.

The date must be a string in the format mm/dd/yyyy.

For Example...

    Output1 = Format(Data() - 1, "mm/dd/yyyy")

Thanks Leith Ross,

I think I am missing something. I am using this.

            While MyBrowser.readyState <> 4: DoEvents: Wend
            
            With IE.document
                    Set oInput1 = .getElementById("ex1-inputEl")    ' Starting Date
                    Set oInput2 = .getElementById("ex2-inputEl")    ' Endding Date
                    Set oSubmit = .getElementById("cmdSubmit")      ' Get Report
                End With

                        
            oInput1.Value = Format(Date - 1, "mm/dd/yyyy")
            oInput2.Value = Format(Date - 1, "mm/dd/yyyy")
            
            oSubmit.Click

still pulling the runtime 91 when the code gets to "With IE.document"

Originally Posted by Leith Ross

Hello John,

Internet Explorer runs asynchronously. Which means what VBA executes is not in sync with Internet Explorer. That is why the first While loop is used.

This keeps VBA occupied until Internet Explorer has loaded the web page. However, a loaded web an HTML document does not make. There is a certain about of time needed to convert the text into their HTML counter parts.

It is not uncommon for the document not to have been converted by the time VBA executes the "With IE.Document" statement.

You can add another loop to check that the document exists before proceeding like this...

Sub GetReportMacro()

    Dim IE As Object
    Dim oInput1 As Object
    Dim oInput2 As Object
    Dim oSubmit As Object
    Dim URL As String
    
        URL = "*****************"    ' Change to the site you want to visit.
        
        Set IE = CreateObject("InternetExplorer.Application")
                    
            IE.navigate URL
            IE.Visible = True
            
            While IE.Busy And IE.readyState <> 4: DoEvents: Wend
            
            While IE.Document Is Nothing: DoEvents: Wend
            
                With IE.document
                    Set oInput1 = .getElementById("ex1-inputEl")    ' Starting Date
                    Set oInput2 = .getElementById("ex2-inputEl")    ' Endding Date
                    Set oSubmit = .getElementById("cmdSubmit")      ' Get Report
                End With
            
            oInput1.Value = Format(Date - 1, "mm/dd/yyyy")
            oInput2.Value = Format(Date - 1, "mm/dd/yyyy")
                
            oSubmit.Click
            
End Sub

I have added the second loop, and I am running the whole macro through steps "F8". and I am still getting the runtime error 91 at the With Statement.

here is my full code as of now. everything is running fine up to the point we are speaking about.

Sub MCLogin()

    Dim IE As Object
    Dim IEdoc As Object
    Dim oSelect As Object
    Dim URL As String
    Dim MyHTML_Element As IHTMLElement
    Dim MyURL As String
    Dim myYesterday As Date
    Dim oInput1 As Object
    Dim oInput2 As Object
    Dim oSubmit As Object
    
              
        MyURL = "*****************************************"
        
        Set MyBrowser = New InternetExplorer
        MyBrowser.Silent = True
        MyBrowser.navigate MyURL
        MyBrowser.Visible = True
        
        While MyBrowser.readyState <> 4: DoEvents: Wend
  
            Set HTMLDoc = MyBrowser.document
            HTMLDoc.all.txtLogonID.Value = "**********"
            HTMLDoc.all.txtPswd.Value = "********"
            
            For Each MyHTML_Element In HTMLDoc.getElementsByTagName("input")
                If MyHTML_Element.Type = "submit" Then MyHTML_Element.Click: Exit For
            Next
            
            While MyBrowser.readyState <> 4: DoEvents: Wend
            
       Application.wait Now + TimeSerial(0, 0, 2)
       
            Set oSelect = MyBrowser.document.getElementById("CatID")
            oSelect.Focus
            oSelect.selectedIndex = 6
            oSelect.FireEvent "onchange"
            
            While MyBrowser.readyState <> 4: DoEvents: Wend
            
        Application.wait Now + TimeSerial(0, 0, 2)
        
            For Each MyHTML_Element In HTMLDoc.getElementsByTagName("input")
                If MyHTML_Element.Value = "Last" Then MyHTML_Element.Click: Exit For
            Next
            
            While MyBrowser.readyState <> 4: DoEvents: Wend
            
            Set AllHyperLinks = HTMLDoc.getElementsByTagName("a")
            
                For Each Hyper_link In AllHyperLinks
                
                    If Hyper_link.innerText = "New Jobs Remaining- Updated to match better 11/20/13" Then
                          Hyper_link.Click
                          Exit For
            
                End If
            Next
                       
            While IE.Busy And IE.readyState <> 4: DoEvents: Wend
            
            While IE.Documet Is Nothing: DoEvents: Wend
            
            With IEdoc
                    Set oInput1 = .getElementById("ex1-inputEl")    ' Starting Date
                    Set oInput2 = .getElementById("ex2-inputEl")    ' Endding Date
                    Set oSubmit = .getElementById("cmdSubmit")      ' Get Report
                End With

                        
            oInput1.Value = Format(Date - 1, "mm/dd/yyyy")
            oInput2.Value = Format(Date - 1, "mm/dd/yyyy")
            
            oSubmit.Click
            
            
End Sub

hmmm. still got the runtime 91 object variable or With block variable not set.

Do not remove Busy condition from waiting loop !

Other tip : IE.locationName is equal to the title of the webpage only when IE finishes to load it …

GOT IT!
Thank you so much Leith Ross, you have been an amazing help on this. I ended up running the same code as above only I added a application.wait line to halt VBA for a minute while the page loaded up. Works like a champ.

Thanks Again,
John