[Music]
hey guys welcome back to BB A to Z if
you remember in our last two videos we
have learned how to use VBA to control
the web elements using we have used the
internet controls Microsoft internal
controls and the HTML object library to
do this and today I'm gonna show you one
more thing like how to use VBA to
synchronize data from the web pages I
have this web page which is MIDI control
calm and here you have the latest you
know the most current stock market
market action of the top buyers and you
know I mean this is just an example so
I'm not going into the thing but the
idea is basically to synchronize the
data from the web page which you can see
right here for example okay so I'm just
gonna quickly dive in for example what I
did for myself I mean I have done quite
a number of stuff using other tools as
well but for for me you know sometime I
try to you know synchronize how many
views are there in the YouTube channel
for example and how many subscribers are
there in this channel for example okay
so we have I've already created a
connection and you know every time I run
a microt it gives me you know that my
ladies these of subscribers and ramiz as
per date so for example if I run this
code right now if I run this it will
give me as of right now what is the
subscribers I've got so I'm expecting 53
and then 166 thousand views right so if
so if you can see down here it's copying
web data to your sheet and it first
copies the data to one sheet and then it
try to pull this into a new sheet using
you know loop and all this so if you
look at this this is 853 as you can see
up here is a tree and then the views is
same as
of this so basically we're trying to do
a similar stuff here so I'm just gonna
close this for now I'll create a new
workbook okay this one is not the one
that we're using earlier this is a
different one we're gonna so first I
have this link let me take this link
first the webpage doesn't have to be
open so let me close this I'm starting
my recorder how this cold is sing beta
and then I'll click on data and then
I'll click on from web get external data
from where I'll just go and put in this
link and click on go so if you get a
problem like this just allow it to run
this is for the JavaScript and you know
the data that needs to be loaded for
this page to work okay okay so once your
page is loaded like you can see if you
see this arrow so if you click on this
box and if you click on import it will
import the whole data that's on this
page okay
but since we don't want that let's say I
just want to import all these tables
okay
so just take this box for example let's
import all these six tables okay so once
you've selected the table that you want
or the data set so all that can be
imported will you have a box like this
the arrow Mar basically so check that
box and once you're done with that you
just click on import okay so you select
wherever you want import let's go to
properties and quickly check that you
know everything is fine
and it will this and if you want to put
a name for this you know import it just
put that name once you're done just
click on OK and then ok again so I'm
expecting that the data will be in eh
looks like it's copying the data
this year so it once you are able to set
up the connection
it's definitely I'm stopping my
recording definitely what gonna work
again next time when you do it again so
let's say I've cleared this date down it
will say like the rains associated with
the quarry is gonna be deleted I'll say
no let's clear that they done not the
query okay so if you right-click on this
area you'll see there's a refresh there
okay so as soon as you click on refresh
it will synchronize the whole they die
again here for you like that
so as simple as that you can use the
matter like this to get the data you
want from any of the webpages okay works
pretty consistent I've used this for a
while and I hope this work for you as
well
so once you've set up the connection
you'll be able to see the connection is
here okay so if you go here you'll see
this is the connection string like money
control calm and all this okay so if you
click on add a query ok that we don't
need can't do it for now that's just
because this is not like the other
connections that will become like a
fresh one that you be creating again so
the other thing is like if you go and
see the code if you have to recreate
this or if you have to share this to
someone you can just disable this common
type equal to zero so for example let me
just delete this tab okay let me just
delete this tab and the connection is
gone
so if I run this I am expecting this to
do the same kind of work that was done
so I'll just run the code and I'm
expecting the data to come here right so
it's there so this is how you can pull
the data from the web page and if you
want to you know if you don't want to
run this code every time you can simply
let me just record a macro again and
this you know instead of running the
same code I can just run the retrieve
instead okay so if you go to the code
you'll see here that range one that's
like and then
saying query table that refreshed by
grigory false so this at the top this
thing data is basically this is creating
connection and then importing the data
from well okay so this is simply
refreshing the connection so I hope you
found this informative I'll you know
have this the code in the blog as well
but I think you can just try it out
yourself and then if you have any
questions please feel free to come back
for any questions so sometimes you might
not you might not get the data in the
same layout like you want but the way it
gets imported and works is pretty
consistent so you might want to write
additional code to organize your data in
a different tab and let this one be you
know like the connection I can bridge to
the website so that's all for now thank
you so much and I will try to do more
videos in coming days thanks for
watching bye bye