To store the values in excel sheet using VBA

Closed
suman - Feb 9, 2009 at 07:08 AM
sunilmkhr Posts 1 Registration date Tuesday July 13, 2010 Status Member Last seen July 18, 2010 - Jul 18, 2010 at 10:23 AM
How to store the values which are in a variable to an Excel sheet using Macros VBA
sample code is
Public Sub procWebMacro()
>
> Dim objBrowser As SHDocVw.InternetExplorer
> Dim objHTMLDoc As MSHTML.HTMLDocument
>
> Set objBrowser = New SHDocVw.InternetExplorer
> objBrowser.Visible = True
> objBrowser.navigate "http://www.indianrail.gov.in/inet_metro_trns.html"
>
> While objBrowser.readyState <> READYSTATE_COMPLETE
> DoEvents
> Wend
>
> Set objHTMLDoc = objBrowser.document
>
> Set objElement = objHTMLDoc.getElementsByName("lccp_src_stncode").Item(0)
>
>
> For I = 0 To objElement.Length
> If Trim(objElement.Item(i).Text) = "HYDERABAD - HYB" Then
> objElement.Item(i).Selected = True
> Exit For
> End If
> Next I
>
> End Sub

Now how to store "objElement " values to excel sheet

1 reply

sunilmkhr Posts 1 Registration date Tuesday July 13, 2010 Status Member Last seen July 18, 2010 1
Jul 18, 2010 at 10:23 AM
Hi Suman,

Please try the below code
, This will put all data in 1st column of sheet1, if you want change column no. just change value of N


Dim objBrowser As SHDocVw.InternetExplorer
Dim objHTMLDoc As MSHTML.HTMLDocument
Dim N as integer
N=2

Set objBrowser = New SHDocVw.InternetExplorer
objBrowser.Visible = True
objBrowser.navigate "http://www.indianrail.gov.in/inet_metro_trns.html"

While objBrowser.readyState <> READYSTATE_COMPLETE
DoEvents
Wend

Set objHTMLDoc = objBrowser.document

Set objElement = objHTMLDoc.getElementsByName("lccp_src_stncode").Item(0)


For I = 0 To objElement.Length
If Trim(objElement.Item(i).Text) = "HYDERABAD - HYB" Then
sheets("sheet1").cells(N,1).select
objElement.Item(i).Selected = True
sheets("sheet1").cells(N,1).Value = ObjElement.Item(i)
N=N+1
Exit For
End If
Next i

End Sub
1