지마켓 해외직구 상점(상품) 가져오기

 1. 지마켓 해외직구 상품 가져오기

 

 

Private Sub CommandButton1_Click()
'On Error Resume Next
    Dim driver As New ChromeDriver
    Dim ele As WebElement
    Dim nCnt As Integer
    Dim page As Integer
    Dim tEle As Object
    Dim shopName As String
    Dim sortKey As Variant
   
    If Cells(2, 1).Value > 0 Then
        Range("A4:I" & (Cells(2, 1).Value + 4)).Clear
    End If
    page = Cells(2, 5).Value
    shopName = Cells(2, 2).Value
    If Len(shopName) > 0 Then
        driver.Get "https://browse.gmarket.co.kr/search?keyword=" & shopName & "&f=is:cb&k=0&p=" & page
    Else
        driver.Get "https://browse.gmarket.co.kr/search?keyword=해외직구&f=is:cb,c:" & Cells(2, 3).Value & "&k=0&p=" & page
    End If
    driver.Window.SetSize 80, 1000
    nCnt = 4
    'driver.Wait (500)
    'shopName = sortKey(ComboBox1.ListIndex)
    'Set bt_next = driver.FindElementById(shopName, timeout:=0, Raise:=False)
    'If Not bt_next Is Nothing Then bt_next.Click

    Do
        While driver.ExecuteScript("return document.readyState") <> "complete"
            driver.Wait (5000)
        Wend
        'paging number to scroll
        driver.FindElementByCss("#footer").ExecuteScript "this.scrollIntoView(true);"
        
        '직구가게명 찾기
        Cells(1, 9).Value = driver.FindElementsByClass("box__item-container").Count
        If Cells(1, 9).Value = 0 Then Exit Do
        For Each tEle In driver.FindElementsByClass("box__item-container")
            Cells(nCnt, 1).Value = tEle.FindElementByClass("text__item", timeout:=200, Raise:=True).Text
            Cells(nCnt, 2).Value = tEle.FindElementByClass("link__item", timeout:=0, Raise:=True).Attribute("href")
            If tEle.FindElementsByCss(".link__shop").Count > 0 Then
                Cells(nCnt, 3).Value = tEle.FindElementByCss(".text__seller").Text
                Cells(nCnt, 4).Value = tEle.FindElementByClass("link__shop").Attribute("href")
            'ElseIf tEle.FindElementsByCss(".section--itemcard_info_shop .img").Count > 0 Then
            '    Cells(nCnt, 3).Value = tEle.FindElementByCss(".section--itemcard_info_shop .img").Text
            '    Cells(nCnt, 4).Value = tEle.FindElementByClass("link--shop").Attribute("href")
            'Else
            '    Cells(nCnt, 3).Value = tEle.FindElementByCss(".img--smiledelivery").Attribute("alt")
            '    Cells(nCnt, 4).Value = tEle.FindElementByClass("link--smiledelivery").Attribute("href")
            End If
            '가격
            Cells(nCnt, 5).Value = tEle.FindElementByCss(".box__price-seller .text__value").Text
            
            'If tEle.FindElementsByClass("text--reviewcnt").Count > 0 Then Cells(nCnt, 6).Value = Replace(tEle.FindElementByClass("text--reviewcnt").Text, "후기 ", "")
            'If tEle.FindElementsByClass("text--buycnt").Count > 0 Then Cells(nCnt, 7).Value = Replace(tEle.FindElementByClass("text--buycnt").Text, "구매 ", "")
            If tEle.FindElementsByClass("text__tag").Count > 0 Then
                If Len(tEle.FindElementByClass("text__tag").Text) = 4 Then
                    Cells(nCnt, 9).Value = Replace(tEle.FindElementByClass("text__tag").Text, "배송", "")
                Else
                    Cells(nCnt, 9).Value = Replace(Replace(tEle.FindElementByClass("text__tag").Text, "배송비 ", ""), "원", "")
                End If
            End If
            ActiveSheet.Hyperlinks.Add Range("B" & nCnt), Address:=Range("B" & nCnt)
            ActiveSheet.Hyperlinks.Add Range("D" & nCnt), Address:=Range("D" & nCnt)
            
            nCnt = nCnt + 1
        Next tEle

        Set bt_next = driver.FindElementByClass("link__page-next", timeout:=0, Raise:=False)
        If bt_next Is Nothing Or (page >= Cells(2, 6).Value) Or driver.FindElementsByCss(".link__page-next.link--off").Count > 0 Then _
            Exit Do
        
        page = page + 1
        
        'bt_next.Click
        shopName = Cells(2, 2).Value
        If Len(shopName) > 0 Then
            driver.Get "https://browse.gmarket.co.kr/search?keyword=" & shopName & "&f=is:cb&k=0&p=" & page
        Else
            driver.Get "https://browse.gmarket.co.kr/search?keyword=해외직구&f=is:cb,c:" & Cells(2, 3).Value & "&k=0&p=" & page
        End If
    Loop
    driver.Close
    Set driver = Nothing
    '//1번째 열 기준으로 중복제거
    'Range("A4:I" & Cells(Rows.Count, 1).End(xlUp).row).RemoveDuplicates Columns:=1, Header:=xlNo
    Range("A2") = Cells(Rows.Count, 1).End(xlUp).row - 3
