Avatar of Svgmassive
Svgmassive
 asked on

sparse download file in vba

I would like to extract the location,city,country etc.from the attach file in vba.
text.txt
Microsoft ApplicationsMicrosoft AccessVBA

Avatar of undefined
Last Comment
Svgmassive

8/22/2022 - Mon
Pavel Celba

Your file is in JSON format which is described e.g. here: https://en.wikipedia.org/wiki/JSON

You may read it in VBA using e.g. JSON Converter: https://github.com/VBA-tools/VBA-JSON
Some code samples are also here: https://dymeng.com/parsing-json-with-vba/
aikimark

extract the location,city,country etc

Does "etc" mean you want all possible data?
aikimark

You can use the vbscript.regexp object to do your parsing.

The regex pattern you would need for this sample is:
"([^"]*)":"([^"]*)"|"([^"]*)":\d+|"([^"]*)":\[([^\]]*)\]

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
aikimark

Correction on the regex pattern.  I missed a capture group.  Should be:
"([^"]*)":"([^"]*)"|"([^"]*)":(\d+)|"([^"]*)":\[([^\]]*)\]

Open in new window

Svgmassive

ASKER
alimark having problems setting up the pattern with the quotation marks
aikimark

1. Select the pattern and replace single quote characters with two quote characters.
2. Put the resulting string inside a pair of quote characters.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Svgmassive

ASKER
aikimark got an example of the entire function?
ASKER CERTIFIED SOLUTION
aikimark

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Svgmassive

ASKER
aikimark Awesome!!