Get Reg NOs from a website

vrunda · Nov 25, 2012

I have list of customers who are registered at a particular site. I want their Registration nos in my excel sheet to prepare report. & by this I shall come to know which customers are registered & which one unregistered?

vrunda · Nov 25, 2012

I tried to use web querries as shown in Mutual fund tracker. But its not possible as at that site we do not have Reg. Nos. List available. But if we put name of customers in search box we get the registration no.

So for this purpose how can we use Excel & VBA to automatically get data from site. We have list of customers in excel sheet. Now for every customer we have to put name in box specified & get the no. By vba can we automate the procedure?

SirJB7 · Nov 27, 2012

Hi, vrunda!

Does this helps?

http://chandoo.org/forums/topic/macros-web-query-huge-headache-can-anyone-help-me

Regards!

vrunda · Nov 28, 2012

Thanks for reply..Atlast someone replied. I searched the page already. Actually my requirement is On one website i have to go say the url is "http://mahavat.gov.in/Tin_Search/Tinsearch.jsp" .

In excel sheet i have name of customers in column A . on above url i have to go & type the name of customer in name field & after pressing enter i get the TIN no. I just want to ask can i autoamte this procedure & then get the TIN nos. in column B. One more condition is the Tin no should end with alphabet "V". & when the list of tin nos come , if they are more than 1 then it should copy in col C, Col D etc.

Here Tin nos means registration nos.

All this is ethical., & authentic.

vrunda · Nov 28, 2012

For eg , Customer name we may take as Giriraj Agencies. & put in dealers name search box & then enter . We may get result.. which we want on column B onwards.

vrunda · Nov 28, 2012

https://dl.dropbox.com/u/53850800/Book1.xlsx is the sample file

SirJB7 · Nov 28, 2012

Hi, vrunda!

In the uploaded file you didn't provide any useful data so as to test the TIN website. Please update it with data that let people who might read this perform query operations on the site. Thank you.

Regards!

vrunda · Nov 29, 2012

In sample file In column I gave few name of customers for who Tin have to be searched. But in reality I have long list of customers for which individually not possible to go to site always. Any button on sheet collecting the TIN would do.

https://dl.dropbox.com/u/53850800/Book1.xlsx

SirJB7 · Nov 29, 2012

Hi, vrunda!

I'm afraid that because of the way that the web page handles the query and display of the data, the method described in my uploaded file isn't applicable here. Sorry for not being of further help.

Regards!

vrunda · Nov 29, 2012

Thanks Sir JB7. Yes I tried in that way but could not do it . Any help would be appreciated.

NARAYANK991 · Nov 29, 2012

Hi All ,

The following segment of code navigates to the final page retrieving the TIN results for the submitted dealer name , in this case GIRIRAJ AGENCIES :

[pre]

Code:

Sub Test()
Const cURL = &#34;http://www.mahavat.gov.in/Tin_Search/Tinsearch.jsp&#34;

Const DataField = &#34;GIRIRAJ AGENCIES&#34;

Dim IE As InternetExplorer
Dim doc As HTMLDocument
Dim LoginForm As HTMLFormElement

Set IE = New SHDocVw.InternetExplorer
IE.Visible = True
IE.Navigate cURL

Do

Loop Until IE.ReadyState = READYSTATE_COMPLETE &#39;= 4

Set doc = IE.Document

Set LoginForm = doc.forms(0)

