Truyền dữ liệu dễ dàng:2 phương pháp VBA đã được chứng minh để kéo dữ liệu trang web vào Excel

Phương pháp 1 – Nhập dữ liệu cụ thể từ trang web vào Excel VBA MsgBox

BƯỚC:

• Đi tới Nhà phát triển tab từ dải băng.
• Nhấp vào Visual Basic từ Mã danh mục để mở Trình soạn thảo Visual Basic . Hoặc nhấn ALT +F11 để mở Trình soạn thảo Visual Basic .

• Điều này sẽ xuất hiện trong Trình chỉnh sửa Visual Basic .
• Chuyển tới Công cụ trình đơn và nhấp vào Tài liệu tham khảo .

• Tài liệu tham khảo – Dự án VBA hộp thoại sẽ xuất hiện.
• Cuộn xuống một chút và kích hoạt hai Microsoft Thư viện . Dấu kiểm Thư viện đối tượng Microsoft HTML và Kiểm soát Internet của Microsoft .
• Nhấp vào OK .

• Nhấp vào Mô-đun từ Chèn thanh menu thả xuống.

• Điều này sẽ tạo ra một Mô-đun trong sổ làm việc của bạn.
• Sao chép và dán VBA mã hiển thị bên dưới.

Mã VBA:

Sub Import_SpecificData()
Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim total As Variant
website = "https://en.wikipedia.org/wiki/2022_FIFA_World_Cup"
Set request = CreateObject("MSXML2.XMLHTTP")
request.Open "GET", website, False
request.setRequestHeader "If-Modified-Since", "Mon, 14 Nov 2022 00:00:00 GMT"
request.send
response = StrConv(request.responseBody, vbUnicode)
html.body.innerHTML = response
total = html.getElementsByClassName("wikitable")(0).innerText
MsgBox total
End Sub

• Chạy mã bằng cách nhấp vào RubSub hoặc nhấn phím tắt F5 .

• Điều này sẽ tìm nạp dữ liệu, nhập những dữ liệu đó từ trang web và hiển thị chúng vào MsgBox .

Giải thích mã VBA

Sub Import_SpecificData()

Phụ là một phần của mã dùng để xử lý công việc trong mã nhưng sẽ không trả về bất kỳ giá trị nào. Nó còn được gọi là thủ tục con. Chúng tôi đã đặt tên cho quy trình của mình là Import_SpecificData() .

Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim total As Variant

DIM câu lệnh trong VBA đề cập đến “tuyên bố, ” phải được sử dụng để khai báo một biến.

website = "https://en.wikipedia.org/wiki/2022_FIFA_World_Cup"
Set request = CreateObject("MSXML2.XMLHTTP")

Điều này sẽ tạo ra một đối tượng thực hiện yêu cầu trang web.

request.Open "GET", website, False

Từ dãy nhà này chúng ta sẽ biết đi đâu và đi đến đó như thế nào. Bạn không cần thay đổi dòng, bạn có thể sao chép trực tiếp dòng này trong khi nhập bất kỳ loại dữ liệu nào từ bất kỳ trang web nào.

request.setRequestHeader "If-Modified-Since", "Mon, 14 Nov 2022 00:00:00 GMT"

Chúng tôi sẽ nhận được dữ liệu mới từ trang web của mình.

request.send

Dòng này gửi yêu cầu đến trang web.

response = StrConv(request.responseBody, vbUnicode)

Chúng tôi lấy dữ liệu phản hồi của trang web thành các biến.

html.body.innerHTML = response

Dòng mã này đặt trang web vào một đối tượng HTML để giúp việc tham chiếu dữ liệu dễ dàng hơn.

total = html.getElementsByClassName("wikitable")(0).innerText

Với dòng này, chúng ta sẽ nhận được tổng số phần tử được chỉ định của trang web. Chúng ta cần lấy tên lớp. Để có được tên lớp của phần tử cụ thể đó, chúng ta phải làm theo các hướng dẫn đơn giản.

• Truy cập trang web và nhấp chuột phải trên khối cụ thể đó.
• Sau đó, nhấp vào Kiểm tra .

• Điều này sẽ hiển thị HTML và CSS các thành phần của trang web đó. Nhấp vào lớp bạn cần học và đặt chúng vào trong html.getElementsByClassName(“wikitable”) .

MsgBox total

Điều này sẽ hiển thị tổng số trong Microsoft cửa sổ.

End Sub

Thao tác này sẽ kết thúc quy trình.

Phương pháp 2 – Quét dữ liệu từ trang web sang Excel

BƯỚC:

• Nhấp vào biểu tượng nhỏ Ghi Vĩ mô .

• Macro bản ghi hộp thoại sẽ xuất hiện.
• Đặt tên cho macro. Chúng tôi đặt tên cho macro là Get_Data .
• Nhấp vào OK .

• Đi tới Dữ liệu tab từ dải băng.
• Nhấp vào Từ Web trong Nhận và chuyển đổi dữ liệu .

• Từ Web cửa sổ sẽ hiển thị.
• Bằng cách nhấp vào CTRL +V trên bàn phím của bạn, dán URL trong URL khu vực sau khi chọn Cơ bản .
• Nhấn nút OK nút.

