วิธีการนำเข้าข้อมูลจากเว็บไซต์ไปยัง Excel

เป็นที่ทราบกันดีว่าเวิลด์ไวด์เว็บมีข้อมูลที่เป็นประโยชน์มากมาย อย่างไรก็ตาม เราต้องนำเข้าข้อมูลไปยัง Microsoft Excel ก่อนทำการวิเคราะห์ใดๆ มีสองวิธีที่คุณสามารถใช้เพื่อทำงานประเภทนี้ให้สำเร็จ

รับข้อมูลภายนอกจากเว็บด้วยตนเอง

สมมติว่าคุณต้องการดาวน์โหลดรายได้เกี่ยวกับภาพยนตร์ทำเงินชั้นนำจากหน้าเว็บนี้ ต่อไปนี้จะแสดงวิธีการทำได้อย่างง่ายดาย

เปิด Microsoft Excel และคลิกที่ ข้อมูล ในแท็บ รับข้อมูลภายนอก กลุ่ม คลิก จากเว็บ . หลังจาก แบบสอบถามเว็บใหม่ กล่องโต้ตอบปรากฏขึ้น ให้คัดลอกที่อยู่เว็บ (http://www.the-numbers.com/movie/records/All-Time-Worldwide-Box-Office) ลงใน Address และคลิกที่ ไป ปุ่ม. Excel จะเริ่มดาวน์โหลดหน้าเว็บดังแสดงในรูปที่ 1.1 เพียงคลิกที่ ไม่ หากคุณจะได้รับ ข้อผิดพลาดของสคริปต์ กล่องคำเตือนซึ่งคล้ายกับรูปด้านล่าง กล่องจะหายไปและไม่มีผลกระทบต่อกระบวนการนำเข้าของคุณ

รูปที่ 1.1

มีลูกศรในกล่องสีเหลืองที่มุมบนขวาของกล่องโต้ตอบ New Web Query เมื่อคลิกที่ไอคอน คุณสามารถระบุได้ว่าจะแสดงไอคอนที่คล้ายกันก่อนตารางหรือไม่ ตัวอย่างเช่น ไม่มีไอคอนลูกศรถัดจากตารางในแผงด้านซ้ายของรูปที่ 1.2 และจะมีไอคอน (ในแผงด้านขวา) หลังจากที่คุณคลิกที่ปุ่มลูกศรเพื่อแสดงไอคอน

รูปที่ 1.2 [คลิกที่ภาพเพื่อดูภาพเต็ม]

รูปที่ 1.3 [คลิกที่ภาพเพื่อดูภาพเต็ม]

รูปที่ 1.4

และคุณยังสามารถตั้งค่าข้อมูลการสืบค้นของคุณเพื่อรีเฟรชในแบบที่คุณต้องการ เพียงคลิกขวาที่เซลล์ใดก็ได้ในตาราง เลือก คุณสมบัติของช่วงข้อมูล . ใน คุณสมบัติของช่วงข้อมูลภายนอก . ที่แจ้ง กล่องโต้ตอบ คุณสามารถเปลี่ยน รีเฟรชการควบคุม การตั้งค่าเพื่อเปลี่ยนรูปแบบ ตัวอย่างเช่น คุณสามารถระบุคิวรีที่จะรีเฟรชทุก 60 นาทีหรือเมื่อเปิดไฟล์

ขูดข้อมูลโดยใช้การเขียนโปรแกรม VBA

การเขียนโปรแกรม VBA สามารถใช้เพื่อดึงข้อมูลจากหน้าเว็บ มีประโยชน์มากกว่าในขณะที่ยากเมื่อเปรียบเทียบกับวิธีแรก นอกจากนี้ คุณต้องเข้าใจว่า HTML คืออะไร ก่อนที่จะเรียนรู้วิธีขูดข้อมูลโดยใช้การเขียนโปรแกรม VBA ฉันแนะนำให้คุณเรียนรู้ความรู้พื้นฐานเกี่ยวกับ HTML จากเว็บไซต์นี้ หากคุณไม่รู้อะไรเลยเกี่ยวกับ HTML หรือเพียงเล็กน้อย และจากที่นี่ คุณสามารถเรียนรู้เกือบทุกอย่างที่เกี่ยวข้องกับการขูดเว็บโดยใช้ VBA บทความนี้จะแสดงให้คุณเห็นเพียงสองตัวอย่างเท่านั้น

ขูดข้อมูลจากหน้าเว็บเดียว

สมมติว่าเราต้องการแยกชื่อบริษัท ที่อยู่อีเมล และชื่อผู้ติดต่อออกจากหน้าเว็บนี้ หากคุณเปิดหน้าเว็บนี้ คุณจะพบว่ามีบล็อกการติดต่อที่ด้านล่าง รูปที่ 2.1 แสดงบล็อคการติดต่อและซอร์สโค้ดที่เกี่ยวข้อง ข้อมูลในกล่องสีแดงคือสิ่งที่เราต้องการ และเส้นที่ขีดเส้นใต้สีเขียวคือสิ่งที่เราต้องแยกออกมา

รูปที่ 2.1

รหัสต่อไปนี้สามารถช่วยคุณดึงข้อมูลที่ร้องขอด้านบนและใส่ไว้ในแผ่นงานแรก

ซอร์สโค้ด

Sub Retrieve_Click()
 
'Create InternetExplorer

Set IE = CreateObject("InternetExplorer.Application")
 
'Let's not see the browser window

IE.Visible = False
 
'Open the web page

IE.Navigate "http://www.austrade.gov.au/SupplierDetails.aspx?ORGID=ORG8160044431&folderid=1736"
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop
 
'Retrieve company name, email address & contact information

Set contactobj = IE.document.getElementsByClassName("contact-details block dark")
 
htext = contactobj(0).innerHTML
 
MsgBox htext
 
If InStr(htext, "<p>Company Name: ") Then
 
ThisWorkbook.Worksheets(1).Cells(1, 1) = Split(Split(htext, "<p>Company Name: ")(1), "<br")(0)
 
End If
 
If InStr(htext, "mailto:") Then
 
ThisWorkbook.Worksheets(1).Cells(2, 1) = Split(Split(htext, "mailto:")(1), Chr(34) & ">")(0)
 
End If
 
If InStr(htext, "<p>Name: ") Then
 
ThisWorkbook.Worksheets(1).Cells(3, 1) = Split(Split(htext, "<p>Name: ")(1), "<br")(0)
 
End If
 
ThisWorkbook.Worksheets(1).Cells(4, 1) = IE.LocationURL
 
ThisWorkbook.Save
 
Set IE = Nothing
 
Set contactobj = Nothing
 
End Sub

"IE.document.getElementsByClassName("Contact-details block dark")" ช่วยให้คุณสามารถรับองค์ประกอบทั้งหมดที่มีชื่อคลาส - contact-details block dark หน้าเว็บนี้แสดงรายการคุณสมบัติและวิธีการเกือบทั้งหมดที่สามารถใช้กับองค์ประกอบ HTML คุณสามารถเลือกสิ่งที่เหมาะสมสำหรับปัญหาของคุณเองได้

คุณสมบัติ innerHTML สามารถอนุญาตให้คุณตั้งค่าหรือส่งคืนเนื้อหาขององค์ประกอบ HTML ในกรณีของเรา มันส่งคืนเนื้อหาขององค์ประกอบด้วยชื่อคลาส "รายละเอียดการติดต่อบล็อกมืด" และตั้งค่าเป็นตัวแปร htext เนื้อหา (รูปที่ 2.2) สามารถแสดงได้โดย "Msgbox htext"

รูปที่ 2.2

คุณจะเห็นว่าข้อความมีโครงสร้างที่ดี นั่นเป็นเหตุผลที่เราสามารถใช้ฟังก์ชัน SPLIT เพื่อแยกสิ่งที่ต้องการได้ ตัวอย่างเช่น หากเราใช้ “

ชื่อบริษัท:” เป็นตัวคั่นและสูตร “แยก(htext, “

ชื่อบริษัท:“)(1)” สามารถส่งคืนข้อความทั้งหมดหลังจาก “

ชื่อบริษัท:” สำหรับข้อความที่ส่งคืนใหม่นี้ เราสามารถใช้ “

หลังจากที่คุณคลิกตกลงในรูปที่ 2.2 ข้อมูลที่ร้องขอสามารถนำเข้าจากเว็บไปยังแผ่นงาน Excel ตัวอย่างเช่น เซลล์ A1 มีชื่อบริษัท ในขณะที่เซลล์ A4 มีที่อยู่เว็บเพจของบริษัท

รูปที่ 2.3

การเพิ่มโค้ดด้านล่างก่อนที่คุณจะบันทึกเวิร์กบุ๊ก คุณสามารถเพิ่มไฮเปอร์ลิงก์ไปยังเซลล์ A4 ได้

ซอร์สโค้ด

'Add hyperlink

ThisWorkbook.Worksheets(1).Hyperlinks.Add ThisWorkbook.Worksheets(1).Cells(4, 1), ThisWorkbook.Worksheets(1).Cells(4, 1)

หากคุณคลิกที่เซลล์ A4 คุณสามารถย้อนกลับไปยังหน้าเว็บได้ สิ่งนี้มีประโยชน์โดยเฉพาะอย่างยิ่งเมื่อคุณต้องดึงข้อมูลสำหรับบริษัทจำนวนมาก เนื่องจากคุณสามารถคลิกที่ไฮเปอร์ลิงก์ใดๆ เพื่อกลับมายังหน้าเว็บที่เกี่ยวข้องอีกครั้ง และเพิ่มหรืออัปเดตข้อมูลใดๆ ด้วยตนเองในระหว่างการตรวจทานในภายหลัง

รูปที่ 2.4

การอ่านที่คล้ายกัน

• วิธีการเปิดสมุดงานอื่นและคัดลอกข้อมูลด้วย Excel VBA
• [แก้ไขแล้ว!] วิธีการเปิดสมุดงานออบเจ็กต์ล้มเหลว (4 โซลูชัน)
• Excel VBA เพื่อเติมข้อมูลอาร์เรย์ด้วยค่าเซลล์ (4 ตัวอย่างที่เหมาะสม)
• วิธีการเปิดสมุดงานและเรียกใช้มาโครโดยใช้ VBA (4 ตัวอย่าง)

โต้ตอบกับหน้าเว็บ

ตัวอย่างข้างต้นแสดงให้เห็นถึงวิธีการดึงข้อมูลจากหน้าเว็บแบบคงที่ แต่บ่อยครั้งที่เราจำเป็นต้องดึงข้อมูลที่มีการโต้ตอบกับหน้าเว็บเพื่อดึงข้อมูลจำนวนมาก ดูรูปที่ 3.1 จะแสดงวิธีไปที่หน้าเว็บของตัวอย่างข้างต้น จะเห็นได้ว่ามีหลายอุตสาหกรรมและแต่ละอุตสาหกรรมก็มีบริษัทมากมาย ตัวอย่างเช่น มีบริษัทในอุตสาหกรรมการเกษตร 651 แห่ง เราควรทำอย่างไรหากต้องการดึงข้อมูลติดต่อของบริษัททั้งหมดจากทุกอุตสาหกรรม?

รูปที่ 3.1 [คลิกที่ภาพเพื่อดูภาพเต็ม]

หลังจากป้อนรหัสต่อไปนี้ใน Visual Basic Editor และส่งรหัสแล้ว คุณจะเห็นว่า IE ของคุณเปิดอยู่ หน้าเว็บแรกปรากฏขึ้นตามด้วยหน้าเว็บที่สอง ที่นี่ คุณจะได้เรียนรู้วิธีรับองค์ประกอบรายการแบบหล่นลง การเลือกตัวเลือก และทริกเกอร์เหตุการณ์หลังจากที่คุณเลือกตัวเลือก “m =IE.document.getElementsByTagName(“option”).Length – 1” จะให้ตัวเลือกทั้งหมดแก่คุณ สามารถใช้สำหรับลูปถัดไปได้

ซอร์สโค้ด

Sub retrieve()
 
'Create InternetExplorer

Set IE = CreateObject("InternetExplorer.Application")
 
'Let's see the browser window

IE.Visible = True
 
'Open the web page

IE.Navigate "http://www.austrade.gov.au/international/buy#"
 
'Wait while IE is loading

Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
'Part 1 - Select dropdown list and trigger event after you select one option

Set selectobj = IE.document.getElementsByTagName("select")
 
m = IE.document.getElementsByTagName("option").Length - 1
 
selectobj(0).selectedIndex = 1
 
selectobj(0).FireEvent ("onchange")
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
End Sub

รหัสส่วนนี้สามารถนำคุณไปยังหน้าเว็บที่แสดงในรูปที่ 2.1 หลังจากที่ Excel คลิกที่ชื่อ ชื่อบริษัททั้งหมดรวมอยู่ในองค์ประกอบที่มีชื่อคลาสเป็น "ชื่อ" Searchobj คือคอลเล็กชันและ searchobj(i) สามารถส่งคืน (i+1) ^th วัตถุ. ตัวอย่างเช่น searchobj(1).Click ช่วยให้คุณสามารถเยี่ยมชมหน้าเว็บของ RIDLEY CORPORATION (เมลเบิร์น)

ซอร์สโค้ด

'Part 2 - Select company Name

Set searchobj = IE.document.getElementsByClassName("Name")
 
searchobj(0).Click
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop

สุดท้าย นี่คือโค้ดทั้งหมดที่สามารถแสดงให้คุณเห็นกระบวนการเปิด IE เรียกดูหน้าเว็บ และดึงข้อมูล ข้อมูลที่ดึงออกมาจะเหมือนกับในรูปที่ 2.4

ซอร์สโค้ด

Sub Retrieve()
 
'Create InternetExplorer

Set IE = CreateObject("InternetExplorer.Application")
 
'Let's see the browser window

IE.Visible = True
 
'Open the web page

IE.Navigate "http://www.austrade.gov.au/international/buy#"
 
'Wait while IE is loading

Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
'Part 1 - Select dropdown list and trigger event after you select one option

Set selectobj = IE.document.getElementsByTagName("select")
 
m = IE.document.getElementsByTagName("option").Length - 1
 
selectobj(0).selectedIndex = 1
 
selectobj(0).FireEvent ("onchange")
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
'Part 2 - Select company Name

Set searchobj = IE.document.getElementsByClassName("Name")
 
searchobj(0).Click
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop
 
'Part 3 - Retrieve company name, email address & contact information

Set contactobj = IE.document.getElementsByClassName("contact-details block dark")
 
htext = contactobj(0).innerHTML
 
If InStr(htext, "<p>Company Name: ") Then
 
ThisWorkbook.Worksheets(1).Cells(1, 1) = Split(Split(htext, "<p>Company Name: ")(1), "<br")(0)
 
End If
 
If InStr(htext, "mailto:") Then
 
ThisWorkbook.Worksheets(1).Cells(2, 1) = Split(Split(htext, "mailto:")(1), Chr(34) & ">")(0)
 
End If
 
If InStr(htext, "<p>Name: ") Then
 
ThisWorkbook.Worksheets(1).Cells(3, 1) = Split(Split(htext, "<p>Name: ")(1), "<br")(0)
 
End If
 
ThisWorkbook.Worksheets(1).Cells(4, 1) = IE.LocationURL
 
'Add hyperlink

ThisWorkbook.Worksheets(1).Hyperlinks.Add ThisWorkbook.Worksheets(1).Cells(4, 1), ThisWorkbook.Worksheets(1).Cells(4, 1)
 
End Sub

อันที่จริงแล้ว สิ่งที่เราต้องทำคือดึงข้อมูลการติดต่อของทุกบริษัทจากทุกอุตสาหกรรม ดังนั้น เราจำเป็นต้องใช้คำสั่งวนซ้ำเพื่อทำงานนี้ให้เสร็จ ต่อไปนี้เป็นรหัสที่สมบูรณ์ และคุณยังสามารถค้นหารหัสในดึงข้อมูลติดต่อสำหรับ บริษัท ทั้งหมด.xlsm ซึ่งคุณสามารถดาวน์โหลดได้ที่ส่วนท้ายของบทความนี้

ซอร์สโค้ด

Sub Retrieve()
 
For idex = 2 To 18
 
'Create InternetExplorer

Set IE = CreateObject("InternetExplorer.Application")
 
'Let's see the browser window

IE.Visible = False
 
'Open the web page

IE.Navigate "http://www.austrade.gov.au/international/buy#"
 
'Wait while IE is loading

Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
idexn = idex - 1
 
'Part 1 - Select dropdown

Set selectobj = IE.document.getElementsByTagName("select")
 
m = IE.document.getElementsByTagName("option").Length - 1
 
selectobj(0).selectedIndex = idexn
 
selectobj(0).FireEvent ("onchange")
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
wurl = IE.LocationURL
 
tot = IE.document.getElementsByClassName("SearchTotal")(0).innerHTML
 
pg = Int(tot / 25) + 1
 
Max = (tot Mod 25) - 1
 
'Part 2 - Select Class = "Name"

a = 2
 
For j = 1 To pg
 
If j = 1 Then
 
IE.Navigate (wurl)
 
Else
 
IE.Navigate (wurl & "&pg=" & j)
 
End If
 
Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
If j <> pg Then
 
For i = 1 To 24
 
Set searchobj = IE.document.getElementsByClassName("Name")
 
searchobj(i).Click
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop
 
'Part 3 - Retrieve company name, email address & contact information

Set contactobj = IE.document.getElementsByClassName("contact-details block dark")
 
htext = contactobj(0).innerHTML
 
ThisWorkbook.Worksheets(idex).Cells(a, 1) = j
 
ThisWorkbook.Worksheets(idex).Cells(a, 2) = a - 1
 
If InStr(htext, "<p>Company Name: ") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 3) = Split(Split(htext, "<p>Company Name: ")(1), "<br")(0)
 
