Ebay vba url issue

[Sharid]

I have a vba code that extracts ebay product details the code works fine if i am using a direct url in the code

Original Code for search, but only searches 1 item

 .Navigate2 "https://www.ebay.co.uk/sch/i.html?_from=R40&_trksid=m570.l1313&_nkw=jackets&_sacat=0"

This variation of the url takes the seach item from Sheet1 A1 and places it into ebay

 .Navigate2 "https://www.ebay.co.uk/= & Replace(Worksheets("Sheet1").Range("A1").Value, " ", "+")

With the above URL I can get my search item to pop up in ebay search, however nothing is extracted. As you can see this is missing as it keeps giving me an error. &_sacat=0"

I can not work out what the issue is as only the url layout changes. The url is for the ebay search page and NOT for the home page of https://www.ebay.co.uk/ it might work better if the search item from Sheet1 A1 is placed in here and then the search page pulls up the searched products.

I tried this, but it keeps giving me an "Expected : end of statement error" or a "Syntax error"

.Navigate2 "https://www.ebay.co.uk/_nkw = & Replace(Worksheets([B]"Sheet1"[/B]).Range("A1").Value, " ", "+")

Thanks for having a look

 

[Joe4]

I think you need to separate the string part from the calculation part and sew together with & (you did not close out the first part of the string with a closing double-quote, something like

.Navigate2 [COLOR=#ff0000]"https://www.ebay.co.uk/_nkw="[/COLOR] & [COLOR=#0000ff]Replace(Worksheets([B]"Sheet1"[/B]).Range("A1").Value, " ", "+")[/COLOR]

Note that it is often helpful to create a string to store the value, so you can easily check/validate that it is returning what you want in a message box, i.e.

Dim  myUrl as String
myUrl = [COLOR=#333333]"https://www.ebay.co.uk/_nkw=" & Replace(Worksheets([/COLOR][B]"Sheet1"[/B][COLOR=#333333]).Range("A1").Value, " ", "+")
MsgBox myUrl  'can comment out/delete once working
.Navigate2 myUrl[/COLOR]

 

[Sharid]

Joe4

Thanks for the reply.

Your code came up with page not found on ebay, I had to add the bit in blue to get it to pull up the page. However it is the same problem and is not extracting ANY data. I ahve therefore listed the full code below. Some items have been commeted out as they have not been tested yet.

I am limited in vba, but does this have something to do with it, _sacat=0

Option Explicit

    Public Sub GetDataEbay()
        Dim htmlDoc As MSHTML.HTMLDocument, ie As SHDocVw.InternetExplorer, ws As Worksheet

        Set ie = New SHDocVw.InternetExplorer
        Set htmlDoc = New MSHTML.HTMLDocument
        Set ws = ThisWorkbook.Worksheets("Sheet1")

       Dim nextPageElement As Object
        Dim pageNumber As Long
          
        With ie
            .Visible = True
            
[COLOR=#ff0000][B]' Joe4 code -  I had to add the bit in [/B][/COLOR][COLOR=#0000cd][B]blue[/B][/COLOR][COLOR=#ff0000][B] to get it to work[/B][/COLOR]
         
Dim myUrl As String
myUrl = "https://www.ebay.co.uk/[COLOR=#0000ff]sch/i.html?[/COLOR]_nkw=" & Replace(Worksheets("Sheet1").Range("A1").Value, " ", "+")
'MsgBox myUrl  'can comment out/delete once working
.Navigate2 myUrl
                   
            
      [COLOR=#008000][B]'Original Code for search, but only searches 1 item[/B][/COLOR]
    [COLOR=#008000]  '.Navigate2 "https://www.ebay.co.uk/sch/i.html?_from=R40&_trksid=m570.l1313&_nkw=jackets&_sacat=0"[/COLOR]
            
[B][COLOR=#008000]'Not Working   [/COLOR][/B]
        [COLOR=#008000] '.Navigate2 "https://www.ebay.co.uk/sch/& Replace(Worksheets("Sheet1").Range("A1").Value, " ", "+")

    [B]  ' Takes seach from A1 and places it into ebay, but NO data extracted[/B][/COLOR]
    [COLOR=#008000]  '.Navigate2 "https://www.ebay.co.uk/sch/i.html?_from=R40&_trksid=m570.l1313&_nkw=" & Replace(Worksheets("Sheet2").Range("A1").Value, " ", "+")[/COLOR]
    
             
            While .Busy Or .readyState <> 4: DoEvents: Wend

            Dim index As Long, HTMLItems As Object, rowNum As Long, xCell As Range
            Dim cssSelectors(), i As Long
[COLOR=#008000]   ' Do[/COLOR]
            Select Case True
            Case InStr(.document.URL, "ebay.co.uk") > 0
                cssSelectors = Array(".gvtitle a", ".amt", ".gvtitle a")
            Case InStr(.document.URL, "ebay.com") > 0
                cssSelectors = Array(".s-item__title", ".s-item__price", ".s-item__link")
               
            End Select

[COLOR=#008000][B]'Search next page up to 5 pages[/B][/COLOR]
   [COLOR=#008000] ' If pageNumber >= 5 Then Exit Do 'the first 5 pages
        '    Set nextPageElement = htmlDoc.getElementById("pnnext") 'pnnext
        '    If nextPageElement Is Nothing Then Exit Do
            
            ' Clicks web next page
         '   nextPageElement.Click 'next web page
         '   Do While ie.Busy Or ie.readyState <> 4
         '       DoEvents
         '   Loop
         '   Application.Wait Now + TimeSerial(0, 0, 5)
         '   Set htmlDoc = ie.document
         '   pageNumber = pageNumber + 1
      '  Loop[/COLOR]

            With ws
                For i = LBound(cssSelectors) To UBound(cssSelectors)
                    rowNum = 1
                    Set HTMLItems = ie.document.querySelectorAll(cssSelectors(i))

                    For index = 0 To HTMLItems.Length - 1
                        .Cells(rowNum, i + 1).Value = IIf(i = 2, HTMLItems.Item(index).getAttribute("href"), HTMLItems.Item(index).innerText)
                        rowNum = rowNum + 1
                    Next
                Next
                For Each xCell In .Range("C1:C25000") '<= all these really?
                    .Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
                Next xCell
            End With
           [COLOR=#008000] '.Quit[/COLOR]
        End With
    End Sub

 

[Joe4]

Quite frankly, I never have tried to navigate to internet sites via Excel, but what I am trying to show you a technique that you can use to (hopefully) figure it out.
If you know the URL of the site you need to get to, and you are trying to build it in VBA, you can use a MsgBox to verify that you have correctly built your URL string properly.
Since build it, use that MsgBox, and then compare what the message box returns to what you need the URL to be. Do they match exactly? If not, adjust your code so that they do.
I use this technique a lot when I am using VBA to build SQL code (where I know that my final code needs to look like in order to work). It has served me quite well.

If you do that, and they match, and it still does not work, I suspect that something is being converted along the way when that URL address gets submitted. I really don't know anything about that. I would recommend some Groogle searches to see what you can find on that.

 

[Sharid]

I got the code sorted to accept the key search word from the sheet, the problem was this bit of code

cssSelectors = Array(".gvtitle a", ".amt", ".gvtitle a")

It work fine with 1 url in the code, hence I could not work it out.

I changed it to this - Now it takes the search from the keyword on the sheet. I have changed the sheet keyword to Sheet2 A1

cssSelectors = Array(".lvtitle a", ".lvprice", ".lvtitle a")

It pulls off

Column A = Product title
Column B = Product Price
Column c = Product url

It is still a bit buggy. As if there is an AUCTION item with a BUY NOW price, it pulls of both prices and places the BUY NOW price in the next row, this means that the next items price will be wrong as it has the BUY NOW price of the previous item.

The full code

    Option Explicit

    Public Sub GetDataEbay()
        Dim htmlDoc As MSHTML.HTMLDocument, ie As SHDocVw.InternetExplorer, ws As Worksheet

        Set ie = New SHDocVw.InternetExplorer
        Set htmlDoc = New MSHTML.HTMLDocument
        Set ws = ThisWorkbook.Worksheets("Sheet1")

       Dim nextPageElement As Object
        Dim pageNumber As Long
          
    [COLOR=#008000]    ' IE GET READY AND VISIBLE[/COLOR]
        With ie
            .Visible = True
               .Navigate2 "https://www.ebay.co.uk/sch/i.html?_from=R40&_trksid=m570.l1313&_nkw=" & Replace(Worksheets("Sheet2").Range("A1").Value, " ", "+")
                 
            While .Busy Or .readyState <> 4: DoEvents: Wend

 [COLOR=#008000]       ' ELEMENTS TO EXTRACT FROM EBAY[/COLOR]
            Dim index As Long, HTMLItems As Object, rowNum As Long, xCell As Range
            Dim cssSelectors(), i As Long
            Select Case True
            Case InStr(.document.URL, "ebay.co.uk") > 0
                cssSelectors = Array(".lvtitle a", ".lvprice", ".lvtitle a") [COLOR=#008000]' New Line of Code for fix[/COLOR]
               [COLOR=#ff0000] 'cssSelectors = Array(".gvtitle a", ".amt", ".gvtitle a")[/COLOR] [COLOR=#008000]'-- only works with 1 url that has to be in code --[/COLOR]
            Case InStr(.document.URL, "ebay.com") > 0
                cssSelectors = Array(".s-item__title", ".s-item__price", ".s-item__link")
               
          End Select

           With ws
                For i = LBound(cssSelectors) To UBound(cssSelectors)
                    rowNum = 1
                    Set HTMLItems = ie.document.querySelectorAll(cssSelectors(i))

                    For index = 0 To HTMLItems.Length - 1
                        .Cells(rowNum, i + 1).Value = IIf(i = 2, HTMLItems.Item(index).getAttribute("href"), HTMLItems.Item(index).innerText)
                        rowNum = rowNum + 1
                    Next
                Next
           
        [COLOR=#008000] ' CLICK NEXT PAGE[/COLOR]
        [COLOR=#ff8c00]  Do
          If pageNumber >= 5 Then Exit Do 'the first 5 pages
              Set nextPageElement = htmlDoc.getElementById("pages") '
              If nextPageElement Is Nothing Then Exit Do
            
            nextPageElement.Click 'next web page
          Do While ie.Busy Or ie.readyState <> 4
               DoEvents
            Loop
            Application.Wait Now + TimeSerial(0, 0, 5)
            Set htmlDoc = ie.document
            pageNumber = pageNumber + 1
            
        Loop[/COLOR]
        
      [COLOR=#008000]  ' CONVERT URL TO HYPERLINKS[/COLOR]
            For Each xCell In .Range("C1:C25000") '<= all these really?
                    .Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
                Next xCell
                
            End With
        [COLOR=#008000]    ' QUIT IE BROWSER[/COLOR]
            .Quit
        End With
    End Sub

I can not work out why it will not go to the next page and extract again (code this above in orange). New Data MUST go into NEXT blank row. I know the code works as I have used it on another project.

Could some one take a look.

Also a big thanks to Joe4 as his bit of code put me on the right path.

 

[mole999]

do eBay expressly allow scraping ?

 

[Sharid]

Its only product details and NOT customer details like emails, just looking at what is selling and for how much, so I can work out my prices. Its also not a large search 5 pages.
The data can be gathered manually, but this is easier.

As for do they allow scraping, I think they may be more concerend about personal data rather than price of products. Many people already check price of other sellers, also software can be bought to do this, buy why bother if it van be done of VBA.

Also VBA may not be the best for large scale scraping as it can be heavy on the PC.