Extracting data in json format using vba

Question

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

Marcelo Scofano Diniz · Accepted Answer · 2021-10-28 15:54:33Z

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:

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)

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 ;)

edited Oct 28, 2021 at 15:54

answered Oct 28, 2021 at 13:48

Marcelo Scofano Diniz

7091 gold badge12 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Satti22 Over a year ago

why you are using p.Item(a-pagination")(0) as i am using class name in code?

Marcelo Scofano Diniz Over a year ago

@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

Satti22 Over a year ago

Still facing following error,by reference argument type mismatch on following line.Set p = JSON.parse(strFormattedJSON).If possible please update complete solution.

Marcelo Scofano Diniz Over a year ago

@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

Collectives™ on Stack Overflow

Extracting data in json format using vba

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related