End If
 
If InStr(htext, "mailto:") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 4) = Split(Split(htext, "mailto:")(1), Chr(34) & ">")(0)
 
End If
 
If InStr(htext, "<p>Name: ") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 5) = Split(Split(htext, "<p>Name: ")(1), "<br")(0)
 
End If
 
ThisWorkbook.Worksheets(idex).Cells(a, 6) = IE.LocationURL
 
IE.GoBack
 
Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
a = a + 1
 
Next i
 
Else
 
For i = 0 To Max
 
Set searchobj = IE.document.getElementsByClassName("Name")
 
searchobj(i).Click
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop
 
'Part 3 - Retrieve company name, email address & contact information

Set contactobj = IE.document.getElementsByClassName("contact-details block dark")
 
htext = contactobj(0).innerHTML
 
ThisWorkbook.Worksheets(idex).Cells(a, 1) = j
 
ThisWorkbook.Worksheets(idex).Cells(a, 2) = a - 1
 
If InStr(htext, "<p>Company Name: ") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 3) = Split(Split(htext, "<p>Company Name: ")(1), "<br")(0)
 
End If
 
If InStr(htext, "mailto:") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 4) = Split(Split(htext, "mailto:")(1), Chr(34) & ">")(0)
 
End If
 
If InStr(htext, "<p>Name: ") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 5) = Split(Split(htext, "<p>Name: ")(1), "<br")(0)
 
