0

I want to make VBA code to search on a website on the basis of input made in the first column. Range is from A1 to A102. This code is working fine except one thing: It copies my data from Excel Cell and then paste it in the Search box of website. But it doesn't click the search button Automatically. I welcome any good Suggestions from Experts.

I know how to scrape data from websites but there is a specific class for this searchbox button. What would be this class I should use to made click? This question is relatable to both VBA and javascript/html Experts.

I am getting this as button ID " nav-search-submit-text " and this code as `Class " nav-search-submit-text nav-sprite ", when I click on Inspect element.

Both don't work?

Thanks

Private Sub worksheet_change(ByVal target As Range)

If Not Intersect(target, Range("A1:A102")) Is Nothing Then

Call getdata

End If

End Sub

Sub getdata()

Dim i As Long

Dim URL As String

Dim IE As Object

Dim objElement As Object

Dim objCollection As Object

Set IE = CreateObject("InternetExplorer.Application")

'Set IE.Visible = True to make IE visible, or False for IE to run in the background

IE.Visible = True

URL = "https://www.amazon.co.uk"

'Navigate to URL

IE.Navigate URL

'making sure the page is done loading

 Do
   
 DoEvents

 Loop Until IE.ReadyState = 4

 'attempting to search date based on date value in cell

 IE.Document.getElementById("twotabsearchtextbox").Value = ActiveCell.Value

 'Sheets("Sheet1").Range("A1:A102").Text

 'Select the date picker box and press Enter to 'activate' the new date

 IE.Document.getElementById("twotabsearchtextbox").Select

 'clicking the search button

 IE.Document.getElementsByClassName("nav-sprite").Click

 'Call nextfunction

 End Sub
DisappointedByUnaccountableMod
  • 6,656
  • 4
  • 18
  • 22
AlexG
  • 59
  • 2
  • 9
  • I give you the advice, deal with the structure of URLs. To search for products on Amazon you only need a URL built for the respective search term. The basic URL is `https://www.amazon.co.uk/s?k=`, followed by the search terms. If you are looking for `ear buds`, for example, write `ear+buds` after the equal sign. To make sure that the URL works, put the search words in the formula for encoding URLs https://support.microsoft.com/en-us/office/encodeurl-function-07c7fb90-7c60-4bff-8687-fac50fe33d0e – Zwenn Oct 26 '20 at 10:06
  • Two further remarks: If you use `IE.Document.getElementById("twotabsearchtextbox").Select` nothing happens and `IE.Document.getElementsByClassName("nav-sprite").Click` throws a runtime error because you have to specify the index which element of the NodeCollection formed by `getElementsByClassName("nav-sprite")` you want to click. – Zwenn Oct 26 '20 at 10:14
  • Hi there. Thanks for your attention. I want to make search using ASIN code (the unique ID of a product)..If I want to search for any product I would only add the ASIN code in excel cell and it will show me the price of that product in adjacent column automatically. As you know, Amazon prices change and update gradually. So whenever If I want to get the daily price updates, then I will just click the refresh macro button. And it will automatically update all the prices.I hope its not confusing.And more welcome if you are interested to solve my real query about search button. Thanks – AlexG Oct 26 '20 at 10:19
  • That question was answered here with two working solutions: https://stackoverflow.com/questions/64491907/fetch-product-price-and-name-from-amazon-with-vba/64498323 – Zwenn Oct 26 '20 at 10:22
  • Thanks for sharing knowledge! Can you suggest anything what should I use instead of "IE.Document.getElementsByClassName("nav-sprite").Click" to make the search button click automatically. – AlexG Oct 26 '20 at 10:25
  • Thanks for the link I am not in need of any code yet As I have already made my own code. I just simply in need of some suggestions about the class name I should use to make click on search button. Thanks and more Welcome If you want to help me in this regard. I guess this question is more relatable to HTML Experts instead of VBA experts. What should be the Class Should I use instead of " nav-sprite ". – AlexG Oct 26 '20 at 10:33

1 Answers1

1

To use web scraping with Excel, you must be able to use both VBA and HTML. Additionally CSS and at least some JS. Above all, you should be familiar with the DOM (Document Object Model). Only with VBA or only with HTML you will not get far.

It's a mystery to me why you want to do it in a complicated way when you can do it simply via the URL. For your solution you have to use the class nav-input. This class exists twice in the HTML document. The search button is the element with the second appearance of nav-input. Since the indices of a NodeCollection start at 0, you have to click the element with index 1.

Sub getdata()

Dim URL As String
Dim IE As Object

  URL = "https://www.amazon.co.uk"
  
  Set IE = CreateObject("InternetExplorer.Application")
  IE.Visible = True ' True to make IE visible, or False for IE to run in the background
  IE.Navigate URL 'Navigate to URL
  'making sure the page is done loading
  Do: DoEvents: Loop Until IE.ReadyState = 4
  
  'attempting to search date based on date value in cell
  IE.Document.getElementById("twotabsearchtextbox").Value = ActiveCell.Value
  
  'clicking the search button
  IE.Document.getElementsByClassName("nav-input")(1).Click
End Sub

Edit: Solution to open offer with known ASIN

You can open an offer on Amazon webpage directly if you know the ASIN. To use the ASIN in the active cell in the URL (this does not work reliably. If you have to press Enter to finish the input, the active cell is the one under the desired one), it can be passed as a parameter to the Sub() getdata():

Private Sub worksheet_change(ByVal target As Range)
  If Not Intersect(target, Range("A1:A102")) Is Nothing Then
    Call getdata(ActiveCell.Value)
  End If
End Sub

In the Sub() getdata() the URL with the transferred ASIN is then called:

Sub getdata(searchTerm As String)

Dim URL As String
Dim IE As Object
  
  'Use the right base url
  URL = "https://www.amazon.co.uk/dp/" & searchTerm
  
  Set IE = CreateObject("InternetExplorer.Application")
  IE.Visible = True ' True to make IE visible, or False for IE to run in the background
  IE.Navigate URL 'Navigate to URL
  'making sure the page is done loading
  Do: DoEvents: Loop Until IE.ReadyState = 4
End Sub

It's also possible to do that all in the worksheet_change event of the worksheet (Include getting price and offer title):

Private Sub worksheet_change(ByVal target As Range)
  If Not Intersect(target, Range("A1:A102")) Is Nothing Then
    With CreateObject("InternetExplorer.Application")
      .Visible = True ' True to make IE visible, or False for IE to run in the background
      .Navigate "https://www.amazon.co.uk/dp/" & ActiveCell 'Navigate to URL
      'making sure the page is done loading
      Do: DoEvents: Loop Until .ReadyState = 4
      'Get Price
      ActiveCell.Offset(0, 1).Value = .document.getElementByID("priceblock_ourprice").innertext
      'Get offer title
      ActiveCell.Offset(0, 2).Value = .document.getElementByID("productTitle").innertext
    End With
  End If
End Sub
Zwenn
  • 2,147
  • 2
  • 8
  • 14
  • Thanks a lot! Actually "nav-input" was the class name. But I wasn't using its first index. I was just simply accessing the class by making reference to it. Your solution worked. But what's is the method by URL? If I would add manual A URL of Product in my macro. I would have to change the macro for every product? Because Every product has different URL. Isn't its time-taking? Now I will just simply Write the ASIN code and it will fetch the data from site and paste it in adjacent column. – AlexG Oct 26 '20 at 11:40
  • @AlexG I have added two solutions, to open ASINs directly on the Amazon webpage. – Zwenn Oct 26 '20 at 12:31