Help with VBA and web to populate web form fields with cell data and trigger report output button

Question

I have the code below that accesses the site, log in it, enter the management menu. In the managerial menu screen I have form fields where I insert initial, end date, turn and the generate reports button. I thought that using Htmldoc.all and the form and button id I could fill the data according to the imputed value in the worksheet1 C9 cells for start date, cell C11 for end date and cell 13 for turn. But do not load the data or click on the button! If anyone can help me I will be eternally grateful! Thanks to everyone! Follow the code below:

VBA code

Dim HTMLdoc As HTMLDocument
Dim oBrowser As InternetExplorer
 
'Declara função Sleep
#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For 32 Bit Systems
#End If
 
 
Sub Login()
 
Dim oHTML_Element As IHTMLElement
Dim sURL As String
Dim elemCollection As Object
Dim obj As Object
Dim showhide As Object
Dim IE As Object
Planilha1.Select
 
 
'ACESSAR SITE IRCS----------------------------------
On Error GoTo Err_Clear
   sURL = "http://tdbsorsvr034/tdb-rcs-report/"
Set oBrowser = New InternetExplorer
   oBrowser.Silent = True
   oBrowser.timeout = 60
   oBrowser.navigate sURL
   oBrowser.Visible = True
   
Do
Loop Until oBrowser.readyState = READYSTATE_COMPLETE
 
'INSERIR USUARIO E SENHA IRCS-----------------------
Set HTMLdoc = oBrowser.document
  HTMLdoc.all.j_username.Value = "bximenes"
  HTMLdoc.all.j_password.Value = "Bx123"
  HTMLdoc.all.loginBtnId.onclick
 
  
'PROCURA ELEMENTO GERENCIAL PARA AVANÇAR MENU-------
      EsperaIE IE, 2000
      Set link = HTMLdoc.getElementsByTagName("a")
    For Each l In link
        If l.innerText = "Gerencial" Then
            EsperaIE IE, 2000
            l.Click
            Exit For
        End If
    Next l
    
'INSERIR DATA INICIAL E DATA FINAL E CLICA EM RELATORIOS------

‘Tentei fazer da forma abaixo e não foi
      EsperaIE IE, 2000
      Set form = HTMLdoc.getElementsByTagName("FormSearch")
    For Each f In form
        If f.innerText = "dateUntilId_input" Then
            EsperaIE IE, 2000
            f.Value = "20/02/2020 23:59:59"
            Exit For
        End If
    Next f
 
 
 ‘E da forma abaixo também não foi 
 
'  EsperaIE IE, 2000
'Set HTMLdoc = oBrowser.document
'  HTMLdoc.all.formSearch: dateFromId_inputdateFromId_input.Value = "Planilha1!C9"
'  HTMLdoc.all.formSearch: dateUntilId_input.Value = "Planilha1!C11"
'  HTMLdoc.all.formSearch: shiftId_label.Value = "Planilha1!C13"
'  HTMLdoc.all.formSearch: reportsId.onclick
 
 
For Each oHTML_Element In HTMLdoc.getElementsByTagName("input")
    If oHTML_Element.Type = "submit" Then oHTML_Element.Click:
    Exit For
   
 
Next
Err_Clear:
Resume Next
 
 
 
End Sub`

Page code

pagina
 
 
`data inicial
<input id="formSearch:dateFromId_input" name="formSearch:dateFromId_input" type="text" value="26/02/2020 00:00:00" class="ui-inputfield ui-widget ui-state-default ui-corner-all hasDatepicker" role="textbox" aria-disabled="false" aria-readonly="false" aria-multiline="false">
‘data final
<input id="formSearch:dateUntilId_input" name="formSearch:dateUntilId_input" type="text" value="26/02/2020 23:59:59" class="ui-inputfield ui-widget ui-state-default ui-corner-all hasDatepicker" role="textbox" aria-disabled="false" aria-readonly="false" aria-multiline="false">
‘turno
<label id="formSearch:shiftId_label" class="ui-selectonemenu-label ui-inputfield ui-corner-all" style="width: 158px;">1</label>
‘botao relatorio
<button id="formSearch:reportsId" name="formSearch:reportsId" class="ui-button ui-widget ui-state-default ui-corner-all ui-button-text-only bt_search_100" onclick="PrimeFaces.ab({source:'formSearch:reportsId',update:'showDialogMsg'});return false;" type="submit" role="button" aria-disabled="false"><span class="ui-button-text ui-c">Relatórios</span></button>`

Answer 1

Dear Bruno, I think this one will be quiet.

Again, as in your previous doubt, the method getElementsByTagName returns a collection of elements that have the tag passed as parameter. In this case, by your HTML clippings of the page, the tag you are looking to change is not FormSearch, and yes input (for initial and final date fields) and button (to the report button). FormSearch is just a prefix of the name, practical used by the web designer to better organize page elements.

So to make the code that way, the correct instruction would be HTMLdoc.getElementsByTagName("input") and HTMLdoc.getElementsByTagName("button"), inside the For you test whether the object referenced by f is what you want.

But this is not the simplest solution. As the three elements (start date, end date and button) have a id defined on the page, and whereas the attribute id is unique (or should, if the page is well done), the easiest is to use the method getElementById, that brings a single object, individual. Following example below (insert the variable statements at the beginning and insert the code below in your comment code "INSERT START DATE, ETC" onwards):

    Dim HTMLDoc As HTMLDocument
    Dim btRelatorioPorreta As HTMLButtonElement
    Dim strDataInicial As String, strDataFinal As String

    Do
        Set btRelatorioPorreta = HTMLDoc.getElementById("formSearch:reportsId")
        ' É recomendável inserir um timer aqui, pois se a página não carregar o botão, vai ficar num loop infinito. Se quiser, é só pedir que eu tenho esse código em algum lugar.
    Loop Until Not btRelatorioPorreta Is Nothing

    ' INSERIR DATA INICIAL E DATA FINAL E CLICAR EM RELATORIOS
    strDataInicial = "20/02/2020 23:59:59"
    strDataFinal = "21/02/2020 23:59:59"

    Do
        HTMLDoc.getElementById("formSearch:dateFromId_input").Value = strDataInicial
    Loop Until HTMLDoc.getElementById("formSearch:dateFromId_input").Value = strDataInicial

    Do
        HTMLDoc.getElementById("formSearch:dateUntilId_input").Value = strDataFinal
    Loop Until HTMLDoc.getElementById("formSearch:dateUntilId_input").Value = strDataFinal

    HTMLDoc.getElementById("formSearch:reportsId").Click

In addition, I will explain something that may cause doubt: I put the instructions to fill in the initial and final dates within a loop. Why? Because in practice I realized that sometimes the VBA runs much faster than the Internet Explore processes, and we end up having some mistakes, because we missed a part of the date, or something like that.

I have had many errors of this type, and so I always make these loops, which ensure that the code will only advance if the contents of the fields are properly filled. If you want, you can test without the Do...Loops and, if you get errors, you insert the loops.