Code
You can accomplish this with this code:
'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 TesteBusca()
Dim IE As Object
Dim sWindows As Object
Dim sJanelas As Object
Dim sDados As String
Dim doc As MSHTML.HTMLDocument
Set IE = CreateObject("InternetExplorer.Application")
IE.navigate "http://sdro.ons.org.br/SDRO/DIARIO/index.htm"
IE.Visible = True
EsperaIE IE, 2000
'Debug.Print IE.document.getElementsByTagName("frame")(1).contentDocument.getElementsByTagName("a").innerText
i = 1
For Each link In IE.document.getElementsByTagName("frame")(1).contentDocument.getElementsByTagName("a")
'Debug.Print EXTRAIRELEMENTO(link.href, 8, "/")
If EXTRAIRELEMENTO(link.href, 8, "/") = "21_EnergiaNaturalAfluente.html" Then
i = i + 1
link.Click
EsperaIE IE, 2000
If i = 2 Then Exit For
End If
Next link
End Sub
Public Sub EsperaIE(IE As Object, Optional time As Long = 250)
'Código de: https://stackoverflow.com/questions/33808000/run-time-error-91-object-variable-or-with-block-variable-not-set
Dim i As Long
Do
Sleep time
Debug.Print CStr(i) & vbTab & "Ready: " & CStr(IE.READYSTATE = 4) & _
vbCrLf & vbTab & "Busy: " & CStr(IE.Busy)
i = i + 1
Loop Until IE.READYSTATE = 4 Or Not IE.Busy
End Sub
Function EXTRAIRELEMENTO(Txt As String, n, Separator As String) As String
On Error GoTo ErrHandler:
EXTRAIRELEMENTO = Split(Application.Trim(Mid(Txt, 1)), Separator)(n - 1)
Exit Function
ErrHandler:
' error handling code
MsgBox "Erro, veriique os dados de entrada."
EXTRAIRELEMENTO = CVErr(xlErrNA)
On Error GoTo 0
End Function
Explanation
Extract Element
The User Defined Function (UDF - User Defined Function) of extract element is declared to extract the elements of a string separated by a separator, in this example "/", where each element has an index.
This uses the function Split to split the string into multiple elements from a separator.
And the function Trim
is used to avoid errors when space is used as a separator, as this function removes the spaces from the beginning and end of the string.
Example: EXTRAIRELEMENTO("http://sdro.ons.org.br/SDRO/DIARIO/2018_03_15/Html/DIARIO_15-03-2018.xlsx", 1, "/")
returns the value http:
Other Example:
EXTRAIRELEMENTO("http://sdro.ons.org.br/SDRO/DIARIO/2018_03_15/Html/DIARIO_15-03-2018.xlsx", 2, "/")
returns the value `` or empty
Other:
EXTRAIRELEMENTO("http://sdro.ons.org.br/SDRO/DIARIO/2018_03_15/Html/DIARIO_15-03-2018.xlsx", 3, "/")
returns the value sdro.ons.org.br
A very useful function, which can be applied to extract elements of Strings with separators, such as links, directories, product codes, dates, among others.
Wait IE
Normally I use a Soen function to perform the function of waiting a while for the page to load completely, only While IE.Busy Or IE.ReadyState <> 4: Wend
may not be enough.
So instead of While IE.Busy Or IE.ReadyState <> 4: Wend
You call the function EsperaIE
and chooses the waiting time in ms. Example: EsperaIE IE, 5000
Master code
As the ONS site has a hierarchy with iframes and not only the "pure" HTML. You need to search for the "a" tag with: IE.document.getElementsByTagName("frame")(1).contentDocument.getElementsByTagName("a")
, where the frame index is (1), index (0) is at the top of the page.
NOTE: Don’t forget to enable references: Microsoft HTML Object Library and Microsoft Internet Controls.
In Tools > References...
Your friend’s is on the same site? Because in this da ons, I believe you have an iframe... Then the DOM code would change.
– danieltakeshi
No, the website he used was this "http://sys.sigetplus.com.br/cobranca/transmitter/1191/invoices?agent=5111". How should I open the link then?
– Leandro Lazari