VBA Select html element by classe

Question

VBA Select html element by classe

Navigation

#1 by (3 votes)

1

I need to access the value of an input and click on another with an Excel macro and I need to select the inputs through its class, since they do not have an id. I've seen googling that the .getElementByClassName () method exists, but there's no way it works. Nor tansolo accessing the supposed first element with "(0)"

IE.document.getElementByClassName("tarifas-index")(0)

I have also seen people who do a foreach by selecting tagName and go through the collection of selected items to conmprogate with an If if .className == to the one we want. Something more or less like this:

Dim IE As Object
Set IE = CreateObject("InternetExplorer.application")
IE.Visible = True
IE.Navigate "http://www.salvadorescoda.com/tarifas/index.htm"
Do
    DoEvents
Loop Until IE.readystate = 4
'------------------------

'La siguiente linea es la que causa el error
For Each itemEle In IE.document.getElementByTagName("input")
    If itemEle.className = "campocodigo" Then
        itemEle.Value = "prueba"
    End If
Next

Range("A2").Value = valor

But this code does not work for me, specifically the line after the comment. What do I do wrong or do I not consider? What solution is there? I have never programmed with VBA and I confess that after programming JS and some PHP this language makes me nervous. Thanks in advance!

vba macros excel html-select

asked by Adrià Fàbrega 20.04.2018 в 22:47

source

1 answer

SQLite DO NOT erase in Cascade How to pass a javascript variable to an external php

score 3 · Accepted Answer

Hello!

Your code was well on track, and your idea of playing with classname as well. The problem is the source code of the URL provided. If you read the source code, you will see that in reality the element you are looking for is not in the HTML itself (I read it with Mozilla Firefox, and it does not work out), because the input you are looking for is actually within a frame that is load on the main. That is, it is in another document (if you know JS and PHP I am convinced that you know more than I do about documents, frames and others, but I hope to have explained myself, I do not know about PHP and JS, pure VBA is my thing) .

Knowing this, before trying to get the element, you have to access the frame document. In particular, the frame that interests you is called mainFrame .

My code is a copy of yours, but expanding a bit. To summarize, the steps are:

We navigate to the target URL

We select the main document of the URL

Within that main document, we select the target frame

Once inside the frame, we select the document that makes up the frame

We select the target Input, and write what we want (in this case, test )

And now the code. I hope I can serve. Modify it for your needs.

IMPORTANT: You have to activate two references in the VBA library. The bookstores are:

Microsoft HTML Object Library

Microsoft Internet Controls

The code:

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


url = "http://www.salvadorescoda.com/tarifas/index.htm"
Set IE = New InternetExplorer
IE.Visible = True
IE.navigate url

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

Set DOC = IE.document

Dim iFrameDoc As HTMLDocument
Set iFrameDoc = DOC.frames("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 = "prueba"
        Exit For
    End If
Next

'<----------resto de tu código------->


IE.Quit
Set IE = Nothing

What I get by executing the code:

I hope it serves you, and I encourage you to continue with VBA, which is a very damn language, but in the long run I think it's worth it.

Greetings.

UPDATE: OP has a problem with IE 11, because it does not recognize the frames. Let's try another way to call them, let's see if it comes out.

IE 11 VERSION CODE

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 = "prueba"
        Exit For
    End If
Next

'<----------resto de tu código------->


IE.Quit
Set IE = Nothing