End If
 
ThisWorkbook.Worksheets(idex).Cells(a, 6) = IE.LocationURL
 
ThisWorkbook.Worksheets(idex).Hyperlinks.Add ThisWorkbook.Worksheets(idex).Cells(a, 6), ThisWorkbook.Worksheets(idex).Cells(a, 6)
 
IE.GoBack
 
Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
a = a + 1
 
Next i
 
End If
 
ThisWorkbook.Save
 
Next j
 
Set IE = Nothing
 
Set contactobj = Nothing
 
Next idex
 
End Sub

จุดเดียวที่ฉันต้องอธิบายคือแสดงในรูปที่ 3.2 เว็บเดียวสามารถระบุรายชื่อบริษัทได้มากถึง 25 บริษัทเท่านั้น เมื่อจำนวนบริษัททั้งหมดมากกว่า 25 บริษัทจะมีมากกว่าหนึ่งหน้า รูปที่ 3.2 แสดงว่ามีกฎการรับที่อยู่ของหน้าหลังหน้าแรก เป็นผลจากการต่อที่อยู่ของหน้าแรก "&pg=" และหมายเลขหน้าจริง และสำหรับทุกหน้าก่อนหน้าสุดท้าย จำนวนออบเจ็กต์ทั้งหมดคือ 25  “IE.document.getElementsByClassName(“SearchTotal”)(0).innerHTML” สามารถส่งคืนจำนวนบริษัททั้งหมดในอุตสาหกรรมได้ ในกรณีของเรา มันจะเป็น 651 “Int(tot / 25) + 1” จะทำให้คุณได้จำนวนหน้าทั้งหมด และ “Max =(tot Mod 25) – 1” สามารถส่งคืนจำนวนบริษัทสูงสุดในหน้าสุดท้าย ฉันจะหยุดที่นี่และปล่อยให้คุณคิดหาวิธีนำแนวคิดนี้ไปใช้กับรหัส เป็นวิธีที่ดีกว่ามากสำหรับคุณในการเข้าใจโค้ด หากมีข้อสงสัยสามารถแสดงความคิดเห็นได้

