엑셀 VBA 웹 크롤링 총정리

실습 가이드

엑셀 VBA 매크로로 원하는 웹페이지의 내용을 자동으로 수집하는 '웹 크롤링' 방법을 단계별로 알아봅니다. 본 강의에서는 네이버 뉴스 기사 목록을 스크랩하는 VBA 매크로를 직접 작성합니다. 강의는 아래 순서로 진행됩니다.

인터넷 익스플로러 업데이트가 2020년 11월 이후 중단되었습니다. 따라서 네이버를 포함한 일부 페이지는 인터넷 익스플로러 기반 크롤링이 동작하지 않을 수 있습니다. HTTP 요청 방식을 사용한 강의를 추가로 준비했으니 아래 영상 강의도 함께 참고해 주세요.

업데이트 노트

• 2019년 10월 04일
  : 엑셀 2013 이하 버전에서는 ENCODEURL 함수를 사용자 지정 함수로 추가해야 합니다. 자세한 내용은 ENCODEURL(VBA) 함수 관련 포스트를 참고하세요.
• 2020년 5월 9일
  : 테이블(table) 및 목록(ul, ol) 항목을 실시간으로 크롤링할 수 있는 IMPORTHTML 함수 추가기능을 업데이트하였습니다.

1. 시트에 웹브라우저 추가하기

개발도구 탭에서 [삽입]을 클릭한 뒤, [ActiveX 컨트롤]의 추가 도구상자를 엽니다. 추가 도구상자에서 Microsoft Web Browser를 선택하여 시트에 삽입합니다.

1. 변경해야 할 레지스트리 키 값
   {8856F961-340A-11D0-A96B-00C04FD705A2}
2. 키보드의 윈도우 키 + R을 눌러 '실행' 창을 띄운 뒤, 'Regedit'을 입력합니다.
3. 레지스트리 편집기가 실행되면, 좌측 목록에서 아래 경로로 이동합니다.
   Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft
4. Microsoft 폴더를 우클릭한 뒤, 아래 레지스트리 키 값을 붙여넣어 검색합니다.
   {8856F961-340A-11D0-A96B-00C04FD705A2}

   영상 강의에서는 InternetExplorer의 하위 폴더로 잘못 안내드렸습니다.
   반드시 Microsoft Office 하위 폴더에 있는 Compatibility Flags 레지스트리를 변경해 주세요.

   

5. 사용 중인 윈도우 버전과 설정에 따라 Compatibility Flags 레지스트리 위치가 다를 수 있습니다. 따라서 한 번에 검색되지 않을 경우 키보드 F3 키를 눌러 Compatibility Flags가 나올 때까지 반복 검색합니다.
   

   만약 윈도우 10 / 엑셀 오피스 365를 사용 중이라면, 아래 경로를 붙여넣어 바로 이동할 수 있습니다.
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\16.0\Common\COM Compatibility\{8856F961-340A-11D0-A96B-00C04FD705A2}

6. Compatibility Flags를 더블클릭한 뒤, Value Data를 '0'으로 변경합니다.
7. 모든 단계를 완료한 뒤, 엑셀을 재실행하면 웹브라우저가 정상적으로 삽입됩니다.
   

   만약 모든 단계를 진행해도 여전히 '개체를 삽입할 수 없습니다'라는 오류가 발생한다면, 관련 문제 해결 방법을 자세히 정리해 두었으니 관련 포스트를 참고해 주세요.

2. 보조 라이브러리 추가하기

인터넷 익스플로러 개체와 HTML 개체, 그리고 사용자 지정 폼 컨트롤 개체를 활용하기 위해 3개의 보조 라이브러리를 추가합니다.

Alt + F11 키를 눌러 매크로 편집창을 실행한 뒤, [도구] – [참조]를 클릭하여 아래 3개의 라이브러리를 추가해 주세요.

3. 크롤링 명령문 변수 설정하기

이번 강의에서는 총 4개의 변수를 설정합니다.

