Option Explicit

Sub 최저가현황()

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

Application.DisplayStatusBar = False

ActiveSheet.DisplayPageBreaks = False


    Dim Naver As String, Aurl As String, Gurl As String, Iurl As String, STurl As String
    Naver = "https://search.shopping.naver.com/detail/price_compare_area.nhn?" & "nvMid=" & ActiveCell.Offset(-2, 32).Value & "&pkey=" & ActiveCell.Offset(-2, 33).Value
    Aurl = "http://itempage3.auction.co.kr/DetailView.aspx?itemno="
    Gurl = "http://item.gmarket.co.kr/Item?goodscode="
    Iurl = "http://shopping.interpark.com/product/productInfo.do?viewTp=preview&prdNo="
    STurl = "http://www.11st.co.kr/product/SellerProductDetail.tmall?method=getSellerProductDetail&prdNo="

    On Error Resume Next

    Dim i As Integer, ed As Integer

    Dim ABLEcode(3) As String

    ABLEcode(0) = ActiveCell.Offset(-2, -1).Value
    ABLEcode(1) = ActiveCell.Offset(-2, 4).Value
    ABLEcode(2) = ActiveCell.Offset(-1, 4).Value
    ABLEcode(3) = ActiveCell.Offset(0, 4).Value

    Dim htmlA(2) As String, htmlG(2) As String, htmlI(2) As String, html11(2) As String, beginA(2) As Double, beginG(2) As Double, endA(2) As Double, endG(2) As Double, beginI(2) As Double, endI(2) As Double, begin11(3) As Double, end11(3) As Double
    Dim result(20) As String
    Dim temp As Object, temp2 As Object, Code(20) As String, Title(20) As String, Price(20) As String, Mall(20) As String, PPrice(20) As String, Seller(20) As String, sale(20) As String, Link(20) As String, linktemp As String
    Erase Link

    Dim winhttp As New WinHttpRequest
    Dim HTML1 As MSHTML.HTMLDocument
    Dim elemCol As MSHTML.IHTMLElement
    Set HTML1 = New MSHTML.HTMLDocument

    With winhttp
        .Open "POST", Naver
        .SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:67.0) Gecko/20100101 Firefox/67.0"
        .SetRequestHeader "Referer", "https://search.shopping.naver.com/detail/detail.nhn?"
        .Send
        HTML1.body.innerHTML = .ResponseText
    End With

    ed = 20

    For i = 1 To ed
        Set elemCol = HTML1.getElementsByClassName("tbl tbl_v")(i)
        Set temp = elemCol.tBodies(0)
        Code(i) = temp.Children(0).getAttribute("data-mall-pid")

        Link(i) = temp.getElementsByTagName("A")

        Set temp2 = temp.getElementsByTagName("img")
        Mall(i) = temp2.Item(0).alt


        Set temp = HTML1.getElementsByClassName("lft")
        Set temp2 = temp(i - 1).getElementsByTagName("a")
        Title(i) = temp2(0).innerText

        Set temp = HTML1.getElementsByClassName("td_price")
        Price(i) = Split(temp(i - 1).innerText, "원")(0)
        If InStr(1, Price(i), "최저") > 0 Then
            Price(i) = Split(Price(i), "최저")(1)
        End If

        If Price(i) = "" Then
            Title(i) = ""
            Seller(i) = ""
            sale(i) = ""
            PPrice(i) = ""
            Link(i) = ""
            ed = i - 1
            Exit For
        End If
    Next i

    For i = 1 To ed

        Select Case Mall(i)

            Case "옥션"

            htmlA(1) = getHTML("http://itempage3.auction.co.kr/DetailView.aspx?itemNo=" & Code(i))

            beginA(1) = InStr(1, htmlA(1), "price_original")                 '정산 파트

            If beginA(1) = 0 Then
                beginA(1) = InStr(1, htmlA(1), "price_real")
            End If

            If beginA(1) = 0 Then
                    result(i) = "0"
            End If

            endA(1) = InStr(beginA(1), htmlA(1), "<")

            If beginA(1) = InStr(1, htmlA(1), "price_original") Then
                result(i) = Mid(htmlA(1), beginA(1) + 16, endA(1) - beginA(1) - 16)
                sale(i) = (1 - (Price(i) / result(i))) * 100
                PPrice(i) = Application.WorksheetFunction.RoundDown(result(i) * (1 - ((9 + (sale(i) * 0.1)) / 100)), -1)

            ElseIf beginA(1) = InStr(1, htmlA(1), "price_real") Then
                result(i) = Mid(htmlA(1), beginA(1) + 12, endA(1) - beginA(1) - 12)
                sale(i) = (1 - (Price(i) / result(i))) * 100
                PPrice(i) = Application.WorksheetFunction.RoundDown(result(i) * (1 - ((9 + (sale(i) * 0.1)) / 100)), -1)
            End If

            beginA(2) = InStr(1, htmlA(1), "shop-title")                       '판매처명 파트
            endA(2) = InStr(beginA(2), htmlA(1), "</")
            Seller(i) = Mid(htmlA(1), beginA(2) + 12, endA(2) - beginA(2) - 12)

        Case "G마켓"

            htmlG(1) = getHTML("http://item.gmarket.co.kr/Item?goodscode=" & Code(i))

            beginG(1) = InStr(1, htmlG(1), "price_original")

            If beginG(1) = 0 Then
                beginG(1) = InStr(1, htmlG(1), "price_real")
            End If

            If beginG(1) = 0 Then
                result(i) = "0"
            End If

            endG(1) = InStr(beginG(1), htmlG(1), "<")

            If beginG(1) = InStr(1, htmlG(1), "price_original") Then
                result(i) = Mid(htmlG(1), beginG(1) + 16, endG(1) - beginG(1) - 16)
                sale(i) = (1 - (Price(i) / result(i))) * 100
                PPrice(i) = Application.WorksheetFunction.RoundDown(result(i) * (1 - ((9 + (sale(i) * 0.1)) / 100)), -1)

            ElseIf beginG(1) = InStr(1, htmlG(1), "price_real") Then
                result(i) = Mid(htmlG(1), beginG(1) + 12, endG(1) - beginG(1) - 12)
                sale(i) = (1 - (Price(i) / result(i))) * 100
                PPrice(i) = Application.WorksheetFunction.RoundDown(result(i) * (1 - ((9 + (sale(i) * 0.1)) / 100)), -1)
            End If

            beginG(2) = InStr(1, htmlG(1), "shoptit")                       '판매처명 파트
            endG(2) = InStr(beginG(2) + 1, htmlG(1), "/strong>")
            htmlG(2) = Mid(htmlG(1), beginG(2) + 1, endG(2) - beginG(2) + 1)
            beginG(2) = InStr(1, htmlG(2), "<strong>")
            endG(2) = InStr(beginG(2) + 1, htmlG(2), "<")
            Seller(i) = Mid(htmlG(2), beginG(2) + 8, endG(2) - beginG(2) - 8)

        Case "인터파크"

            htmlI(1) = getHTML("http://shopping.interpark.com/product/productInfo.do?viewTp=preview&prdNo=" & Code(i))

            beginI(1) = InStr(1, htmlI(1), "sale_price")

            If beginI(1) = 0 Then
                result(i) = "0"
            End If

            endI(1) = InStr(beginI(1), htmlI(1), ",")

            result(i) = Mid(htmlI(1), beginI(1) + 14, endI(1) - beginI(1) - 15)
            sale(i) = (1 - (Price(i) / result(i))) * 100

            PPrice(i) = Application.WorksheetFunction.RoundDown(result(i) * (1 - ((11 + (sale(i) * 0.1)) / 100)), -1)

            beginI(2) = InStr(1, htmlI(1), "sellerNm")                      '판매처명 파트
            endI(2) = InStr(beginI(2), htmlI(1), ",")
            Seller(i) = Mid(htmlI(1), beginI(2) + 11, endI(2) - beginI(2) - 12)

        Case "11번가"

            html11(1) = getHTML("http://www.11st.co.kr/product/SellerProductDetail.tmall?method=getSellerProductDetail&prdNo=" & Code(i))

            begin11(1) = InStr(1, html11(1), "정상가")
            end11(1) = InStr(begin11(1), html11(1), "원")

            If begin11(1) = 0 Then
                result(i) = "0"

            End If

            html11(2) = Mid(html11(1), begin11(1), end11(1) - begin11(1))

            If InStr(1, html11(2), "strong") > 0 Then
                begin11(1) = InStr(1, html11(2), "sale_price")
                end11(1) = InStr(begin11(1), html11(2), "</strong>")
                result(i) = Mid(html11(2), begin11(1) + 12, end11(1) - begin11(1) - 12)
                sale(i) = (1 - (Price(i) / result(i))) * 100
                PPrice(i) = Application.WorksheetFunction.RoundDown(result(i) * (1 - ((9 + (sale(i) * 0.1)) / 100)), -1)

            ElseIf InStr(1, html11(2), "normal_price") > 0 Then
                begin11(1) = InStr(1, html11(2), "normal_price")
                end11(1) = InStr(begin11(1), html11(2), "</s>")
                result(i) = Mid(html11(2), begin11(1) + 17, end11(1) - begin11(1) - 17)
                sale(i) = (1 - (Price(i) / result(i))) * 100
                PPrice(i) = Application.WorksheetFunction.RoundDown(result(i) * (1 - ((9 + (sale(i) * 0.1)) / 100)), -1)

            End If

            begin11(2) = InStr(1, html11(1), "seller_nickname")                      '판매처명 파트
            end11(2) = InStr(begin11(2), html11(1), "</a>")
            html11(0) = Mid(html11(1), begin11(2), end11(2) - begin11(2))
            begin11(3) = InStr(1, html11(0), ">")
            Seller(i) = Mid(html11(0), begin11(3) + 1)

        Case Else

            result(i) = "X"
            Seller(i) = "X"
            sale(i) = "X"
            PPrice(i) = "정산식 필요"

        End Select

        Next i

    For i = 1 To 20

        With Worksheets("네이버 현황")
            .Cells(1, 1).Value = ABLEcode(0)

            If Mall(i) = "" And Seller(i) = "X" Then
            .Cells(i + 2, 2).Value = "X"

            Else
            .Cells(i + 2, 2).Value = Mall(i)

            End If

            .Cells(i + 2, 3).Value = Title(i)

            If Seller(i) = "X" Then
                .Cells(i + 2, 4).Value = "X"

            Else
                .Cells(i + 2, 4).Value = Seller(i)

            End If

            .Cells(i + 2, 5).Value = Price(i)
            .Cells(i + 2, 6).Value = sale(i)
            .Cells(i + 2, 7).Value = PPrice(i)
            .Cells(i + 2, 8).Value = ""
                Link(i) = Split(Link(i), "http://cr2.shopping.naver.com/adcrNoti.nhn?x=")(1)
                Link(i) = Split(Link(i), "&nv_mid")(0)
            .Hyperlinks.Add Anchor:=.Cells(i + 2, 8), Address:="http://cr2.shopping.naver.com/adcrNoti.nhn?x=" & _
                                                                    Link(i), TextToDisplay:="☞"
            .Cells(i + 2, 9).Value = result(i)

            .Range("H" & i + 2).HorizontalAlignment = xlCenter
            .Range("H" & i + 2).Interior.COLOR = RGB(255, 255, 204)

        End With

        Next i

        With Worksheets("네이버 현황")
            .Cells(28, 7) = ABLEcode(1)
            .Cells(29, 7) = ABLEcode(2)
            .Cells(30, 7) = ABLEcode(3)
        End With


        Sheets("네이버 현황").Select


Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

Application.EnableEvents = True

Application.DisplayStatusBar = True

ActiveSheet.DisplayPageBreaks = True



End Sub