End Sub

Private Sub Worksheet_Activate()
    ComboBox1.List = Array("영상가전", "장난감/완구", "화장품/향수", "생활/미용가전", "여성의류", "쥬얼리/시계", "PC주변기기", "커피/음료", "가방/잡화", "건강식품", "신발", "자동차용품", "남성의류", "수입 명품", "스포츠의류/운동화", "주방용품", "바디/헤어", "브랜드 남성의류", "주방가전", "공구/안전/산업용품", "생활용품", "캠핑/낚시", "브랜드 여성의류", "브랜드 신발/가방", "음향기기", "휘트니스/수영", "조명/인테리어", "악기/취미", "계절가전", "반려동물용품", "게임", "가구/DIY", "꽃/이벤트용품", "유아동의류", "가공식품", "모바일/태블릿", "문구/사무용품", "건강/의류용품", "신선식품", "침구/커튼", "카메라", "자전가/보드/레져", "도서음반/e교육", "모니터/프린터", "유아동신발/잡화", "브랜드진/케쥬얼", "언더웨어", "골프", "출산/육아", "저장장치", "등산/아웃도어", "생필품", "구기/라켓", "노트북/PC", "여행/항공권", "브랜드 쥬얼리/시계")
End Sub

Private Sub ComboBox1_Change()
    Dim sortKey As Variant
    sortKey = Array("100000032", "100000042", "100000005", "100000092", "100000003", "100000027", "100000055", "100000094", "100000064", "100000068", "100000049", "100000030", "100000046", "100000096", "100000043", "100000085", "100000071", "100000104", "100000008", "100000076", "100000014", "100000017", "100000103", "100000106", "100000102", "100000037", "100000093", "100000091", "100000077", "100000038", "100000111", "100000031", "100000041", "100000035", "100000036", "100000056", "100000045", "100000083", "100000020", "100000039", "100000033", "100000097", "100000028", "100000082", "100000095", "100000105", "100000070", "100000058", "100000006", "100000075", "100000099", "100000074", "100000098", "100000002", "100000013", "100000107")
    Range("C2") = sortKey(ComboBox1.ListIndex)
End Sub

 

 

2. 지마켓 해외직구 상점 가져오기

 

