0

I am currently using below mentioned code for data extraction from web,code is extracting data from web without any issue,now i want extract same data in json format.My code is given below.My page usrl is given below https://www.amazon.com/s?crid=AQO8JFH64H82&i=merchant-items&k=socks&me=A29M0OOY4LPNOT&qid=1635324852&ref=glow_cls&refresh=1&sprefix=socks%2Caps%2C315&low-price=0.1&high-price=5

Set Ie = CreateObject("InternetExplorer.Application")
Ie.Visible = True
Ie.navigate "https://www.amazon.com/s?crid=AQO8JFH64H82&i=merchant-items&k=socks&me=A29M0OOY4LPNOT&qid=1635324852&ref=glow_cls&refresh=1&sprefix=socks%2Caps%2C315&low-price=0.1&high-price=5"
Set html = Ie.Document
Set fso = CreateObject("Scripting.FileSystemObject")
Dim Fileout As Object
Set Fileout = fso.CreateTextFile("C:\Data\test" & Format(Now(), "_yyyy-mm-dd_hh-mm") & ".txt", True, True)
If html1 Like "*a-section a-spacing-small a-spacing-top-small*" Then
If html1 Like "*a-pagination*" Then
pagen = html.getElementsByClassName("a-section a-spacing-small a-spacing-top-small")(0).innerText
pagen = Replace(pagen, "'", "")
endpage = html.getElementsByClassName("a-pagination")(0).innerText
fileout.write endpage
Satti22
  • 11
  • 2

1 Answers1

0

First, welcome to SO.

You have to decide if you want to parse json for yourself, or want to use one of several available libraries:

  1. For use libraries, I recommend first, go to THE SOURCE of Json parsers.

After, please go to this answer; it is a must, and it is as easy as that:

Dim p As Object
Set p = JSON.parse(strFormattedJSON)

But, for parse it, you will need to understand inner structure, as most JSON libraries parse JSON objects to Arrays or Dictionaries, nested inside each other; maybe you have to access them like that:

Debug.Print p.Item("a-pagination")(0)
  1. For parse youself, here is a very good implementation. This also is good and leads to this one, the better.

PS: Our search engine is very good ;)

Marcelo Scofano Diniz
  • 590
  • 1
  • 7
  • 24
  • why you are using p.Item(a-pagination")(0) as i am using class name in code? – Satti22 Oct 28 '21 at 14:01
  • @Satti22, in this solution, after you parse an object to JSON, it will get into Arrays or Dictionaries, using names of properties/elements as keys. I did not implemented it because I'm out of VBA editor in a tablet; that is why I wrote _maybe you have_ – Marcelo Scofano Diniz Oct 28 '21 at 15:53
  • Still facing following error,by reference argument type mismatch on following line.Set p = JSON.parse(strFormattedJSON).If possible please update complete solution. – Satti22 Oct 28 '21 at 16:44
  • @Satti22 My answer was only to provide clues and tell that there are two basic ways. In next 24h I'll have access to a VBA editor and will try one of these solutions, and will edit it – Marcelo Scofano Diniz Oct 28 '21 at 18:24