옥션 해외직구 상점(상품) 가져오기

 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
    Const css_footer = ".c_footer_menu_list"
   
    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.auction.co.kr/search?keyword=" & shopName & "&itemno=&nickname=&encKeyword=" & shopName & "&arraycategory=&frm=&dom=auction&isSuggestion=Yes&retry="
    Else
        driver.Get "https://browse.auction.co.kr/search?keyword=" & Cells(2, 3).Value & "&k=26&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
        'paging number to scroll
        driver.FindElementByCss(".footer").ExecuteScript "this.scrollIntoView(true);"
        While driver.ExecuteScript("return document.readyState") <> "complete"
            driver.Wait (5000)
        Wend
        'rCnt = driver.FindElementByCss(css_footer, timeout:=5000)
        'driver.WaitNotElement By.Css(css_footer), 5000
        '직구가게명 찾기
        Cells(1, 9).Value = driver.FindElementsByClass("section--itemcard_info").Count
        If Cells(1, 9).Value = 0 Then Exit Do
        For Each tEle In driver.FindElementsByClass("section--itemcard_info")
            Cells(nCnt, 1).Value = tEle.FindElementByClass("text--title", timeout:=200, Raise:=True).Text
            Cells(nCnt, 2).Value = tEle.FindElementByClass("link--itemcard", timeout:=0, Raise:=True).Attribute("href")
            If tEle.FindElementsByCss(".section--itemcard_info_shop .text").Count > 0 Then
                Cells(nCnt, 3).Value = tEle.FindElementByCss(".section--itemcard_info_shop .text").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(".area--itemcard_price .text--price_seller").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("box__tag").Count > 0 Then
                Cells(nCnt, 9).Value = Replace(tEle.FindElementByClass("box__tag").Text, "배송", "")
            Else
                Cells(nCnt, 9).Value = Replace(Replace(tEle.FindElementByClass("text--addinfo").Text, "배송비 ", ""), "원", "")
            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--next_page", timeout:=0, Raise:=False)
        If bt_next Is Nothing Or (page >= Cells(2, 6).Value) Or driver.FindElementsByCss(".link--next_page.off").Count > 0 Then _
            Exit Do
        
        page = page + 1
        bt_next.Click
    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("해외직구공구", "해외직구의류", "해외직구상품", "해외직구나이프", "해외직구남성의류", "해외직구신발", "노트북가방")
End Sub

Private Sub ComboBox1_Change()
    Dim sortKey As Variant
    sortKey = Array("해외직구공구", "해외직구의류", "해외직구상품", "해외직구나이프", "해외직구남성의류", "해외직구신발", "노트북가방")
    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.auction.co.kr/search?keyword=" & shopName & "&itemno=&nickname=&encKeyword=" & shopName & "&arraycategory=&frm=&dom=auction&isSuggestion=Yes&retry="
    Else
        driver.Get "https://browse.auction.co.kr/search?keyword=" & Cells(2, 3).Value & "&k=26&p=" & page
    End If
    driver.Window.SetSize 80, 1000
    nCnt = 4

    Do
        'paging number to scroll
        driver.FindElementByCss(".footer").ExecuteScript "this.scrollIntoView(true);"
        While driver.ExecuteScript("return document.readyState") <> "complete"
            driver.Wait (5000)
        Wend
        'paging number to scroll
        'driver.FindElementByCss(".searchList_notice__yuBNi").ExecuteScript "this.scrollIntoView(true);"
        '직구가게명 찾기
         Cells(2, 6).Value = driver.FindElementsByClass("section--itemcard_info").Count
        If Cells(2, 6).Value = 0 Then Exit Do
        For Each tEle In driver.FindElementsByClass("section--itemcard_info")
            If tEle.FindElementsByCss(".section--itemcard_info_shop .text").Count > 0 Then
                Cells(nCnt, 1).Value = tEle.FindElementByCss(".section--itemcard_info_shop .text").Text
                Cells(nCnt, 2).Value = tEle.FindElementByClass("link--shop").Attribute("href")
            ElseIf tEle.FindElementsByCss(".section--itemcard_info_shop .img").Count > 0 Then
                Cells(nCnt, 1).Value = tEle.FindElementByCss(".section--itemcard_info_shop .img").Text
                Cells(nCnt, 2).Value = tEle.FindElementByClass("link--shop").Attribute("href")
            Else
                Cells(nCnt, 1).Value = tEle.FindElementByCss(".img--smiledelivery").Attribute("alt")
                Cells(nCnt, 2).Value = tEle.FindElementByClass("link--smiledelivery").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
    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("해외직구공구", "해외직구의류", "해외직구상품", "해외직구나이프", "해외직구남성의류", "해외직구신발", "노트북가방")
End Sub

Private Sub ComboBox1_Change()
    Dim sortKey As Variant
    sortKey = Array("해외직구공구", "해외직구의류", "해외직구상품", "해외직구나이프", "해외직구남성의류", "해외직구신발", "노트북가방")
    Range("C2") = sortKey(ComboBox1.ListIndex)
End Sub