VBA web scraping issue - how to navigate specific web using html structure (href / child/ )

Question

Hello dear VBA collegues :)

Sub login()
'test 
    Const URL$ = "https://kwm.kromi.de/cgi-bin/kwm?HTML=frontend/login.htm"
    Dim UserName As String, Password As String, LoginData As Worksheet
    Set LoginData = ThisWorkbook.Worksheets("Sheet1")
    UserName = LoginData.Cells(1, "B").Value
    Password = LoginData.Cells(2, "B").Value
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = False
        .Navigate URL
        ieBusy IE
        .Visible = True
       Dim oLogin As Object, oPassword As Object
        Set oLogin = .document.getElementsByName("VS_LOGIN")(0)
        Set oPassword = .document.getElementsByName("VS_PASSWORD")(0)
        oLogin.Value = UserName
        oPassword.Value = Password
        .document.forms(0).submit
        ieBusy IE
       Stop
       '.document.getElementsByTagName("a")(2).href
       '.document.getElementsByClassName("link3").Click
     .Navigate2 ""
     ieBusy IE
     Stop
    End With
'''
End Sub
Sub ieBusy(IE As Object)
    Do While IE.Busy Or IE.readyState < 4
        DoEvents
    Loop
End Sub

And the first task is work, macro log in to website. I need to go deeper and click something but structure of web is too much for my small head I am looking some examples on website but nothing work. I showed code of website below. I need to click button "statystyka".

/html/body/div[1]/div[1]/a[2] - Xpath adress [link picture]https://ibb.co/2Pgx2tn

May you give me some help please :)

edit: I tried use something like this: '.document.getElementsByTagName("a")(2).href but this not good way on thinking

What does nothing work means? Did you click it but nothing happen or is there some error? — Raymond Wu
– Raymond Wu, Commented Sep 16, 2021 at 8:58
It looks like the link actually runs a Javascript so google on ExecScript to find out how to execute Javascript using IE. — Raymond Wu
– Raymond Wu, Commented Sep 16, 2021 at 9:02
@Raymond Wu ,For my is issue is how to navigate this structure of document it is connected with html structure and dependence of html (doc -> head ->div etc). I don't know how to choose in coretly way line (I supouse that people with expirience with html and vba had knowglege how to do this): <a href="" onclick="OneInOne('https://kwm.kromi.de:443/cgi-bin/kwm?HTML=frontend/statistic/stat_current.htm&ID=C466409C6997DADCE06C38B36731BA92',6); return false;">Statystyka</a> I tried something like this '.document.getElementsByTagName("a")(2).href — MaciekPol
– MaciekPol, Commented Sep 16, 2021 at 9:23
I created a simple example to test QHarr's answer, and it works well. However, we cannot access the page you provided, so I am afraid that you cannot reproduce your problem. — Xudong Peng
– Xudong Peng, Commented Sep 20, 2021 at 10:20
@Raymond Wu I am waiting for QHarr reply, maybe he have some idea or other way to try. — MaciekPol
– MaciekPol, Commented Sep 20, 2021 at 13:07

QHarr · Accepted Answer · 2021-09-16 16:06:37Z

1

You need to move into the appropriate frame, add a wait as I am using .Navigate to the frame src, then you can target by a substring of the onclick attribute:

ie.navigate ie.document.querySelector("[name=Navigator]").src
ieBusy ie
ie.document.querySelector("[onclick*=statistic]").click

answered Sep 16, 2021 at 16:06

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

MaciekPol Over a year ago

Hello thank You @QHarr i tried Your code ie.navigate ie.document.querySelector("[name=Navigator]").src <br/> after this line navigate to site with menubar <br/> ie.document.querySelector("[onclick*=statistic]").click <br/> after this line web log out to login site . Do You have an idea? <br/>

QHarr Over a year ago

The click logs you out?

MaciekPol Over a year ago

Thank You dear colluege for Your answer I try to show You on the picture, normaly when You manualy click you recived something like this (ibb.co/WykqLnW) using VBA it is look like this (ibb.co/bLfsNpK) and resul is log out (ibb.co/mtPc1KS)

MaciekPol Over a year ago

is there so different way of choose this button? Do You know any possibility?

QHarr Over a year ago

Try placing ie.document.querySelector("[onclick*=statistic]").click with

ie.document.parentWindow.ExecScript Replace$(ie.document.querySelector("[onclick*=statistic]").getAttribute("onclick"), " return false;", "")

- Also, are there any other steps that should be happening in between?

|

MaciekPol · Accepted Answer · 2021-10-06 10:33:08Z

0

If You want navigate by tag You need to set frame as is below

Dim doc As HTMLDocument
Dim doc2 As HTMLDocument
Dim lnk As HTMLLinkElement

Set doc = IE.document
 Set doc2 = doc.frames("Navigator").document
 Set lnk = doc2.getElementsByTagName("A")(1)
 lnk.Click

@QHarr @Raymond Wu :) Thank You for trying help, maybe that will be solution in others

answered Oct 6, 2021 at 10:33

MaciekPol

33 bronze badges

Collectives™ on Stack Overflow

VBA web scraping issue - how to navigate specific web using html structure (href / child/ )

2 Answers 2

10 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related