นำเข้าข้อมูลเว็บไซต์เข้าสู่ Excel อย่างง่าย: คู่มือทีละขั้นตอน

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

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

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

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

รูป 1.1

มีลูกศรในกล่องสีเหลืองที่มุมขวาบนของกล่องโต้ตอบคิวรีเว็บใหม่ โดยคลิกที่มัน คุณสามารถกำหนดได้ว่าจะแสดงไอคอนคล้ายกันก่อนตารางหรือไม่ ตัวอย่างเช่น ไม่มีไอคอนลูกศรถัดจากตารางในแผงซ้ายของรูป 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 "https://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 ในกรณีของเรา มันคืนค่าเนื้อหาขององค์ประกอบที่มีชื่อคลาส “contact-details block dark” และตั้งค่าให้ตัวแปร htext เนื้อหา (รูป 2.2) สามารถแจ้งเตือนได้โดย ‘Msgbox htext’

รูป 2.2

คุณสามารถเห็นได้ว่าข้อความมีโครงสร้างดี นั่นคือเหตุผลที่เราสามารถใช้ฟังก์ชัน SPLIT เพื่อแยกสิ่งที่ต้องการ ตัวอย่างเช่น หากเราใช้ “<p>Company Name:” เป็นตัวคั่น และสูตร “Split(htext, “<p>Company Name: “)(1)” สามารถคืนค่าข้อความทั้งหมดหลัง “<p>Company Name:” สำหรับข้อความที่คืนค่าใหม่นี้ เราสามารถใช้ “<br” เป็นตัวคั่น และได้ข้อความก่อน “<br” แรก และนั่นคือชื่อบริษัท สรุป ฟังก์ชัน SPLIT เป็นเครื่องมือที่ยืดหยุ่นซึ่งคุณสามารถใช้เพื่อแยกเกือบทุกอย่าง ฟังก์ชันที่มีประโยชน์อื่นๆ รวมถึง LEN, INSTR, LEFT, RIGHT, MID, และ REPLACE ฉันจะไม่อภิปรายรายละเอียดที่นี่

หลังจากคุณคลิกที่ OK ในรูป 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
• [Fixed!] Method Open of Object Workbooks Failed (4 Solutions)
• Excel VBA to Populate Array with Cell Values (4 Suitable Examples)
• How to Open Workbook and Run Macro Using VBA (4 Examples)

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

ตัวอย่างข้างต้นเพียงสาธิตวิธีดึงข้อมูลจากหน้าเว็บคงที่หนึ่งหน้า แต่บ่อยครั้งที่เราต้องแยกข้อมูลได้โต้ตอบกับหน้าเว็บเพื่อดึงข้อมูลจำนวนมาก ดูที่รูป 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 "https://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 คลิกที่ชื่อแรก บริษัททั้งหมดมีชื่อรวมอยู่ในองค์ประกอบที่มีชื่อคลาส “Name” Searchobj เป็นคอลเลกชัน และ searchobj(i) สามารถคืนค่าวัตถุ (i+1)^th ได้ ตัวอย่างเช่น searchobj(1).Click สามารถช่วยให้คุณเยี่ยมชมหน้าเว็บสำหรับ RIDLEY CORPORATION (Melbourne)

ซอร์สโค้ด

'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 "https://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

ในความเป็นจริง สิ่งที่เราต้องทำคือแยกข้อมูลติดต่อสำหรับทุกบริษัทจากทุกอุตสาหกรรม ดังนั้น เราต้องใช้คำสั่ง for loop เพื่อทำภารกิจนี้ให้เสร็จ โค้ดที่สมบูรณ์มีดังนี้ และคุณสามารถหาโค้ดใน Retrieve contact information for all companies.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 "https://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 [คลิกที่ภาพเพื่อดูเต็ม]

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

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

Pull-Data-from-Web-to-Excel.rar

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

• วิธีแยกข้อมูลจากเว็บไซต์ไป Excel อัตโนมัติ?
• นำเข้าข้อมูล (ประโยค, ย่อหน้า, ตาราง, ความคิดเห็น) จาก Word ไป Excel
• 6 หนังสือ Excel VBA Programming ที่ดีที่สุด (สำหรับมือใหม่ & ผู้ใช้ขั้นสูง)
• เรียนรู้ Excel VBA Programming & Macros (บทแนะนำฟรี – ทีละขั้นตอน)
• Excel VBA Coding Tips
• What You Can Do with VBA
• Introduction to VBA Macros