Dim MyBrowser As InternetExplorer
Dim HTMLDoc As HTMLDocument
Dim iArticle As IHTMLElement
Dim i As Long
 
'// 익스플로러 변수를 시트의 WebBrowser1 브라우저로 설정합니다.
Set MyBrowser = Sheet1.WebBrowser1

4. 크롤링 타깃 URL 쿼리문 분석

네이버에서 뉴스 기사를 스크랩하기 위해서는 URL의 쿼리문 구조를 먼저 이해해야 합니다. 검색 쿼리문은 네이버 정책에 따라 변경될 수 있으므로, 상황에 맞게 유연하게 수정해 주어야 합니다.

5. 웹브라우저에서 URL 검색하기

변수로 설정한 MyBrowser에서 Navigate 함수를 사용하여 원하는 URL로 이동합니다.

With MyBrowser
    .Navigate Sheet1.Range("B2").Value
 
    '// Wait_Browser에 대한 내용은 VBA2-3 이전 강의를 참고하세요.
    Wait_Browser MyBrowser
End With
 
'//-------------------Wait Browser 명령문 추가.
 
Sub Wait_Browser(Browser As InternetExplorer, Optional t As Integer = 1)
 
While Browser.Busy Or Browser.ReadyState <> 4
DoEvents
Wend
 
Application.Wait DateAdd("s", t, Now)
 
End Sub

[링크] MS 홈페이지 WebBrowser.Navigate 함수 상세 설명 보러가기

6. HTML 개체 분석하기

6-A. 웹브라우저의 페이지 분석 도구 사용

인터넷 익스플로러, 크롬, Edge, 파이어폭스 등 모든 웹브라우저는 페이지 분석 도구를 제공합니다. 본인이 사용하기 편한 웹브라우저 창에서 키보드 F12 키를 눌러 페이지 분석 도구를 실행합니다.

이후 분석 도구에서 '마우스 커서' 모양의 아이콘을 클릭한 뒤, 스크랩하려는 항목 위로 커서를 이동하면 해당 항목의 HTML 코드를 확인할 수 있습니다.

6-B. HTML 코드에서 원하는 개체 및 요소 찾기

네이버 뉴스 검색 페이지에서 HTML 문을 확인합니다. 각 기사를 나타내는 개체는 'sp_each_title' 클래스이며, 기사 제목과 링크는 해당 클래스의 'title' 및 'href' 요소에서 가져올 수 있습니다.

6-C. VBA에서 원하는 개체 불러오기

VBA의 HTML Object Library에서 지원하는 HTML 문서의 개체 추출 방법은 크게 3가지가 있습니다.

(이 외에도 HTML 문서를 전부 읽어 와 InStr 함수로 직접 파싱하는 방법이 있지만,
번거로운 작업이므로 불가피한 상황을 제외하고는 아래 3가지 방법을 사용합니다.)

HTML 문에서 개체의 ID와 Class 요소로 값을 반환하는 방법은 이전 네이버 유저폼 만들기 강의에서도 설명드린 적이 있습니다. 자세한 내용은 관련 링크를 참고하세요.

'// HTML 문서 추출
    Set HTMLDoc = .Document
 
    '// 최초 행값 입력
    i = 6
 
    '// HTML 개체검색
    For Each iArticle In HTMLDoc.getElementsByClassName("_sp_each_title")
        '// 요소별 값 추출
    Next

6-D. 각 개체의 요소값 추출하기

추출한 개체에서 요소별로 값을 가져옵니다. 이번에 필요한 값은 기사의 제목과 링크입니다. 따라서 'title'과 'href' 요소 값을 추출합니다. IHTMLElement 개체에서 title은 기본 속성으로 바로 불러올 수 있으며, href 요소는 getAttribute 함수를 이용해 불러옵니다.

'// HTML 개체검색
    For Each iArticle In HTMLDoc.getElementsByClassName("_sp_each_title")
        '// 요소별 값 추출
        Sheet1.Cells(i, 1) = iArticle.Title
        Sheet1.Cells(i, 2) = iArticle.getAttribute("href")
        i = i + 1
    Next