Private Sub CommandButton1_Click()
On Error Resume Next
    Dim driver As New ChromeDriver
    Dim i As Integer
    Dim nCnt As Integer
    Dim page As Integer
    Dim tEle As Object
    Dim shopName As String
    
    If Cells(2, 1).Value > 0 Then
        Range("A4:I" & (Cells(2, 1).Value + 4)).Clear
    End If
    page = Cells(2, 4).Value
    shopName = Cells(2, 2).Value
    If Len(shopName) > 0 Then
        driver.Get "https://browse.gmarket.co.kr/search?keyword=" & shopName & "&f=is:cb&k=0&p=" & page
    Else
        driver.Get "https://browse.gmarket.co.kr/search?keyword=해외직구&f=is:cb,c:" & Cells(2, 3).Value & "&k=0&p=" & page
    End If
    driver.Window.SetSize 80, 1000
    nCnt = 4

    Do
        While driver.ExecuteScript("return document.readyState") <> "complete"
            driver.Wait (5000)
        Wend
        'paging number to scroll
        driver.FindElementByCss("#footer").ExecuteScript "this.scrollIntoView(true);"
        '직구가게명 찾기
        Cells(1, 6).Value = driver.FindElementsByClass("box__item-container").Count
        If Cells(1, 6).Value = 0 Then Exit Do
        For Each tEle In driver.FindElementsByClass("box__item-container")
            If tEle.FindElementsByCss(".link__shop").Count > 0 Then
                Cells(nCnt, 1).Value = tEle.FindElementByCss(".text__seller").Text
                Cells(nCnt, 2).Value = tEle.FindElementByClass("link__shop").Attribute("href")
            End If
            Cells(nCnt, 3).Value = " "
            ActiveSheet.Hyperlinks.Add Range("B" & nCnt), Address:=Range("B" & nCnt).Value
            nCnt = nCnt + 1
        Next tEle

        Set bt_next = driver.FindElementByClass("link--next_page", timeout:=0, Raise:=False)
        If bt_next Is Nothing Or (page >= Cells(2, 5).Value) Or driver.FindElementsByCss(".link--next_page.off").Count > 0 Then _
            Exit Do

        page = page + 1
        'bt_next.Click
        shopName = Cells(2, 2).Value
        If Len(shopName) > 0 Then
            driver.Get "https://browse.gmarket.co.kr/search?keyword=" & shopName & "&f=is:cb&k=0&p=" & page
        Else
            driver.Get "https://browse.gmarket.co.kr/search?keyword=해외직구&f=is:cb,c:" & Cells(2, 3).Value & "&k=0&p=" & page
        End If
    Loop
    driver.Close
    Set driver = Nothing
    '//1번째 열 기준으로 중복제거
    Range("A4:B" & Cells(Rows.Count, 1).End(xlUp).row).RemoveDuplicates Columns:=1, Header:=xlNo
    Range("A2") = Cells(Rows.Count, 1).End(xlUp).row - 3
End Sub

Private Sub Worksheet_Activate()
    ComboBox1.List = Array("영상가전", "장난감/완구", "화장품/향수", "생활/미용가전", "여성의류", "쥬얼리/시계", "PC주변기기", "커피/음료", "가방/잡화", "건강식품", "신발", "자동차용품", "남성의류", "수입 명품", "스포츠의류/운동화", "주방용품", "바디/헤어", "브랜드 남성의류", "주방가전", "공구/안전/산업용품", "생활용품", "캠핑/낚시", "브랜드 여성의류", "브랜드 신발/가방", "음향기기", "휘트니스/수영", "조명/인테리어", "악기/취미", "계절가전", "반려동물용품", "게임", "가구/DIY", "꽃/이벤트용품", "유아동의류", "가공식품", "모바일/태블릿", "문구/사무용품", "건강/의류용품", "신선식품", "침구/커튼", "카메라", "자전가/보드/레져", "도서음반/e교육", "모니터/프린터", "유아동신발/잡화", "브랜드진/케쥬얼", "언더웨어", "골프", "출산/육아", "저장장치", "등산/아웃도어", "생필품", "구기/라켓", "노트북/PC", "여행/항공권", "브랜드 쥬얼리/시계")
End Sub

Private Sub ComboBox1_Change()
    Dim sortKey As Variant
    sortKey = Array("100000032", "100000042", "100000005", "100000092", "100000003", "100000027", "100000055", "100000094", "100000064", "100000068", "100000049", "100000030", "100000046", "100000096", "100000043", "100000085", "100000071", "100000104", "100000008", "100000076", "100000014", "100000017", "100000103", "100000106", "100000102", "100000037", "100000093", "100000091", "100000077", "100000038", "100000111", "100000031", "100000041", "100000035", "100000036", "100000056", "100000045", "100000083", "100000020", "100000039", "100000033", "100000097", "100000028", "100000082", "100000095", "100000105", "100000070", "100000058", "100000006", "100000075", "100000099", "100000074", "100000098", "100000002", "100000013", "100000107")
    Range("C2") = sortKey(ComboBox1.ListIndex)
End Sub