我想使用 Excel vba 从网站获取数据，但我不能 [英] I want to get data from web site with Excel vba but I can't

问题描述

Sub Galoplar()
    Sheets("Galop").Select
    Range("A1").Select
    Dim elem As Object, trow As Object
    Dim R&, C&, S$
    With New XMLHTTP60
        .Open "POST", "https://yenibeygir.com/at/getatdetaytab", False
        .setRequestHeader "content-type", "application/x-www-form-urlencoded; charset=UTF-8"
        .send "tab=galopTab&id=15673"
        S = .responseText
    End With

    With New HTMLDocument
        .body.innerHTML = S

        For Each elem In .getElementsByClassName("at_Galoplar")(0).Rows
            For Each trow In elem.Cells
                C = C + 1: Cells(R + 1, C) = trow.innerText
            Next trow
            C = 0: R = R + 1
        Next elem
    End With
End Sub

我从带有上述代码的网址"链接获得Galopları".但是我无法使用以下代码获取Yarışları"数据.

I get "Galopları" from the "Web address" link with the above code. But I can't get "Yarışları" data with the following code.

Sub Yarislar()
    Sheets("Yaris").Select
    Range("A1").Select
    Dim elem As Object, trow As Object
    Dim R&, C&, S$

    With New XMLHTTP60
        .Open "POST", "https://yenibeygir.com/at/getatdetaytab", False
        .setRequestHeader "content-type", "application/x-www-form-urlencoded; charset=UTF-8"
        .send "tab=yarisTab&id=15673"
        S = .responseText
    End With

    With New HTMLDocument
        .body.innerHTML = S

        For Each elem In .getElementsByClassName("at_Yarislar")(0).Rows
            For Each trow In elem.Cells
                C = C + 1: Cells(R + 1, C) = trow.innerText
            Next trow
            C = 0: R = R + 1
        Next elem
    End With
End Sub

我的问题是，我哪里出错了?

My question is about, where am I making a mistake?

如何使用 vba 代码获取上的Son 1 Yıl"数据网址"链接?

How do I use a vba code to get the "Son 1 Yıl" data on the "Web address" link?

推荐答案

初始登陆选项卡没有 jquery 发起的 XHR 事件，其他选项卡会这样做.

The initial landing tab does not have jquery initiated XHR events which the other tabs do.

您可以针对着陆页发出 GET 请求，以按第一个选项卡的类名获取表格.

You can issue a GET request against your landing page to grab the table by its class name for the first tab.

Option Explicit
Public Sub Yarislar()
    Dim s As String, html As HTMLDocument
    Set html = New HTMLDocument

    With New XMLHTTP60
        .Open "GET", "https://yenibeygir.com/at/15673/budakhan", False
        .setRequestHeader "content-type", "application/x-www-form-urlencoded; charset=UTF-8"
        .send
        s = .responseText
    End With

    Dim hTable As HTMLTable, clipboard As Object
    html.body.innerHTML = s
    Set hTable = html.querySelector(".at_Yarislar")

    Set clipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    clipboard.SetText hTable.outerHTML
    clipboard.PutInClipboard
    ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).PasteSpecial

End Sub

<小时>

对于您的第二个问题(因为您无法发布):

---

For your second question (as you are unable to post it):

Public Sub test()
    Dim s As String, html As HTMLDocument, hTable As Long, hTables As Object, clipboard As Object, ws As Object
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    With New XMLHTTP60
        .Open "POST", "https://yenibeygir.com/jokey/updatestats", False
        .setRequestHeader "content-type", "application/x-www-form-urlencoded; charset=UTF-8"
        .send "id=10294&LastYear=True"
        s = .responseText
    End With

    Set html = New HTMLDocument

    html.body.innerHTML = s
    Set hTables = html.querySelectorAll(".Stats")

    For hTable = 0 To hTables.Length - 1
        Set clipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        clipboard.SetText hTables.item(hTable).outerHTML
        clipboard.PutInClipboard
        ThisWorkbook.Worksheets("Sheet1").Cells(GetLastRow(ws, 1) + 2, 1).PasteSpecial
    Next
End Sub

Public Function GetLastRow(ByVal ws As Worksheet, Optional ByVal columnNumber As Long = 1) As Long
    With ws
        GetLastRow = .Cells(.Rows.Count, columnNumber).End(xlUp).Row
    End With
End Function

<小时>

在某些情况下，现在似乎存在后期绑定剪贴板引用的问题.这是通用的早期绑定方法，其中 hTable 是目标 HTMLTable 对象.

---

There now seems to be problems with late bound clipboard reference in some cases. Here is generic early bound method where hTable is the target HTMLTable object.

对于剪贴板早期绑定，请转到 VBE > 工具 > 参考 > Microsoft-Forms 2.0 对象库.

For clipboard early bound go VBE > Tools > References > Microsoft-Forms 2.0 Object Library.

如果您向项目中添加用户窗体，该库将自动添加.

If you add a UserForm to your project, the library will get automatically added.

Dim clipboard As DataObject
Set clipboard = New DataObject
clipboard.SetText hTable.outerHTML
clipboard.PutInClipboard
ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).PasteSpecial

这篇关于我想使用 Excel vba 从网站获取数据，但我不能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！