how to get data from online data base.

[mahaveer]

Actually i have a list of dealer with their TIN Number But i need to Verify these TIN Number on this website that is :[url]http://rajtax.gov.in/vatweb/dealer/dealerMain.jsp?viewPageNo=13 [/URL]


so problem is this it takes so much time so i need a vba code with loop so that Name of dealer will show automatically in next column to their tin number.

I am attaching a sample excel file.

in the above site each time
SEARCH BY = TIN
DEALER TYPE = VAT
ENTER THE SEARCH CRITERIA = IT WILL BE LIST OF TIN NUMBER WHICH I PROVIDED IN MY EXCEL SHEET.

Any help much appreciated.


Thanks & Regards
CA Mahaveer Somani

 

[Deepak]

This might help...

 

[Deepak]

Same question is asked two times....
So need to lock one of them.

http://chandoo.org/forum/threads/how-to-get-data-from-online-data-base.18111/

 

[mahaveer]

mr. deepak,


you are awesome.

Thank you very much sir,

but i have one problem if the tin number is not registered on this website then it should mentioned "Not registered" but in your macro it is showing
"AJMER - A - I"

and one more thing it works fine but it will take time. so can i get the whole list of the registered dealer so that when ever i have required any tin number then i can search it in my excel sheet.

Thanks again sir,

and one more thing actually one member told me that please post your thread in vba macro forums. not here because it is only excel formula forums. so i post it again. Sorry

 

[Marc L]

Hi Mahaveer !

Many dealers in the list ? Deepak code is it fast enough ?

I wanna know before to explore a faster way …

Attach a sample .xlsx file with registered and unregistered numbers.
​

 

[mahaveer]

MARC L Sir,

You are right the provided code is superb and faster too. but suppose i have some tin numbers of some customers which are wrong mentioned in my book.

and when i would check these tin numbers with the provided code then i will get blank answer. it means tin number is wrong. then again i have to move on website.

now suppose if i would have list of all dealers then i can search with the name also using CTRL+F.

Thats why i was asking if it is possible otherwise my intention was not wrong sir.

even i m really very thankful to Mr. Deepak who spend their valuable time to me and you also sir.

if it is possible then please provide me the whole list sir.

 

[Marc L]

According to your need, to well understand it, attach a .xlsx workbook with a source worksheet and
a result desired worksheet, with registered and unregistered numbers with results by name …​

 

[Deepak]

but i have one problem if the tin number is not registered on this website then it should mentioned "Not registered" but in your macro it is showing
"AJMER - A - I"

Need to validate url before run so that will check & update you @ night.

and one more thing it works fine but it will take time. so can i get the whole list of the registered dealer so that when ever i have required any tin number then i can search it in my excel sheet.

It's a java based website so that i am not famlier wit that.
Stay tuned for further replies.

 

[Marc L]

On my test computer, using a faster way save 1.2 second from using Querytable
for the two TIN numbers sample …​

 

[Deepak]

Need to validate url before run so that will check & update you @ night.



It's a java based website so that i am not famlier wit that.
Stay tuned for further replies.

In a lazy approach change below & check.

fn = ActiveSheet.Cells(3, 4).Value
c.Offset(, 1).Value = fn

to

fn = ActiveSheet.Cells(3, 4).Value
If InStr(fn, "- A -") > 0 Then fn = ""
c.Offset(, 1).Value = fn

 

[Marc L]

Same procedure could search either by number or by name
but searching by name increases process time from a factor 2 to 6 x …

Original sample file process times TIN only (2 lines) :
• GetDatafromweb : 3.697s
• Demo . . . . . . . . : 2.246s

Searching by name "GCKC" firm needs one pass and almost 2 more seconds,
"Keshav Sales" firm needs four passes (try it manually !)
and so 8.5 more seconds :
• Demo "GCKC" . : . 3.992s (and searches other line by number)
• Demo "Keshav" : 10.656s (same as above)
• Demo both . . . : 12.885s …

Times (could change from computer to another one) for 2 lines only !
So better is to carry favour to TIN number …

Tests done when server wasn't lagging but it often lags !​