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>`
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!
– Bruno Ximenes
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.
– César Rodriguez
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.
– César Rodriguez
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!
– Bruno Ximenes
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.
– Bruno Ximenes
I’m happy to help!
– César Rodriguez
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!
– Bruno Ximenes
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?
– César Rodriguez
If you manually set up Explorer to save to a particular folder instead of asking, it doesn’t suit you?
– César Rodriguez
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
– Bruno Ximenes
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?
– Bruno Ximenes
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.
– Bruno Ximenes
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'
– Bruno Ximenes
Two questions: 1) What was the error that gave?
– César Rodriguez
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
– Bruno Ximenes
for
in the fieldlabel
, or at least another control within the taglabel
. What is this label visually? You can send the code around it?– César Rodriguez
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
– Bruno Ximenes
Should have to do with these other tags. Brings the other elements on Monday!
– César Rodriguez
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
– Bruno Ximenes
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.
– Bruno Ximenes