엑셀 VBA 크롤링 총정리 및 실전예제 :: 네이버 뉴스 크롤링 매크로

강의 요약

엑셀 VBA 매크로 명령문으로 원하는 홈페이지의 내용을 '크롤링'하는 방법을 단계별로 알아봅니다. 본 강의에서는 네이버 뉴스 기사를 스크랩하는 VBA 매크로를 작성해봅니다. 이번 강의는 아래 순서로 진행됩니다.

업데이트 노트
  • 2019년 10월 04일
    : 엑셀 2013 이하 버전에서는 ENCODEURL 함수를 사용자지정함수로 추가해야합니다. 자세한 내용은 ENCODEURL(VBA) 함수 관련 포스트를 참고하세요.
완성파일 다운로드
파일구분마지막수정일파일형식파일다운로드
10완성파일2020-01-22XLSM퀵 VBA 파일다운로드
9완성파일2020-01-06XLSM퀵 VBA 파일다운로드
8완성파일2019-12-16ZIP퀵 VBA 파일다운로드
7E-Book2019-11-24PDF퀵 VBA 파일다운로드
7예제파일2019-11-22XLSX퀵 VBA 파일다운로드
6E-Book2019-11-26PDF퀵 VBA 파일다운로드
6예제파일2019-10-26XLSX퀵 VBA 파일다운로드
5E-Book2019-12-15PDF퀵 VBA 파일다운로드
5예제파일2019-12-15XLSX퀵 VBA 파일다운로드
4E-Book2019-10-30PDF퀵 VBA 파일다운로드
4완성파일2019-10-30ZIP퀵 VBA 파일다운로드
3E-Book2019-10-30PDF퀵 VBA 파일다운로드
3예제파일2019-10-30XLSX퀵 VBA 파일다운로드
2완성파일2019-12-22XLSM퀵 VBA 파일다운로드
1완성파일2019-11-27XLSM퀵 VBA 파일다운로드
영상강의

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

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

엑셀 VBA 크롤링 웹 브라우저 추가

Microsoft Web Browser 컨트롤을 시트위에 삽입합니다.

주의사항

엑셀 2013 이후 버전 사용자는 Web Browser Control을 삽입할 때 Windows 보안문제로 오류가 발생합니다. 따라서 아래 순서대로 레지스트리 값을 변경해줘야 합니다.

  1. 변경해야 할 레지스트리 값
    {8856F961-340A-11D0-A96B-00C04FD705A2}
  2. 키보드의 윈도우키 + R 을 눌러 '실행' 창을 불러온 뒤, 'Regedit'을 입력합니다.
    엑셀 VBA 웹 브라우저 삽입 레지스트리 키 변경

    실행창에서 'Regedit'을 입력합니다.

  3. 레지스트리 편집기가 실행되면, 좌측의 목록에서 아래 적어드린 경로로 이동합니다.
    Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft
  4. MicroSoft 폴더를 우클릭한 뒤, 아래 적어드린 레지스트리 키값을 붙여넣기하여 검색합니다.
    {8856F961-340A-11D0-A96B-00C04FD705A2}

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

    엑셀 VBA 웹 브라우저 레지스트리 키 검색

    레지스트리 키값을 검색합니다ㅏ. 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'으로 변경합니다.
    엑셀 웹 브라우저 삽입 안됨 레지스트리 변경

    ValueData를 0으로 변경한 뒤 확인을 눌러주세요.

  7. 모든 단계를 완료한 뒤, 엑셀을 재실행하면 웹브라우저가 정상적으로 삽입됩니다.

    만약 모든 단계를 진행해도 여전히 '개체를 삽입할 수 없습니다' 라는 오류가 발생할 경우, 관련문제 해결방법을 더욱 자세히 적어드렸으니 관련포스트를 참고해주세요.

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

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

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

  1. Microsoft Internet Controls : 인터넷 익스플로러(웹 브라우저)를 사용하기 위한 라이브러리입니다. 시트 또는 유저폼에서 Web Browser를 삽입하면 자동으로 추가됩니다.
  2. Microsoft HTML Object Library : HTML 개체를 사용하기 위한 라이브러리입니다. 사용자가 직접 선택하여 추가합니다.
  3. Microsoft Forms 2.0 Object Library : 유저폼 개체 (버튼, 콤보박스 등..) 을 활용하기 위한 라이브러리입니다. [삽입] – [유저폼]을 클릭하여 유저폼을 생성하면 자동으로 추가됩니다.
