Pulling Data from Web Automatically

[kanwalno1]

Hi All,

I am trying to gather data from web into excel. The problem is like
this.
1. http://incometaxindiaefiling.gov.in/knowtan/knowtan.jsp is address
of web site where you can search TAN data for the assesses using
search criteria, if you know the TAN.
2. If you select only the "Category of Deductor" and/or "State"
without mentioning anything in 3rd search criteria, it will give
results for the whole database by clubbing 10 results per page
(alongwith the page number links for further search)
3. Each link on the result page leads us to "Particulars" of that
link, which is the desired information I need.

Is there any way by which the particulars for each link on each page
are pulled/imported into one excel sheet/workbook (in columner form)
automatically, thereby avoiding the need to open each link and copying
the results to excel sheet again and again.

Kind Regards
CA Kanwaljit Singh Dhunna
India

 

[Don Guillett]

Give some examples of Category of Deductor" and/or "State"
along with a snippet of what is found. And, then what is done to find what?
Actual url's would as the selection changes would be helpful.

 

[kanwalno1]

Hi Don,
Thanks for posting. Let us select "Statutory/Autonomous Bodies/Local
Authorities" as category of deductor and "Punjab" as State and then
press "Submit". It will result in the entire list of 2720 assesses
being displayed in the sets of 10 links per page (Page 1 to page 272
displayed at the bottom). If I need to do it manually, I will open
each link, copy the data and paste them into separate columns one by
one. But that will take minimum 2720 clicks for a single category X
Single State.

What I require is a code which will automatically pull the data from
all the 2720 linked pages after I have searched for a combination of
"category of deductor" and "State".

Hope that clear the point.

Best Regards
CA kanwaljit Singh Dhunna
India

 

[Don Guillett]

Normally, I would be able to use something like this but I get "unable to
open"

Sub GetData()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://incometaxindiaefiling.gov.in/knowtan/knowtanresults.jsp?curpage=11",
_
Destination:=Range("A4"))
.BackgroundQuery = True
.SaveData = True
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.Refresh BackgroundQuery:=False
End With
End Sub

 

[kanwalno1]

Hi Don,

Thanks. I entered the above code in a standard module and executed it
from Tools->Macro->Run.
But it is returning a Runtime Error 1004 "Unable to Open"
I am new to VB, and don't know how to proceed. Any guesses ?

Regards
CA Kanwaljit Singh Dhunna
India