Set Input_Box = LoginForm.elements(&#34;DEALERNAME&#34;)

Input_Box.Value = DataField

LoginForm.submit
End Sub

[/pre]
Courtesy : http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/e282217d-f60b-40f6-92aa-6a5cf9ddc791/

For the above routine to work , you have to include references to :

1. Microsoft Internet Controls

2. Microsoft HTML Object Library

in your project.

Can someone take this forward to download the result table from the new page , and extract only those TIN entries which end in "V" ? I can continue to work on this , but I will take time.

Narayan

vrunda · Nov 29, 2012

Thanks!!Narayan,

So nice of u !! I shall try this in my book. & for getting only "V" I shall also try. Secondly if we take const data field it will take only one customer name which is given in code , so ithink we have to loop in column A until it is """". Pls continue even if it take time...

Thankss!!!1

vrunda · Nov 29, 2012

I tried the code. After going to final Tin page how to get the required data in excel sheet

NARAYANK991 · Nov 29, 2012

Hi Vrunda ,

Like I said in my earlier post , I will take some time. Give me a day or two.

In the meantime , if anyone else is interested to take this forward , I will request them to do so.

Narayan

NARAYANK991 · Nov 30, 2012

Hi Vrunda ,

Some progress.

[pre]

Code:

Sub Test()
Const cURL = &#34;http://www.mahavat.gov.in/Tin_Search/Tinsearch.jsp&#34;

Const DataField = &#34;GIRIRAJ AGENCIES&#34;

Dim IE As InternetExplorer
Dim doc As HTMLDocument
Dim LoginForm As HTMLFormElement

Set IE = New SHDocVw.InternetExplorer
IE.Visible = True
IE.Navigate cURL

Do

Loop Until IE.ReadyState = READYSTATE_COMPLETE

Set doc = IE.Document

Set LoginForm = doc.forms(0)

Set Input_Box = LoginForm.elements(&#34;DEALERNAME&#34;)

Input_Box.Value = DataField

LoginForm.submit

Do While IE.Busy
DoEvents
Loop

Set doc = IE.Document

For i = 0 To doc.all.Length - 1
If TypeName(doc.all(i)) = &#34;HTMLTable&#34; Then
Set tbl = doc.all(i)
If tbl.Rows.Length &#62; 2 Then
For j = 0 To tbl.Rows(0).Cells.Length - 1
If tbl.Rows(0).Cells(j).innerText = &#34;TIN NUMBER&#34; Then
For k = 1 To tbl.Rows.Length - 1
Debug.Print tbl.Rows(k).Cells(j).innerText
Next k
Exit For
End If
Next j
End If
End If
Next i

End Sub

[/pre]
Courtesy : http://www.dailydoseofexcel.com/archives/2006/11/29/html-tables/

Narayan

vrunda · Dec 1, 2012

Your code is working. I tried to replace debug.print by

tbl.Rows(k).Cells(j).innerText.select

selection.copy

Sheet3.Select

Sheet3.Range("B2").Select

Sheet3.PasteSpecial "Unicode Text"

But it is giving runtime error 424--- at first row--> tbl.Rows(k).Cells(j).innerText.select

NARAYANK991 · Dec 1, 2012

Hi Vrunda ,

Select is a method ; innerText is a property ; combining the two of them is going to generate an error.

Since innerText is a property , you can assign it to a variable ; instead of the following line in my posted code :

Debug.Print tbl.Rows(k).Cells(j).innerText

you can have :

tin_number = tbl.Rows(k).Cells(j).innerText

where tin_number is declared as a string variable ( Dim tin_number As String ).

Do you want me to post the complete code for what you wanted ? I think the rest of it can be done as follows :

1. Remove the assignment of DataField ( at present , it is assigned the static value "GIRIRAJ AGENCIES" ) ; introduce a loop to read each dealer's name , one by one , from a pre-defined range on your worksheet , and put this in Input_Box.

2. Once the result page appears , introduce a second loop to read the TIN NUMBERS one by one , check whether the last character is a "V" , and , if so , write it to the appropriate cell in your worksheet.

3. Once all the TIN NUMBERS for a particular dealer have been completed , go to the next dealer.

If you can do all of this yourself , probably you can do it earlier than if I were to do it ; I may take 2 or 3 days ; if this is acceptable , let me know.

Narayan

vrunda · Dec 2, 2012

Thanks!! for your help.

I m trying it.. meanwhile if its convinient u please go ahead with it.

vrunda · Dec 2, 2012

I am posting code from following lines only. I have declared Tin_number As String after other declarations & made changes as follows , but it is giving last value of Tin from html table i.e 27100046938P in whole range of Tinlist. I have named Range(b2:l2)as Tinlist.

For k = 1 To tbl.Rows.Length - 1

'Debug.Print tbl.Rows(k).Cells(j).innerText

Tin_number = tbl.Rows(k).Cells(j).innerText

ActiveSheet.Select

Range("Tinlist") = Tin

' Application.ScreenUpdating = True

Next k

Exit For

End If

Next j

vrunda · Dec 2, 2012

I modified a little bit to get the tin nos on activesheet. But how to start the loop to read the customers name from sheet i m not getting it.

It is giving error- - -NEXT without FOR

Sub Test()

Const cURL = "http://www.mahavat.gov.in/Tin_Search/Tinsearch.jsp"

'Const DataField = "Giriraj Agencies"

' Dim Partyname As String

Dim IE As InternetExplorer

Dim doc As HTMLDocument

Dim LoginForm As HTMLFormElement

Dim Tin_number As String

For F = 1 to partylist' ( where partylist is defined as range("A3:A500"))

datafield = activecell.value

Set IE = New SHDocVw.InternetExplorer

IE.Visible = True

IE.Navigate cURL

Do

Loop Until IE.ReadyState = READYSTATE_COMPLETE

Set doc = IE.Document

Set LoginForm = doc.forms(0)

Set Input_Box = LoginForm.elements("DEALERNAME")

Input_Box.Value = DataField

LoginForm.submit

Do While IE.Busy

DoEvents

Loop

Set doc = IE.Document

For i = 0 To doc.all.Length - 1

If TypeName(doc.all(i)) = "HTMLTable" Then

Set tbl = doc.all(i)

If tbl.Rows.Length > 2 Then

For j = 0 To tbl.Rows(0).Cells.Length - 1

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

For k = 1 To tbl.Rows.Length - 1

' Debug.Print tbl.Rows(k).Cells(j).innerText

Tin_number = tbl.Rows(k).Cells(j).innerText

ActiveSheet.Select

ActiveCell.Value = Tin_number

ActiveCell.Offset(0, 1).Activate

' Application.ScreenUpdating = True

Next k

Exit For

End If

Next j

Next F

End If

End If

Next i

End Sub

NARAYANK991 · Dec 2, 2012

Hi Vrunda ,

Today is Sunday ! I'll post something tomorrow morning.

Narayan

NARAYANK991 · Dec 3, 2012

Hi Vrunda ,

Can you check out the file here ?

http://speedy.sh/6pA7N/Vrunda-Web-Query.xlsm

Narayan

vrunda · Dec 3, 2012

Narayan Sir,

It is wonderful . Really appreciable. Hats off to you!!!

Sir as u wrote for Partylist -- change as required. But I am not getting the formula you wrote in name manager. I think it includes all names in column A. Is it correct?

Secondly can i give button on sheet 1 & assign this macro to it.?

Pls let me know about range name formula Sheet1!$A$5:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)+1).

On this sheet 1 we cannot delete any row after A5 & cannot use cut-paste commands. Any more instructions on this sheet to work flawlessly.

NARAYANK991 · Dec 3, 2012

Hi Vrunda ,

Yes , the named range Party_List has been defined as :

=Sheet1!$A$4:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)+1)

Essentially this is a list of all the vendors / customers.

I had originally defined the VBA variable partylist as a static range A3:A500 ; this why I had commented Change as required. Now , since a dynamic named range called Party_List has been defined within the worksheet , the VBA itself will not need to be changed.

If your sheet name is different from Sheet1 , you will have to change the following statement :

Sheets("Sheet1").Activate

Other than this , I don't think you will need to change anything more.

Deleting rows after A5 will not create any problem , since if rows are deleted , the named range will adjust itself automatically. The same goes for using cut-paste commands.

The following statement :

=Sheet1!$A$4:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)+1)

is a standard way of defining a dynamic named range i.e. the range expands to accommodate any fresh data that you might enter. The starting point is Sheet1!$A$4 ; the COUNTA(Sheet1!$A:$A) function counts the number of cells in column A , which have some data in them e.g. in your worksheet at present , this will return the number 16 , since the cells A1 through A17 , except cell A2 , have data in them. When we add 1 to this ( +1 ) , we get 17 , which is the row number of the last cell in column A which has data. Thus , we are defining the range Sheet1!$A$4:$A$17.

If you now add data in A18 , the COUNTA function will return 17 ( instead of the earlier 16 ) , and the range will now become Sheet1!$A$4:$A$18. Thus , without your having to do anything , the range has automatically adjusted its definition to include the new row of data. The only thing you have to ensure is that between the first row of data , say A4 , and the last row of data , say A17 , there are no blank cells , because the COUNTA function will return a wrong cell address for the last cell with data , in the formula for the named range.

In case you face any problems , please get back to me.

Remember that the +1 in the named range definition is because A2 is blank at present ; in case you enter something in this cell , or you delete this row altogether , then you will have to remove the +1 from the definition of the named range.

Narayan

vrunda · Dec 3, 2012

Thank you so much for explaining it so nicely. Thanks once again.

One more questtion :>>

Tin_number = Trim(tbl.Rows(k).Cells(j).innerText)

Why we use trim here above? I think trim is for removing space..am I correct?

What is match_found -- is it inbuilt Vba function.... ?

Why & when we use it?

Get Reg NOs from a website

Member

Member

Excel Rōnin

Member

Member

Member

Excel Rōnin

Member

Excel Rōnin

Member

Excel Ninja

Member

Member

Excel Ninja

Excel Ninja

Member

Excel Ninja

Member

Member

Member

Excel Ninja

Excel Ninja

Member

Excel Ninja

Member