VBA Get text fragment of an html element with id

Question

VBA Get text fragment of an html element with id

Navigation

#1 by (1 votes)

0

I am trying to access a web page with a macro in Excel, enter a code in an input, press a button and copy the price resulting from the search. It is this last step that I do not achieve. The element that contains the price has the id #result_ok but one of the drawbacks is that it is a text node that does not end up being inside anything and at the same time has three brothers. I show you:

I have tried to do a getElementById and access its innerText or innerHTML and nothing ... It gets code that does not correspond to its inner text or several errors when trying other things.

Sub buscar()

Dim IE As InternetExplorer
Dim DOC As HTMLDocument
Dim url As String

Set IE = Nothing
Set DOC = Nothing

url = "http://www.salvadorescoda.com/tarifas/index.htm"
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.navigate url

While IE.Busy Or IE.readyState <> READYSTATE_COMPLETE
    DoEvents
Wend

Set DOC = IE.document.frames


Dim iFrameDoc As HTMLDocument

Set iFrameDoc = DOC.Item("mainFrame").document 'especificamos el nombre del frame
If iFrameDoc Is Nothing Then
    MsgBox "No existe ningún frame con el nombre especificado."
    IE.Quit
    Set IE = Nothing
    Exit Sub
End If

Dim itemEle As Object

'ya estamos en el frame, ahora podemos conseguir el elemento INPUT
For Each itemEle In iFrameDoc.getElementsByTagName("input")
    If itemEle.getAttribute("class") = "campocodigo" Then
        itemEle.Value = ActiveCell.Value
        Exit For
    End If
Next

'buscamos y clickamos el botón buscar
For Each itemEle In iFrameDoc.getElementsByTagName("input")
    If itemEle.getAttribute("class") = "floatright botonbuscar" Then
        itemEle.Click
        Exit For
    End If
Next

'¿Como obtengo el texto?
ActiveCell.Offset(0, 1).Value = iFrameDoc.getElementsById("result_ok").innerHTML




'IE.Quit
'Set IE = Nothing


End Sub

Do you know what the problem is? How can I get it? Thank you very much in advance!

vba macros excel get

asked by Adrià Fàbrega 06.05.2018 в 22:36

source

1 answer

Attempt to invoke virtual method 'void android.widget.ListView.setAdapter (android.widget.ListAdapter)' on a null object reference get the last two-dimensional associative arrangement

score 1 · Accepted Answer

Hello!

It's the same as always. It's about making a loop with all the DIV elements that you have, until you identify the one that interests you.

I have added to the code, after the part where you click on search, the following:

Dim EsteDiv As HTMLDivElement 'variable para encontrar el elemento DIV que nos interesa

Dim EsteString() As String 'Matriz tipo STRING para almacenar el texto que nos interesa del DIV

'Nuevo bucle para coneguir el DIV de resultados
For Each EsteDiv In iFrameDoc.getElementsByTagName("div")
    If EsteDiv.getAttribute("id") = "result_ok" Then
    'estamos en el DIV que interesa. Cogemos todo el texto y lo dividimos con SPLIT
    'usando como delimitador 1 espacio en blanco
    'Así creamos una matrix de Texto con varios valores, pero el precio siempre estará en el último Index de la matriz
    'el último Index siempre se saca con UBOUND
        EsteString = Split(EsteDiv.textContent, " ")
        ActiveCell.Offset(0, 1).Value = EsteString(UBound(EsteString))
        Exit For
    End If
Next EsteDiv

Set EsteDiv = Nothing

To summarize the process, what I do is that I divide the whole text of the DIV, using space as the boundary character. In your example, the whole text of the DIV is something like this:

Artículo: AA02079Descripción: V.ASIENTO JENKINS 2 1/2"PVP (Sin IVA): 379.82€

Of all the text, only the last thing interests me. Everything else is too much for me. So what I do is I divide the string, using the function Split (more info here)

This returns an array of several values (in this case up to 10, because the initial value in a matrix is always in index 0), which are the following:

Matrix (0) = Article:

Matrix (1) =

Matrix (2) = AA02079 Description:

Matrix (3) = VOICE

Matrix (4) = JENKINS

Matrix (5) = 2

Matrix (6) = 1/2 "PVP

Matrix (7) = (Without

Matrix (8) = VAT):

Matrix (9) = € 379.82

Of all, we are interested in the latter, which is the price. We are interested in Matrix (9). And we have been lucky because, just as the web is designed right now, we will always be interested in the last value because it is the price.

To know the last value within a matrix, we use the property Ubound

And to call a specific value of a matrix, it is simply to use a number. That is, if you write, for example, Activecell.Value = Matriz(3) you will get the fourth value. But if you write ActiveCell.Value = Matriz (Ubound(Matriz)) you will always get the last value of the array, regardless of its size.

So with all these ideas is how I designed the code, and this is what I get after executing it:

I hope it works for you, and you can adapt the code to your needs.