7. 셀에 하이퍼링크 추가하기

입력된 기사 링크를 클릭하면 자동으로 해당 기사로 이동할 수 있도록 각 셀에 하이퍼링크를 연결합니다. 아래 명령문을 For Each 루프 안에서 'i를 증가시키기 직전 단계'에 붙여넣습니다.

Sheet1.Hyperlinks.Add Sheet1.Cells(i, 2), Sheet1.Cells(i, 2)

8. 버튼에 매크로 삽입 및 크롤링 완료 메시지 띄우기

명령문이 종료되면 스크랩이 완료되었다는 안내 메시지를 띄워 줍니다. 아래 코드를 명령문 가장 마지막 부분에 복사하여 붙여넣습니다.

MsgBox "검색하신 단어 [" & Sheet1.Range("B1") & "] 에 대한 네이버 뉴스기사 스크랩핑을 완료하였습니다."

[개발도구] – [삽입] – [양식 컨트롤] 에서 '버튼'을 추가합니다. 이후 버튼을 우클릭한 뒤, 매크로 지정으로 이동합니다. 매크로 목록에서 'Web_scraping' 명령문을 버튼에 연결합니다. 엑셀 양식 컨트롤에 대한 자세한 내용은 관련 링크를 참고하세요.

9. 강의에 사용된 네이버 뉴스 크롤링 전체 명령문

Option Explicit
 
Sub Web_scarping()
 
Dim MyBrowser As InternetExplorer
Dim HTMLDoc As HTMLDocument
Dim iArticle As IHTMLElement
Dim i As Long
 
'// 익스플로어(XML변수 설정)
Set MyBrowser = Sheet1.WebBrowser1
 
'// 검색 (XML 요청)
With MyBrowser
    .Navigate Sheet1.Range("B2").Value
 
    Wait_Browser MyBrowser
 
    '// HTML 추출
    Set HTMLDoc = .Document
 
    i = 6
 
    '// 개체검색
    For Each iArticle In HTMLDoc.getElementsByClassName("_sp_each_title")
        '// 요소별 값 추출
        Sheet1.Cells(i, 1) = iArticle.Title
        Sheet1.Cells(i, 2) = iArticle.getAttribute("href")
        Sheet1.Hyperlinks.Add Sheet1.Cells(i, 2), Sheet1.Cells(i, 2)
        i = i + 1
    Next
 
End With
 
MsgBox "검색하신 단어 [" & Sheet1.Range("B1") & "] 에 대한 네이버 뉴스기사 스크랩핑을 완료하였습니다."
 
End Sub

10. ENCODEURL 함수 추가하기 (엑셀 2013 이하 버전)

엑셀 2013 이전 버전 사용자는 아래 ENCODEURL 사용자 지정 함수를 추가해야 합니다.

Function ENCODEURL(varText As Variant, Optional blnEncode = True)
 
'############################################################
'한글/영문 텍스트를 URL 주소로 변경합니다.
'https://www.oppadu.com/vba-encodeurl-함수/
'############################################################
 
Static objHtmlfile As Object
 
If objHtmlfile Is Nothing Then
    Set objHtmlfile = CreateObject("htmlfile")
    With objHtmlfile.parentWindow
    .execScript "function encode(s) {return encodeURIComponent(s)}", "jscript"
    End With
End If
 
If blnEncode Then
    ENCODEURL = objHtmlfile.parentWindow.encode(varText)
End If
 
End Function

자주묻는질문

Q1. 웹페이지 전체의 서식을 유지한 채 시트에 붙여넣고 싶어요

인터넷 창에 보이는 페이지 전체를 서식이 유지된 상태로 엑셀에 붙여넣고 싶다면 아래 코드를 응용해 보세요.

단, 페이지 크기와 페이지에 사용된 개체 수(체크박스, 텍스트박스 등)에 따라 동작 속도가 느려질 수 있으므로 주의가 필요하며, 상황에 따라 적절히 수정하여 사용할 수 있습니다.

