11번가 해외직구 상점(상품) 가져오기

 1. 11번가 해외직구 상품 가져오기

 

 

 

Private Sub CommandButton1_Click()
    Dim driver As New ChromeDriver
    Dim ele As WebElement
    Dim i As Integer, j As Integer
    Dim nCnt As Integer, rCnt As Integer
    Dim page As Integer
    Dim tEle As Object
    Dim shopName As String
    Dim sortKey As Variant
    sortKey = Array("sortCdN", "sortCdSPS")

    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://www.11st.co.kr/browsing/MainThemeAction.tmall?method=globalDirectList&localShop=N&countryCode=CN&dispCtgrNo=8286" & Cells(2, 3).Value & "'$$pageNo%%" & page
    Else
        driver.Get "https://www.11st.co.kr/browsing/MainThemeAction.tmall?method=globalDirectList&localShop=N&countryCode=CN&dispCtgrNo=8286" & Cells(2, 3).Value & "'$$pageNo%%" & 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
    
    'paging number to scroll
    'driver.FindElementByCss(ss_footer).ExecuteScript "this.scrollIntoView(true);"
    Do
        While driver.ExecuteScript("return document.readyState") <> "complete"
            driver.Wait (5000)
        Wend
        shopName = sortKey(ComboBox1.ListIndex)
        Set bt_next = driver.FindElementByClass(shopName, timeout:=0, Raise:=False)
        bt_next.Click
        
        'paging number to scroll
        driver.FindElementByCss(".c_footer_menu_list").ExecuteScript "this.scrollIntoView(true);"

        '직구가게명 찾기
        Cells(1, 9).Value = driver.FindElementsByClass("product_type1").Count
        If Cells(1, 9).Value = 0 Then Exit Do
        For Each tEle In driver.FindElementsByClass("product_type1")
            Cells(nCnt, 1).Value = tEle.FindElementByClass("subject", timeout:=200, Raise:=True).Text
            Cells(nCnt, 2).Value = tEle.FindElementByTag("a", timeout:=0, Raise:=True).Attribute("href")
            Cells(nCnt, 3).Value = tEle.FindElementByClass("company").Text
            Cells(nCnt, 4).Value = tEle.FindElementByCss(".category_box .shop", timeout:=0, Raise:=True).Attribute("href")
            Cells(nCnt, 5).Value = tEle.FindElementsByClass("n")(1).Text
            If tEle.FindElementsByClass("n").Count > 1 Then
                Cells(nCnt, 9).Value = tEle.FindElementsByClass("n")(2).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.FindElementByCss(".btn_more_box", timeout:=0, Raise:=False)
        If bt_next Is Nothing Or (page >= Cells(2, 6).Value) 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("#sortCd%%N", "#sortCd%%SPS")
    Range("C2") = sortKey(ComboBox1.ListIndex)
End Sub

 

 

 

2. 11번가 해외직구 상점 가져오기

 

 

Private Sub ComboBox1_Change()
    Dim sortKey As Variant
    sortKey = Array("#sortCdN", "#sortCdSPS")
    Range("E1") = sortKey(ComboBox1.ListIndex)
End Sub

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:C" & (Cells(2, 1).Value + 3)).Clear
    End If
    
    page = Cells(2, 3).Value
    driver.Get "https://www.11st.co.kr/browsing/MainThemeAction.tmall?method=globalDirectList&localShop=N&countryCode=CN&dispCtgrNo=8286"
    driver.Wait (500)
    Set bt_next = driver.FindElementById(Range("E1"), timeout:=0, Raise:=False)
    If Not bt_next Is Nothing Then bt_next.Click
    driver.Window.SetSize 80, 900
    nCnt = 4

    Do
        'paging number to scroll
        'driver.FindElementByCss(".searchList_notice__yuBNi").ExecuteScript "this.scrollIntoView(true);"
        driver.Wait (500)
        '직구가게명 찾기
        For Each tEle In driver.FindElementsByClass("product_type1")
            Cells(nCnt, 1).Value = tEle.FindElementByClass("company").Text
            Cells(nCnt, 2).Value = tEle.FindElementByClass("shop").Attribute("href")
            Cells(nCnt, 3).Value = " "
            ActiveSheet.Hyperlinks.Add Range("B" & nCnt), Address:=Range("B" & nCnt).Value
            nCnt = nCnt + 1
        Next tEle

        Set bt_next = driver.FindElementByCss("btn_more_box", timeout:=0, Raise:=False)
        If bt_next Is Nothing Or (page >= Cells(2, 4).Value) 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