โอนข้อมูลง่ายๆ: 2 วิธี VBA ที่พิสูจน์แล้ว ดึงข้อมูลเว็บไซต์เข้าสู่ Excel

วิธีที่ 1 – นำเข้าข้อมูลเฉพาะจากเว็บไซต์เข้าสู่ Excel VBA MsgBox

ขั้นตอน:

• ไปที่แท็บ Developer จากริบบอน
• คลิกที่ Visual Basic จากหมวดหมู่ Code เพื่อเปิด Visual Basic Editor หรือกด ALT+F11 เพื่อเปิด Visual Basic Editor

• จะปรากฏใน Visual Basic Editor
• ไปที่เมนู Tools และคลิกที่ References

• กล่องโต้ตอบ References – VBAProject จะปรากฏขึ้น
• เลื่อนลงเล็กน้อยและเปิดใช้งาน Microsoft Library สองตัว ติ๊กถูก Microsoft HTML Object Library และ Microsoft Internet Controls
• คลิก OK

• คลิกที่ Module จากเมนูดรอปดาวน์ Insert

• สิ่งนี้จะสร้าง Module ในสมุดงานของคุณ
• คัดลอกและวางโค้ด VBA ที่แสดงด้านล่าง

โค้ด 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

• รันโค้ดโดยคลิกปุ่ม RunSub หรือกดปุ่มลัด F5

• สิ่งนี้จะดึงข้อมูล นำเข้าข้อมูลเหล่านั้นจากเว็บไซต์และแสดงใน MsgBox

คำอธิบายโค้ด VBA

Sub Import_SpecificData()

Sub เป็นส่วนของโค้ดที่ใช้จัดการงานในโค้ดแต่จะไม่คืนค่า เรียกอีกอย่างว่า subprocedure เราตั้งชื่อกระบวนการของเราว่า Import_SpecificData()

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

คำสั่ง DIM ใน VBA หมายถึง “declare” ซึ่งต้องใช้เพื่อประกาศตัวแปร

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

บรรทัดของโค้ดนำเว็บเพจใส่เข้าไปในออบเจ็กต์ HTML เพื่อให้การอ้างอิงข้อมูง่ายขึ้น

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

ด้วยบรรทัดนี้ เราจะได้ข้อมูลรวมขององค์ประกอบที่ระบุของเว็บเพจ เราต้องได้ชื่อคลาส เพื่อให้ได้ชื่อคลาสขององค์ประกอบเฉพาะนั้น เราต้องทำตามคำแนะนำง่ายๆ

• ไปที่เว็บไซต์และ คลิกขวา ที่บล็อกเฉพาะนั้น
• จากนั้น คลิกที่ Inspect

• สิ่งนี้จะแสดงองค์ประกอบ HTML และ CSS ของเว็บไซต์นั้น คลิกที่คลาสที่คุณต้องการและนำไปวางใน html.getElementsByClassName(“wikitable”)

MsgBox total

สิ่งนี้จะแสดงผลรวมในหน้าต่าง Microsoft

End Sub

สิ่งนี้จะจบกระบวนการ

วิธีที่ 2 – Scrape ข้อมูลจากเว็บไซต์เข้าสู่ Excel

ขั้นตอน:

• คลิกที่ไอคอนเล็กๆ ที่ Record Macro

• กล่องโต้ตอบ Record Macro จะปรากฏ
• ตั้งชื่อแมโคร เราตั้งชื่อแมโครว่า Get_Data
• คลิก OK

• ไปที่แท็บ Data จากริบบอน
• คลิกที่ From Web ภายใต้ Get & Transform Data

• หน้าต่าง From Web จะแสดง
• โดยการกด CTRL+V บนคีย์บอร์ด วาง URL ในพื้นที่ URL หลังจากเลือก Basic
• กดปุ่ม OK

• กล่องโต้ตอบ Navigator จะปรากฏเป็นผลลัพธ์
• เลือกตารางข้อมูลใดก็ตามจากเมนู Display Options ที่เหมาะกับความต้องการของคุณ
• เลือก “Load To”

• กล่องโต้ตอบ Import Data จะแสดง
• เลือก Table จาก “Select how you want to view this data in your workbook.” และคลิกที่ Existing worksheet
• เลือกเซลล์ที่คุณต้องการนำเข้าข้อมูล
• คลิก OK

• สิ่งนี้จะนำเข้าข้อมูลจากเว็บไซต์

• เลือกทั้งแผ่นงานและคลิกปุ่ม Delete กล่องโต้ตอบ Microsoft Excel จะปรากฏ ตรวจสอบให้แน่ใจว่าคุณคลิก No

• สิ่งนี้จะ Delete ข้อมูลทั้งหมด
• ไปที่ Data จากริบบอนและคลิก Refresh All ภายใต้ Queries & Connections
• ได้ข้อมูลทั้งหมดกลับมาในเวิร์กชีต

• เปิดริบบอนและเลือก Developer จากเมนูดรอปดาวน์
• เลือก Visual Basic เพื่อเปิด Visual Basic Editor หรือ Visual Basic Editor อาจเข้าถึงได้โดยการกด ALT+F11
• ใน Module คุณจะได้ VBA Macros อยู่ที่นั่น

โค้ด 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 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

• นี่คือโค้ดแมโครอัตโนมัติ

วิธีนำเข้าข้อมูลเว็บโดยใช้แท็บ Data ใน Excel

ขั้นตอน:

• วาง URL ในเซลล์ C4 ในเวิร์กชีตนำเข้าข้อมูลของเรา
• คัดลอก URL โดยกด CTRL+C

• ไปที่แท็บ Data จากริบบอน
• คลิกที่ From Web ภายใต้ Get & Transform Data

• หน้าต่างชื่อ From Web จะปรากฏ
• เลือก Basic และในฟิลด์ URL , วาง URL โดยกด CTRL+V จากคีย์บอร์ดของคุณ
• คลิกที่ปุ่ม OK

• สิ่งนี้จะแสดงกล่องโต้ตอบ Navigator
• เลือกตารางข้อมูลใดก็ตามตามความต้องการของคุณจาก Display Options
• คลิกที่ Load

• สิ่งนี้จะนำเข้าข้อมูลที่เลือกจากเว็บไซต์นั้นและโหลดลงในไฟล์ Excel ของคุณ

ดาวน์โหลดไฟล์ฝึกฝน

คุณสามารถดาวน์โหลดสมุดงานและฝึกฝนกับพวกเขาได้

บทความเกี่ยวข้อง

• Excel VBA: Web Scraping with Chrome
• Excel VBA to Scrape Table from Website
• How to Do Web Scraping Without Browser with Excel VBA

รับแบบฝึกหัด Excel ขั้นสูงพร้อมเฉลยฟรี!