아래 명령문은 With MyBrowser ~ End With 사이에 붙여넣어 사용합니다. 수정된 명령문을 실행하면 웹페이지 전문을 복사한 뒤 새로 생성한 시트에 서식을 유지한 채 붙여넣습니다.

'// <--!기존 명령문의 With MyBrowser ~ End With 를 아래 명령문으로 대체합니다 -->
'################################
' 웹페이지의 크기, 사용된 개체수에 따라 동작속도가 느려질 수 있으므로 주의합니다.
' 새로운 시트를 생성한 뒤 웹페이지 전문을 복사/붙여넣기 합니다
'################################
With MyBrowser
    .Navigate Sheet1.Range("C3").Value
 
    Wait_Browser MyBrowser
 
    '// HTML 추출
    Set HTMLDoc = .Document
    .Document.execCommand "SelectAll"
    .Document.execCommand "Copy"
 
    Application.ScreenUpdating = False
    Dim newWS As Worksheet
    Set newWS = ThisWorkbook.Worksheets.Add
 
    newWS.PasteSpecial , False, False
    Application.ScreenUpdating = True
 
End With

Q2. 여러 개의 클래스를 가진 개체를 불러오고 싶습니다

크롤링 작업 시, 특별한 경우를 제외한 대부분의 경우 Tag와 Class 항목을 기준으로 값을 불러옵니다. Tag 값은 1개로 고정되어 있어 문제가 되지 않지만, 여러 개의 클래스를 가진 개체를 불러올 때는 어떻게 처리해야 할까요?

우선 한 가지 팁을 드리자면, 여러 개의 클래스가 입력된 경우 대부분은 마지막에 입력된 클래스만으로 값을 불러와도 정상적으로 동작하는 경우가 많습니다.

AND 조건으로 여러 개의 CLASS를 구분해야 할 경우

하지만 반드시 두 개의 클래스를 동시에 지정해야 하는 상황이라면, .getElementsByClassName에서 클래스를 '띄어쓰기'로 구분하여 입력합니다. 띄어쓰기로 클래스를 구분하면 .getElementsByClassName은 여러 개의 클래스를 "AND" 조건으로 묶어 불러옵니다.

OR 조건으로 여러 개의 CLASS를 구분해야 할 경우

그렇다면 AND 조건이 아니라 OR 조건으로 여러 개의 클래스를 불러와야 할 경우에는 어떻게 해야 할까요?

만약 XML을 이용하고 헤더 값으로 최신 버전의 Agent를 사용하여 HTML 문을 읽어 왔다면 .querySelectorAll 함수를 이용할 수 있습니다. 예를 들어 A 클래스 또는 B 클래스 개체를 모두 불러오고 싶을 때는 아래와 같이 함수를 입력합니다.

하지만 엑셀 VBA에서 제공하는 Internet Explorer 라이브러리를 사용한다면 .querySelectorAll 함수의 사용이 제한됩니다. (querySelectorAll 함수는 IE8 버전 이상에서만 동작합니다.)

따라서 본 강의에서 설명드린 인터넷 익스플로러 기반 명령문에서 OR 조건으로 여러 개의 CLASS를 불러오기 위해서는 2가지 대안을 고려할 수 있습니다.

• For Each 문을 여러 번 작성합니다.

  For Each iArticle In HTMLDoc.getElementsByClassName("A클래스")
  '// 본문입력
  Next
   
  For Each iArticle In HTMLDoc.getElementsByClassName("B클래스")
  '// 본문입력
  Next
   
  '// 반복...

• 또는 추가 명령문을 작성하여 코드를 간소화합니다.

  Sub Get_MultipleClasses(HTMLDoc As HTMLDocument, Classes as Variant)
   
  Dim iArticle As IHTMLElement
  Dim Class as Variant
   
  For Each Class in Classes
  	For Each iArticle In HTMLDoc.getElementsByClassName(Class)
  	'//본문입력
  	Next
  Next
   
  End Sub