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

Asked

Viewed 249 times

0

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>`

1 answer

0


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.

  • Show ball Cesar vc eh fera! Tomorrow I’ll be the code you gave me! Sure q will work! One day I still get good in VBA like you! = ) strong embrace!

  • Thanks for the compliments! I’m not that good, but last year I started doing something for my company that seems to be similar to what you’re doing in yours, so I’ve been through those same sufferings.

  • One of the things that broke my head and that can happen to you, since you are working with intranet: there were times that, out of nowhere, the object of Internet Explorer (in your code is the Rowser) gave an error of "disconnected object from customers", something like that. If it happens, you can ask here, it is easy to solve. It is an error that usually occurs in intranet, because of the redirects.

  • Show ball Cesar! You can let it happen I turn to the forum and to you kkkk! I have many ideas to facilitate the collection of data from my reports, I was thinking of something to pull directly from the database, I just don’t know if it is possible! But it’s a future project because I have a power BI panel that I need to supply with information from the same intranet system. Thank you very much for everything!

  • Thanks Caesar, once again worked out the code you made! I’m going step by step to be able to generate the reports. Soon if I fail to develop the next steps I will request your help and that of all of the stackoverflow. Hug.

  • I’m happy to help!

  • Caesar a doubt, is it possible to pass the parameters to save certain file? Local type, name, once you click on the report button it opens the box to indicate where you want to save the pdf. And also, if instead of generating a pdf it would generate me an xls file? Thank you for everything brother!

  • I’ve already done this using Chrome, through an interface called Selenium (which fortunately is free). At the time, I couldn’t do it through Internet Explorer, and then I didn’t have to do it anymore. In the downloads folder it doesn’t suit you?

  • If you manually set up Explorer to save to a particular folder instead of asking, it doesn’t suit you?

  • I guess you wouldn’t notice that I have to rename the files. altogether I will have to download 4 files in pdf and 1 in xls that the system returns me. unless in the code I can program the names like this: QC Daily report 1º "26 - 02 - 2020" where the date in parentheses is automatically set from the day before the current system day. Like I’m gonna use the formula

  • I found this post : https://answall.com/questions/244037/comor- um-dowload-no-ie-via-vba. Can I change where you use URL to call by button and not by URL?

  • Cesar, good morning to you! Performing the tests with the code that Oce passed me is running perfect, however I found a problem in the question of if the excel field is empty, it should clear the field of the web form too, but it is not cleaning. I will explain: in excel I put cells to input the data, dtinicial, dtfinal and turn. In the code it takes the value of these cells and copies to the field of the web form. however to the field shift sometimes I need to leave empty to take the 2 shifts. only that does not clean.

  • I tried to do this but did not return the empty value: ' Do If strTurno = "" Then Htmldoc.getElementById("formSearch:shiftId_label"). Value = "" Else Htmldoc.getElementById("formSearch:shiftId_label"). Value = strTurno End If Loop Until Htmldoc.getElementById("formSearch:shiftId_label"). Value = strTurno'

  • Two questions: 1) What was the error that gave?

  • None! Just don’t change the value! The turn field has a list where you select 1, 2, 3 turn or leave it blank! Maybe that’s why qnd leaves the cell blank it does not change the value of the field on the page. Already if I leave filled with 1 or 2 it changes normally

    1. How is the turn field? In theory, the label is just a label for another control, so its content should not be changed by the user. There should be a tag for in the field label, or at least another control within the tag label. What is this label visually? You can send the code around it?
  • Now I can only rule on Monday! I saw you have other information, I saw tbm that has a Selected being forever start with the 1 filled turn

  • Should have to do with these other tags. Brings the other elements on Monday!

  • blz! I even tried to use the other name that the element has, I did using getElementById and tbm classname but it didn’t help. Does not generate error but does not fill with empty. Both I send fetch the cell value in Excel and I leave = "" in the command line

  • Good morning Caesar, as I told you earlier, the code for the shift label has an input id as the following code: <select name="formSearch:shiftId_input" id="formSearch:shiftId_input"><option value=""></option><option Selected="Selected" value="4050">1</option><<value="4051">2</option><option value="13100">3</option></select>. I have tried to change the id="formSearch:shiftId_label for input, or just left shiftID and even then it still looks like 1 if I leave the field empty.

Show 15 more comments

Browser other questions tagged

You are not signed in. Login or sign up in order to post.