페이지 정보

작성자 하나를하더라도최선을
댓글 0건 조회 6,369회 작성일 20-05-16 19:01

본문

아래와 같이 B열에 있는 상호를 검색하여 네이버 증권에서 현재가를 가져온다.

 

Sub program1472_com()

    Dim url As String, C As Range, t As String, Cookie As String, q As String, query As String

    For Each C In Range([b2], Cells(Rows.Count, "B").End(3))

        query = Replace(C, "&", "%26")

        url = "https://ac.finance.naver.com/ac?_callback=http://program1472.com&st=111&r_format=json&r_lt=111&q=" & query

        With CreateObject("WinHttp.WinHttpRequest.5.1")

            .Open "GET", url

            .SetRequestHeader "Host", "ac.finance.naver.com"

            .SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; WOW64; rv:56.0) Gecko/20100101 Firefox/56.0"

            .SetRequestHeader "Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8"

            .SetRequestHeader "Accept-Language", "ko-KR,ko;q=0.8,en-US;q=0.5,en;q=0.3"

            .SetRequestHeader "Connection", "keep-alive"

            If Len(Cookie) Then .SetRequestHeader "Cookie", Cookie

            .SetRequestHeader "Upgrade-Insecure-Requests", "1"

            .SetRequestHeader "TE", "Trailers"

            .Send

            .WaitForResponse: DoEvents

            t = .ResponseText

        End With

        q = Split(Split(Split(t, "[[[")(1), "[")(1), """")(1)

        q = UCase(q)

        If q = C Then

            q = Split(Split(t, "[[[")(1), """")(1)

            url = "https://finance.naver.com/item/main.nhn?code=" & q

            With CreateObject("WinHttp.WinHttpRequest.5.1")

                .Open "GET", url

                .SetRequestHeader "Host", "finance.naver.com"

                .SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; WOW64; rv:56.0) Gecko/20100101 Firefox/56.0"

                .SetRequestHeader "Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8"

                .SetRequestHeader "Accept-Language", "ko-KR,ko;q=0.8,en-US;q=0.5,en;q=0.3"

                .SetRequestHeader "Referer", "https://finance.naver.com/search/search.nhn?query=" & query

                .SetRequestHeader "Connection", "keep-alive"

                If Len(Cookie) Then .SetRequestHeader "Cookie", Cookie

                .SetRequestHeader "Upgrade-Insecure-Requests", "1"

                .SetRequestHeader "TE", "Trailers"

                .Send

                .WaitForResponse: DoEvents

                t = .ResponseText

            End With

            C.Offset(, Day(Now)).Value = Split(Split(Split(t, "<p class=""no_today"">")(1), "<span class=""blind"">")(1), "<")(0)

        End If

    Next

End Sub

 

첨부파일

• 2020-05-16dirId102020101docId356068430VBA 웹값가져오기.xlsm (18.6K)
  0회 다운로드 | DATE : 2020-05-16 19:01:58

댓글목록

등록된 댓글이 없습니다.