รูปที่ 3.2

นี่จะแสดงให้คุณเห็นส่วนหนึ่งของ excel ขั้นสุดท้าย ข้อมูลติดต่อของทุกบริษัทในอุตสาหกรรมเดียวกันถูกรวมไว้ในใบงานเดียว

รูปที่ 3.3 [คลิกที่ภาพเพื่อดูภาพเต็ม]

ดาวน์โหลดไฟล์การทำงาน

ดาวน์โหลดไฟล์การทำงานจากลิงค์ด้านล่าง

ดึงข้อมูลจากเว็บไปยัง Excel.rar

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

• วิธีการดึงข้อมูลจากเว็บไซต์ไปยัง Excel โดยอัตโนมัติ
•  นำเข้าข้อมูล (ประโยค ย่อหน้า ตาราง ความคิดเห็น) จาก Word ไปยัง Excel
• หนังสือการเขียนโปรแกรม Excel VBA ที่ดีที่สุด 6 เล่ม (สำหรับผู้เริ่มต้นและผู้ใช้ขั้นสูง)
• เรียนรู้การเขียนโปรแกรม Excel VBA และมาโคร (บทช่วยสอนฟรี – ทีละขั้นตอน)
• เคล็ดลับการเข้ารหัส Excel VBA
• คุณสามารถทำอะไรกับ VBA
• แนะนำ VBA Macros