Hi Deepak,
Whats the website? How you set about doing this will probably depend
on the way that teh site structures its data. You may also end up with
an enormous amount of data and may therefore need to consider using a
database to store the info and then do your dashboarding in Excel from
that.
In simple terms you need to use a bit of automation to get the job
done, and probably some regular expressions. Here is an example using
IE with automation and a regular expression to capture the desired
data:
Function FTP_List() As Variant
Dim ie As InternetExplorer
Dim regex As RegExp
Dim regmatch As MatchCollection
Dim tmp As String
Dim i As Long
Dim list() As String
Set ie = New InternetExplorer
Set regex = New RegExp
ie.Navigate "<enter your website here>"
Do Until ie.ReadyState = READYSTATE_COMPLETE
Loop
With regex
.Pattern = "<enter your regex pattern here>"
.IgnoreCase = True
.Multiline = False
.Global = True
End With
tmp = ie.Document.body.innertext
Set regmatch = regex.Execute(tmp)
ReDim list(0)
If regmatch.Count > 0 Then
For i = 0 To regmatch.Count - 1
ReDim Preserve list(i)
list(i) = regmatch(i)
Next
End If
FTP_List = list()
Set ie = Nothing
Set regex = Nothing
Set regmatch = Nothing
End Function
This could probably be easily modified to meet your requirements as
long as a suitable regex can be built to extract the data you want
from the web pages' source data (thats the ie.Document.body.innertext
above that I pass into a string variable). You can use regex to
capture multiple 'groups' of data in a single line and the regex
collection object will collect and collate it all nicely for you.
I imagine that you will need to perform some sort of cleansing of the
collected data after a capture so that duplicate info isnt captured
and stored (ie/ you dont want things that havent changed). How to
manage the output is entirely up to you and what you want to achieve.
I hope this is of some help to you.
Cheers
The Frog
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
