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

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

CÁC BƯỚC:

• Đi đến tab Nhà phát triển (Developer) từ Ribbon.
• Nhấp vào Visual Basic từ danh mục Mã (Code) để mở Trình soạn thảo Visual Basic (Visual Basic Editor). Hoặc nhấn ALT+F11 để mở Trình soạn thảo Visual Basic.

• Cửa sổ Trình soạn thảo Visual Basic sẽ hiện ra.
• Đi đến menu Công cụ (Tools) và nhấp vào Tham chiếu (References).

• Hộp thoại Tham chiếu – VBAProject sẽ xuất hiện.
• Cuộn xuống một chút và bật hai Thư viện Microsoft (Microsoft Library). Tích chọn Microsoft HTML Object Library và Microsoft Internet Controls.
• Nhấp OK.

• Nhấp vào Module từ menu thả xuống Chèn (Insert).

• Thao tác này sẽ tạo một Module trong sổ làm việc của bạn.
• Sao chép và dán mã VBA như 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 nút RunSub hoặc phím tắt F5.

• Thao tác này sẽ lấy dữ liệu nhập từ trang web và hiển thị chúng trong MsgBox.

Giải thích mã VBA

Sub Import_SpecificData()

Sub là phần của mã dùng để xử lý công việc trong mã nhưng không trả về giá trị. Còn gọi là thủ tục con. Chúng ta đặt tên thủ tục là Import_SpecificData().

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

Lệnh DIM trong VBA ám chỉ “khai báo (declare)”, bắt buộc dùng để khai báo biến.

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

Điều này sẽ tạo đối tượng để yêu cầu trang web.

request.Open "GET", website, False

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

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

Chúng ta sẽ lấy dữ liệu mới từ trang web.

request.send

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

response = StrConv(request.responseBody, vbUnicode)

Chúng ta nhận dữ liệu phản hồi trang web vào biến.

html.body.innerHTML = response

Dòng mã này đưa trang web vào đối tượng HTML để 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ẽ lấy tổng của các phần tử được chỉ định của trang web. Chúng ta cần lấy tên lớp. Để lấy tên lớp của phần tử cụ thể đó, chúng ta làm theo hướng dẫn đơn giản.

• Đi đến trang web và chuột phải vào khối cụ thể đó.
• Sau đó, nhấp vào Kiểm tra (Inspect).

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

MsgBox total

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

End Sub

Điều này sẽ kết thúc thủ tục.

Phương pháp 2 – Thu thập dữ liệu từ trang web vào Excel (Web Scraping)

CÁC BƯỚC:

• Nhấp vào biểu tượng nhỏ Ghi macro (Record Macro).

• Hộp thoại Ghi macro (Record Macro) sẽ xuất hiện.
• Đặt tên cho macro. Chúng ta đặt tên macro là Get_Data.
• Nhấp OK.

• Đi đến tab Dữ liệu (Data) từ Ribbon.
• Nhấp vào Từ Web (From Web) dưới Lấy & Chuyển đổi dữ liệu (Get & Transform Data).

• Cửa sổ Từ Web (From Web) sẽ hiện ra.
• Bằng cách nhấn CTRL+V trên bàn phím, dán URL vào khu vực URL sau khi chọn Cơ bản (Basic).
• Nhấn nút OK.

• Hộp thoại Trình điều hướng (Navigator) sẽ xuất hiện.
• Chọn bất kỳ bảng dữ liệu nào từ menu Tùy chọn hiển thị (Display Options) phù hợp nhu cầu.
• Chọn “Tải vào (Load To).”

• Hộp thoại Nhập dữ liệu (Import Data) sẽ hiện ra.
• Chọn Bảng (Table) từ “Chọn cách bạn muốn xem dữ liệu này trong sổ làm việc.” và nhấp vào Trang tính hiện có (Existing worksheet).
• Chọn ô nơi bạn muốn nhập dữ liệu.
• Nhấp OK.

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

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

• Thao tác này sẽ Xóa tất cả dữ liệu.
• Đi đến Dữ liệu (Data) từ Ribbon và nhấp Làm mới tất cả (Refresh All) dưới Truy vấn & Kết nối (Queries & Connections).
• Lấy lại tất cả dữ liệu trong trang tính.

• Mở Ribbon và chọn Nhà phát triển (Developer) từ menu 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ể truy cập bằng cách nhấn ALT+F11.
• Trong Module, bạn sẽ nhận được VBA Macros ở đó.

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 hóa.

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

CÁC BƯỚC:

• Đặt URL vào ô C4 trong trang tính nhập dữ liệu.
• Sao chép URL bằng cách nhấn CTRL+C.

• Đi đến tab Dữ liệu (Data) từ Ribbon.
• Nhấp vào Từ Web (From Web) dưới Lấy & Chuyển đổi dữ liệu (Get & Transform Data).

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

• Thao tác này sẽ hiển thị hộp thoại Trình điều hướng (Navigator).
• Chọn bất kỳ bảng dữ liệu nào theo yêu cầu từ Tùy chọn hiển thị (Display Options).
• Nhấp vào Tải (Load).

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

Tải xuống tệp thực hành

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

Các bài viết liên quan

• Excel VBA: Cào web với Chrome
• Excel VBA để cào bảng từ trang web
• Cách cào web không dùng trình duyệt với Excel VBA

Nhận miễn phí Bài tập Excel nâng cao có lời giải!