Get Reg NOs from a website

[NARAYANK991]

Hi Vrunda ,


The TRIM , and match_found are both solutions which were put in after I came across the problem !


1. When I first ran the code without the TRIM and did RIGHT(tin_number,1) to extract the last character of the TIN NUMBER , so that I could check to see whether it is a "V" , I found that even though I could see that the TIN NUMBER had a "V" at the end , the program was not able to see it ! When I checked further , I saw that all the TIN NUMBERS had an extra space character at the end ; so the last character in the TIN NUMBER is a space , and not "C" , "V" , "E" , "P" ,... Hence we have to use the TRIM function to remove the extra space at the end of the TIN NUMBERS.


2. match_found is another story ! The first four dealers displayed correctly , as I kept changing the dealer name , but when I came to the fifth dealer , there was a problem because this dealer had no records. Now , when we try to enter the sixth dealer's name , there is a problem because there is no box where the dealer's name can be entered ! The form itself had changed. So , whenever we come across a dealer whose name has no records associated with it , we cannot just go to the next dealer ; instead , we have to navigate back to the original data entry form. Hence the use of match_found , which is just a VBA Boolean variable ; whenever we come across at least one entry pertaining to a dealer , we know that we can enter the next dealer's name in the same form ; when there are no entries against a particular dealer , we navigate back to the original form , and then enter the next dealer's name. match_found tells us whether any records have been found or not.


At the beginning of the section for a particular dealer , we put match_found to FALSE ; when we pass through the inner-most IF section , we put match_found to TRUE , meaning we have found at least one record for that particular dealer.


Is this explanation OK ?


Narayan

 

[vrunda]

YES ABSOLUTELY OK!!

You said >> "At the beginning of the section for a particular dealer , we put match_found to FALSE ; when we pass through the inner-most IF section , we put match_found to TRUE , meaning we have found at least one record for that particular dealer."


What i understood is even if there are no records found for a dealer we say match is found so that it should read & go to next dealer i.e. we have found at least one record for that particular dealer.


Sir,

How i can be master like u in Excel & VBA?

I m going through John Walkenbach-- Power programming.

Any other suggestions... tips....

 

[NARAYANK991]

Hi Vrunda ,


If you master Walkenbach's book , I think you should do very well.


Regarding your comment about match_found , I think there is some misunderstanding ;

[pre]

Column_counter = 1
match_found = False
Set doc = IE.Document
For i = 0 To doc.all.Length - 1
In the above section , we are initialising match_found to FALSE , before we start the loop which looks to see if there are any records for the given dealer name.


If tbl.Rows(0).Cells(j).innerText = "TIN NUMBER" Then

[/pre]
The above IF statement is the one which decides whether there are any records for the given dealer ; if there are no records , we will not get the header row which contains the text "TIN NUMBER" ; instead we will just have one row returning the error message "No records found".


If we go through this IF statement , we put match_found to TRUE.


EX: is the section , where we check for match_found ; if it is TRUE , we don't need to do anything else ; we can go to the next dealer , and put in the dealer's name in the required box on the form ; if match_found is FALSE , it means there were no records against the given dealer , which in turn means the web-page being displayed is no longer the data entry page ; so , in this case , we have to navigate back to the original form ( http://www.mahavat.gov.in/Tin_Search/Tinsearch.jsp ) , and then we can enter the next dealer's name in the data entry box.


Narayan

 

[vrunda]

https://dl.dropbox.com/u/53850800/Vrunda_Web_Query.xlsm


Sir , i found one problem in it. If there is only one tin no. containing V at end & no other parties , then the column B dont get data copied. Pls see file above & see when website opens it displays Tin no. but still the no. is not copied on sheet1.

 

[NARAYANK991]

Hi Vrunda ,


Sorry ; the problem was just this statement :


If tbl.Rows.Length > 2 Then


Change the above statement to :


If tbl.Rows.Length >= 2 Then


When only one record is returned , then the number of rows is 2 , so the earlier check for greater than 2 was not being fulfilled ; change the check to "greater than or equal to".


Narayan

 

[vrunda]

THANKS!!

IT WAS REALLY SIMPLE. tHANKS ALOT.

If i have some questions i shall come again.

THANKS

 

[AYR]

Hi Vrunda and narayank ,
After a lot of searching I found about this thread in this forum

Congratulations on your efforts to crack the Excel macro to get tin number details from Mahavat Website

My problem is same as yours,

please can you send me the link of the working file which can get address from the website of Mahavat

I need urgently

thanks in advance

 

[NARAYANK991]

Hi ,

The same file is uploaded here.

Narayan

 

[AYR]

hi naryank 991
thanks for the prompt reply , its a amazing sheet
but my requirement seems to be slightly different

i want to enter the vat tin nos in one coloumn and the excel sheet should look up the mahavat website and get me the dealer name and address.

i want to check these address in my database if they are correct




i have a list with tin nos in column a

for eg. 27090300453v


when i run the macro

in case tin no is wrong the cells can be populated as null


now i should get address in column as under


column b " elite traders"

column c "address1 " i.e " 90/b"

column d " "street name" i.e " blank"

column e " address 2 "i.e " crawford market"

column f " address 3 " i.e " mohammedali road"

coloumn g " taluka " i.e"*"

column h " district" i.e " mumbai"

column i " city name" i.e " mumbai"

coloumn j " state name" i.e " mumbai"

coloumn k " pin code" i.e " 400003"


your help in the matter will greatly help me


thanks in advance

 

[AYR]

Hello any body can you please help me with the above macro

I thank a million to any one who can

 

[SirJB7]

@AYR
Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one. NARAYANK991 posted a link to the file you requested, but if you need further assistance you better read what follows.

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.

Regards!

PS: Please don't answer here at this thread.

 

[NARAYANK991]

Hi ,

If you can wait for a few more days , I can upload a file with the revised code.

Narayan