엑셀 크롤링 라이브러리 추가

[도구] – [참조]로 이동하여 3개의 라이브러리가 추가되었는지 확인합니다.

3. 변수 설정하기

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

  • MyBrowser As InternetExplorer : 웹브라우저 개체를 나타냅니다.
  • HTMLDoc As HTMLDocument : 웹브라우저에서 추출해올 HTML 문서입니다.
  • iArticle As IHTMLElement : HTML문서에서 각 Class별로 추출할 개체입니다.
  • i As Long : 각 개체에서 추출한 값을 출력할 행 번호를 나타냅니다.

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

네이버에서 뉴스기사를 스크랩해오기 위해서는 URL의 쿼리문을 이해해야합니다. 검색 쿼리문은 네이버 검색 정책에 따라 바뀔 수도 있으며, 상황에 따라 유기적으로 수정해줘야 합니다.

4-A. 검색 항목 및 검색할 단어 쿼리문 분석하기
  • 네이버에서 '유튜브'를 검색한 뒤, '뉴스 더보기' 버튼을 클릭하여 뉴스 페이지로 이동합니다. 그럼 아래의 URL 주소로 이동합니다.
    search.naver.com/search.naver?&where=news&query=%EC%9C%A0%ED%8A%9C%EB%B8%8C&sm=tab_tmr&frm=mr&nso=so:r,p:all,a:all&sort=0
  • Where=news 요소는 네이버 검색 항목 중, 뉴스 페이지를 보여주는 항목으로 알 수 있습니다.
  • Query='URL인코딩(유튜브)' 요소는 해당 검색어 결과값을 출력하는 항목으로 이해할 수 있습니다.
  • 그 외에 나머지 항목들은 크게 중요하지 않으므로 아래 URL링크만 복사하여 URL 검색을 해봅시다.
    search.naver.com/search.naver?&where=news&query=%EC%9C%A0%ED%8A%9C%EB%B8%8C
  • 나머지 항목을 지웠지만, 이사없이 원하는 단어로 잘 검색되는 것을 확인할 수 있습니다.
4-B. 검색할 페이지 번호 쿼리문 분석하기
  • 이번에는 페이지 이동을 해보겠습니다. 2번째 페이지로 이동하면, 아래와 같은 URL로 이동합니다.
    search.naver.com/search.naver?&where=news&query=%EC%9C%A0%ED%8A%9C%EB%B8%8C&sm=tab_pge&sort=0&photo=0&field=0&reporter_article=&pd=0&ds=&de=&docid=&nso=so:r,p:all,a:all&mynews=0&cluster_rank=14&start=11&refresh_start=0
  • Where 항목과 Query 항목은 이전과 동일합니다.
  • 그 뒤에 또 다른 여러 항목들이 추가되었으나, 자세히보면 페이지 이동과 관련있어보이는 항목은 'Start' 항목인 것을 볼 수 있습니다.
  • 이번엔 쿼리문을 아래와 같이 작성하여 검색을 해보겠습니다. 마찬가지로 이상없이 잘 검색되는 것을 볼 수 있습니다.
    search.naver.com/search.naver?&where=news&query=%EC%9C%A0%ED%8A%9C%EB%B8%8C&start=11
  • Start 값을 12, 13으로 변경하면 각 순번에 해당하는 기사를 페이지의 첫번째 기사로 출력합니다.

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

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

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

6. HTML 개체 분석하기

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

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

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

크롬 크롤링 HTML 페이지분석 툴 사용

웹브라우저의 페이지분석툴을 활용하면 HTML코드를 쉽게 분석할 수 있습니다.

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

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

  • 기사 개체 : sp_each_title 클래스
  • 기사 제목 : 해당 클래스의 Title 요소
  • 기사 링크 : 해당 클래스의 href 요소