• Trình điều hướng Kết quả là hộp thoại sẽ xuất hiện.
• Chọn bất kỳ bảng dữ liệu nào từ Tùy chọn hiển thị thực đơn phù hợp với nhu cầu của bạn.
• Chọn “Tải vào .”

• Nhập dữ liệu hộp thoại sẽ hiển thị.
• Chọn Bảng từ “Chọn cách bạn muốn xem dữ liệu này trong sổ làm việc của mình. ” và nhấp vào Bảng tính hiện có .
• Chọn ô nơi bạn muốn nhập dữ liệu.
• Nhấp vào OK .

• Thao tác này sẽ nhập dữ liệu từ trang web.

• Chọn toàn bộ bảng tính và nhấp vào Xóa nút, Microsoft Excel hộp thoại sẽ xuất hiện. Đảm bảo bạn nhấp vào Không .

• Việc này sẽ Xóa tất cả dữ liệu.
• Đi tới Dữ liệu từ dải băng và nhấp vào Làm mới tất cả trong Truy vấn &Kết nối .
• Lấy lại tất cả dữ liệu trong trang tính.

• Mở dải băng và chọn Nhà phát triển từ trình đơn thả xuống.
• Chọn Visual Basic để mở Trình soạn thảo Visual Basic . Hoặc Trình soạn thảo Visual Basic cũng có thể được truy cập bằng cách nhấn ALT +F11 .
• Trong Mô-đun , bạn sẽ nhận được Macro VBA đó.

Mã VBA:

Option Explicit
Sub Get_Data()
'
' Get_Data Macro
'
'
    Sheets("Get Data").Select
    Range("N11").Select
    ActiveWorkbook.Queries.Add Name:="2022 FIFA bidding (majority 12 votes)", _
        Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://en.wikipedia.org/wiki/2022_FIFA_World_Cup""))," & Chr(13) _
        & "" & Chr(10) & "    Data1 = Source{1}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes (Data1,{{""Bidders"", type text}, {""Votes Round 1"", type text}, {""Votes Round 2"", type text}, {""Votes Round 3"", type text}, {""Votes Round 4"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    Workbooks("Import Data from Website .xlsm").Connections.Add2 _
        "Query - 2022 FIFA bidding (majority 12 votes)", _
        "Connection to the '2022 FIFA bidding (majority 12 votes)' query in the workbook." _
        , Array( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""2022 FIFA bidding (majority 12 votes)"";Extended Properties=""""" _
        , ""), "SELECT * FROM [2022 FIFA bidding (majority 12 votes)]", 2
    Application.CommandBars("Queries and Connections").Visible = False
    ActiveWorkbook.Queries.Add Name:="2022 FIFA bidding (majority 12 votes) (2)" _
        , Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://en.wikipedia.org/wiki/2022_FIFA_World_Cup""))," & Chr(13) & "" & Chr(10) & "    Data1 = Source{1}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data1,{{""Bidders"", type text}, {""Votes Round 1"", type text}, {""Votes Round 2"", type text}, {""Votes Round 3"", type text}, {""Votes Round 4"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""2022 FIFA bidding (majority 12 votes) (2)"";Extended Propertie" _
        , "s="""""), Destination:=Range("$B$2")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT * FROM [2022 FIFA bidding (majority 12 votes) (2)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "_2022_FIFA_bidding__majority_12_votes___2"
        .Refresh BackgroundQuery:=False
    End With
    Columns("A:A").ColumnWidth = 2.86
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Columns("G:G").ColumnWidth = 12
    Range("M26").Select
    Application.CommandBars("Queries and Connections").Visible = False
    Cells.Select
    Selection.ListObject.QueryTable.Delete
    Selection.ClearContents
    Range("B2:F9").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("B2").Select
End Sub

• Đây là mã macro tự động.

Cách nhập dữ liệu web bằng tab dữ liệu trong Excel

BƯỚC:

• Đặt URL trong ô C4 trong bảng tính nhập dữ liệu của chúng tôi.
• Sao chép URL bằng cách nhấn CTRL +C .

• Đi tới Dữ liệu tab từ dải băng.
• Nhấp vào Từ Web trong Nhận và chuyển đổi dữ liệu .

• Cửa sổ có tên Từ Web sẽ xuất hiện.
• Chọn Cơ bản và trong URL trường, dán URL bằng cách nhấn CTRL +V từ bàn phím của bạn.
• Nhấp vào OK nút.

• Điều này sẽ hiển thị Bộ điều hướng hộp thoại.
• Chọn bất kỳ bảng dữ liệu nào theo yêu cầu của bạn từ Tùy chọn hiển thị .
• Nhấp vào Tải .

• Thao tác này sẽ nhập dữ liệu đã chọn từ trang web đó và tải dữ liệu đó vào tệp excel của bạn.

Tải xuống sổ tay thực hành

Bạn có thể tải sách bài tập xuống và thực hành với chúng.

Bài viết liên quan

• Excel VBA:Quét web bằng Chrome
• Excel VBA để quét bảng từ trang web
• Cách thực hiện quét web không cần trình duyệt bằng Excel VBA