Automation with excel vba

Question

I tried automation this proccess with excel vba:

After to click in link... Open next window:

my code:

Sub WFM_test()

    Sheets("Preenchimento_Remedy").Activate
    Wd = Range("D02").Value 'URL address


    Set objShell = CreateObject("Shell.Application")
    Set objAllWindows = objShell.Windows

    For Each ow In objAllWindows
        'MsgBox ow
        If (InStr(1, ow, "Internet Explorer", vbTextCompare)) Then
            'MsgBox ow.Hwnd & "  " & ow & "   " & ow.locationURL
            If (InStr(1, ow.LocationURL, Wd, vbTextCompare)) Then
                Set objRemedy = ow
            End If
        End If
    Next

    If objRemedy Is Nothing Then
    Else

        Set objPage = objRemedy.Document

       Set WFM = ObjPage.getElementsByClassName("MenuEntryNameHover")
       WFM.item(0).click

       End if

End Sub

how to click in "Defaut WFM" with vba? I tried: <pre>Set WFM = ObjPage.getElementsByClassName("MenuEntryNameHover").item(0).click</pre> — Anderson Correa
This would be hard to know without seeing the XML, I doubt anyone is willing to find the variable for you in the XML, may I suggest you loop through the elements to find the name? Like so: For Each elem In ieApp.Document.all Range("A" & (i + 1)).Value = elem i= i+1 Next — Sgdva

Ryan Wildry Ryan Wildry · Accepted Answer · 2016-06-09T15:40:40

Give this a shot. It should select the table first, then select all the elements that are part of that table and do a match based on the InnerText.

Sub WFM_test()
    Dim AllTableItems    As Object
    Dim element          As Object

    Sheets("Preenchimento_Remedy").Activate
    Wd = Range("D02").Value 'URL address


    Set objShell = CreateObject("Shell.Application")
    Set objAllWindows = objShell.Windows

    For Each ow In objAllWindows
        'MsgBox ow
        If (InStr(1, ow, "Internet Explorer", vbTextCompare)) Then
            'MsgBox ow.Hwnd & "  " & ow & "   " & ow.locationURL
            If (InStr(1, ow.LocationURL, Wd, vbTextCompare)) Then
                Set objRemedy = ow
            End If
        End If
    Next

    If Not objRemedy Is Nothing Then
        ' you need this (0) as you specify which class you want to select
        ' The classname is not a unique property
        Set Table = objRemedy.Document.getElementsByClassName("MenuTable")(0)

        'Select all Elements in the table
        Set AllTableItems = Table.getElementsbyTagName("*")

        'Iterate over the elements in the table and find the match
        For Each element In AllTableItems
            If element.InnerText = "Default WFM Group" Then element.Click
        Next i

    End If
End Sub

Automation with excel vba

1 Answers