네이버 크롤링 HTML 개체 확인

원하는 개체 위로 마우스커서를 이동하면 해당 개체의 HTML 코드를 확인할 수 있습니다.

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

VBA 의 HTML Object Library에서 지원하는 HTML문서의 각 개체를 끌어오는 방법은 크게 3가지입니다.

(그 외에, HTML 문서를 전부 읽어와 Instr 함수를 사용해 수작업하는 방법도 있습니다만,
상당히 번거로운 작업이므로 불가피한 상황을 제외하곤 아래 3가지 방법을 사용합니다.)

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

  1. .getElementById
    : HTML문서에서 개체의 ID 값으로 개체를 찾아 반환합니다. 하나의 문서에서 ID는 고유값으로 한개만 존재합니다.따라서, 스크랩할 개체가 ID값을 가지고 있을 경우 아주 간단하게 스크랩할 수 있습니다.
  2. .getElementsByClassName
    : HTML문서에서 개체의 Class 값으로 개체를 찾습니다. 하나의 문서에서 동일한 Class 명을 가진 개체는 여러개 존재할 수 있으므로, For문을 통해 원하는 개체를 필터링하여 개체를 찾아 사용합니다.
  3. .getElementsByTagName
    : HTML 문서에서 개체의 Tag 값으로 개체를 찾습니다. 대부분의 경우 getElementByID와 getElementByClass 함수로 원하는 개체를 불러올 수 있으나, a 태그 (링크태그) 또는 head 태그 (머릿말태그) 등 특정 태그를 불러올 시 사용합니다.

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

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

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

입력된 기사 링크를 클릭하면 자동으로 해당 기사로 이동할 수 있도록 각 셀에 하이퍼링크를 연결합니다. 아래 명령문을 For Each 명령문 중간에서 'i를 더해주는 이전 단계'에 붙여넣기 합니다.

엑셀 크롤링 하이퍼링크 삽입

하이퍼링크를 추가하면 사용자가 보다 편리하게 원하는 기사로 이동할 수 있습니다.

8. 버튼에 매크로 삽입 및 안내 메세지 띄우기

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

엑셀 웹 크롤링 안내 메세지 띄우기

기사 스크랩이 완료되었다는 안내메세지를 띄워줍니다.

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

크롤링 기능 버튼에 매크로 삽입

버튼에 매크로를 입혀줍니다. 이후 버튼을 클릭하여 네이버 뉴스 스크랩을 할 수 있습니다.

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

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

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

별점주기 (5 / 4)

15
댓글 남기기

avatar
7 Comment Thereads
8 Thread replies
5 팔로워
 
가장 좋아요가 많은 댓글
가장 인기많은 댓글
9 작성자
천동빈박승용엑셀초보오빠두엑셀 백종진 최근 작성자
  현재 페이지 댓글알림 신청  
알림 설정
조성웅
손님
조성웅

이번 것은 완성 파일 안 올려주시나요?

오빠두엑셀
손님

안녕하세요? 오빠두엑셀입니다.
포스트를 하나씩 수정하고 있습니다. 조만간 완성파일도 정리해서 올려드리도록 하겠습니다.
이번주 안으로 올려드릴테니, 주말에 다시 한번 확인주시겠어요?^_^* 감사합니다.

조성웅
손님
조성웅

감사합니다~ 역시 최고!

오빠두엑셀
손님

안녕하세요? 오빠두엑셀입니다.
포스트를 하나씩 수정하고 있습니다. 조만간 완성파일도 정리해서 올려드리도록 하겠습니다.
이번주 안으로 올려드릴테니, 주말에 다시 한번 확인주시겠어요?^_^* 감사합니다.

졸려여
손님
졸려여

안녕하세요. 오빠두엑셀 참고해서 vba 공부하고 있습니다.
워낙 상세한 설명으로 인터넷 익스플로러를 통한 웹크롤링은 어느 정도 감을 잡았는데요.
크롬 웹브라우저를 이용해서 익스플로러와 동일한 크롤링을 해보고 싶은데 구글 찾아보아도 셀레니움을 이용하는 방식밖에 안나와서 문의드려요ㅎㅎ
1. Dim MyBrowser As InternetExplorer와 같이 As chrome 사용할 수 있는 방법이 없을까요
2. 1번의 internetexplorer일 때는 Set MyBrowser = Sheet1.WebBrowser1 에 에러가 없었으나 셀레니움 라이브러리 설치 후 chromedriver를 이용하면 vba에서 에러 발생합니다. 해결 방법이 있을까요?ㅠ

오빠두엑셀
손님

안녕하세요?:) 오빠두엑셀입니다.
크롬 웹브라우저를 셀레니움 없이도 이용을 할수는 있습니다. 정말 이용만요… 크롬에서 제공하는 라이브러리가 있지만 VB6에서는 지원이 안되는걸 알고있어요..

1. 방법이 없습니다.
2. 셀레니움 라이브러리를 활성화 하신 뒤
Dim MyBrowser As New WebDriver
MyBrowser.Start "chrome", "http://google.com"
로 입력해보시겠어요?^-^

오연택
손님
오연택

안녕하세요. 예제파일로 연습도 해보고 싶은데 예제파일은 다운받을 수 없을까요~?

백종진
손님
백종진
별점주기 :
     

너무 좋은강의 감사합니다
네이버 스마트스토어 자료 분석에 응용해보려 하는데요!
각 상품이 분류된 카테고리 명과 해당 검색키워드의 등록된 전체 상품수를 크롤링 하고싶은데 class로 도저히 값이 반환되지 않아서 문의드립니다
https://search.shopping.naver.com/search/all.nhn?where=all&frm=NVSCTAB&query=%EC%9E%90%EB%8F%99%EA%B8%89%EC%8B%9D%EA%B8%B0
해당 URL에서 "생활/건강>반려동물>식기/급수기>자동급식기" 카테고리를 추출하기위하여
getElementsByclass 명령문 값에depth 또는 cat_id_50006736 같은 값들을 입력해보고있는데 도저히 나오질 안네요 ㅠㅠ

검색어가 바뀌면 카테고리 값도 바뀌던데…class로 찾는것이 아닌가요??

엑셀초보
손님
엑셀초보
별점주기 :
     

와~ 너무 신기해요. ^^
부동산 관련 책을 읽다가 저자가 부동산 분석 프로그램을 배포하는 것을 보았어요.

업데이트는 직접 다운 받아서 프로그램 폴더에 붙여넣어야하는데, 그 분석 프로그램을 GUI 프로그램과 웹 크롤링으로 만든 것 같아서 실제로 비슷하게 만들어보면 프로그래밍 독학이 재밌을 것 같아 이것저것 설치를 했는데, 어렵더라구요. ㅜ.ㅜ

그러다 우연히 이 곳에 들어왔는데 엑셀로 웹크롤링이 된다는 것과 엑셀에 포함된 User form으로 비주얼베이직 같은 GUI 를 만들 수 있다는 것을 처음 알았어요.

엑셀 기초~ VBA 등 고급 강의를 무료로 제공해주시는 것도 감사한데, 알기 쉽게 설명해 주셔서 정말 감사합니다.

새해 건강하시고, 복 많이 많으시길 기원합니다. ^^

박승용
손님
박승용
별점주기 :
     

안녕하세요? 강좌 너무 잘 보고 있습니다. 많은 도움이 됩니다.
강좌 처음에 나오는 것처럼 이미지도 엑셀 시트에 넣고 싶은데 방법 알려주세요

천동빈
손님
천동빈
별점주기 :
     

열성적 강의 감사합니다.
저는 윈도우10에 EXCEL16을 상용하는데
시트에 웹브라우저 추가하기 를 위해
위의 설정대로 Compatibility Flags 값도 바꾸어 주었는데
자꾸 "개체를 삽입할 수 없습니다."와 수식 입력줄에 EMBED("Shell Explore.2", "")가
나타나 브라우저 삽입을 할 수 없습니다.
뭐가 잘못된 걸 까요?