Get data from browser

[Vivek]

Hi,

I'm looking for code to grab data from a browser window that is currently open. The browser title will be fixed.
I don't prefer the web query option as the data in question is accessible only after logging in and other clicks. I use Office XP and Windows XP SP2.

Thanks,
Vivek

 

[Tim Williams]

Google this group for "IE automation"

Tim

Hi,

I'm looking for code to grab data from a browser window that is currently
open. The browser title will be fixed.
I don't prefer the web query option as the data in question is accessible
only after logging in and other clicks. I use Office XP and Windows XP SP2.

Thanks,
Vivek

 

[Vivek]

Tim,

Tried your tip but could not find any usable posts. Can you point to any specific post in your knowledge that deals with code for trying to grab data from IE. That would be very useful for me. This is only for one-time personal use

Thanks,
Vivek

 

[Tim Williams]

Without knowing exactly what info you need to extract from the page it's
difficult to offer anything concrete, but something like this would get you
started:

Sub Tester()

dim oDoc
set oDoc = GetIE(http://www.somesite.com/)

if not oDoc is nothing then

'do something with oDoc
msgbox oDoc.body.innerHTML

else
msgbox "not found"
end if

End Sub

'Find an open IE window with matching location and
' return a reference to the document object
Function GetIE(sAddress As String) As Object

Dim objShell As Object, objShellWindows As Object, o As Object
Dim retVal As Object, sURL As String


Set retVal = Nothing
Set objShell = CreateObject("Shell.Application")
Set objShellWindows = objShell.Windows

For Each o In objShellWindows
sURL = ""
On Error Resume Next
sURL = o.document.Location
On Error GoTo 0
If sURL <> "" Then
If sURL Like sAddress & "*" Then
Set retVal = o.document
Exit For
End If
End If
Next o

Set GetIE = retVal
End Function

Tim



Tim,

Tried your tip but could not find any usable posts. Can you point to any
specific post in your knowledge that deals with code for trying to grab data
from IE. That would be very useful for me. This is only for one-time
personal use

Thanks,
Vivek

 

[Vivek]

Thanks Tim.

When i put your code in a module the "set oDoc=" line appears in red. Running the macro results in a syntax error on that line. What could be going wrong here and how can I fix it?

I'm looking to extract a table from the website - similar to what a web query does from within Excel.

Vivek

 

[Tim Williams]

Post the code exactly as you have entered it in Excel.

Tim


Thanks Tim.

When i put your code in a module the "set oDoc=" line appears in red.
Running the macro results in a syntax error on that line. What could be
going wrong here and how can I fix it?

I'm looking to extract a table from the website - similar to what a web
query does from within Excel.

Vivek

 

[Rick Rothstein \(MVP - VB\)]

Try putting the argument in quotes and see if that helps...

set oDoc = GetIE("http://www.somesite.com/")

Rick


Thanks Tim.

When i put your code in a module the "set oDoc=" line appears in red.
Running the macro results in a syntax error on that line. What could be
going wrong here and how can I fix it?

I'm looking to extract a table from the website - similar to what a web
query does from within Excel.

Vivek

 

[Tim Williams]

Good catch.
Sometimes my newsreader gets too "helpful"

Tim

 

[Vivek]

Ok Thanks for the helping hand. After the correction, the html of the page now comes up in a msgbox. Now how do I process this to get the table on the page into Excel? Do I need to write code to look for the data? Or is there a simpler way?

Anyway, the table i need consists of rows with 11 cells to each row as seen in the HTML pasted below, out of which i need "BANBAR" "45" and "0" (cells 1, 3 and 4) on the same row in, say, cells A2, B2 and C2 (if row 1 is reserved for the table header). The rest of the row is not needed only cells 1, 3 and 4 are enough.

-----Begin HTML copy-paste ----------------------------------------
<tr align="center" bgcolor="#EAE5CC">

<td class="content">BANBAR<input type="hidden" name="FML_STCK_CD1" value="BANBAR"><input type="hidden" name="FML_ISIN1" value="INE028A01013"></td>

<td class="content">&nbsp;</td>

<td class="content" align="right">45</td>

<td class="content" align="right">0<input type="hidden" name="FML_TOT_BLOCK_QTY_ARRY1" value="0"></td>

<td class="content">0</td>

<td class="content" align="right"><a href="javascript://" onclick="GetQuote('BANBAR','BSE')"><font color="blue">203.50</font></a></td>
<td class="content" align="right">9,157.50</td>

<td class="topadd"><select disabled name="FML_AR1" class="dropdown" onChange="ChangeQty('1',document.Verify_order.FML_AR1.selectedIndex)"><option value="A">Allocate</option>
<option value="R">DeAllocate</option>
</select></td>
<td class="topadd"><input type="textbox" class="textbox_trade" name="FML_QUANTITY1" size="5" maxlength="8" value="" disabled><input type="hidden" name="FML_QUANTITY_HIDDEN1" value=""></td>

<td width="5%" class="topadd"><a href="JavaScript:GoToBuy('BANBAR','Trading_Buy.asp');">Buy</a></td>
<td width="5%" class="topadd"><a href="JavaScript:GoToBuy('BANBAR','Trading_Sell.asp');">Sell</a></td>

</tr>
-----END of HTML-------------------------------------------------------

Thanks again,
Vivek

 

[Tim Williams]

If there's only one table on the page and your info is in the first row:

'***********************
Dim t
Set t = oDoc.getElementsByTagName("TABLE")(0)

Msgbox t.rows(0).cells(0).innerText '1st cell
Msgbox t.rows(0).cells(2).innerText '3rd cell
Msgbox t.rows(0).cells(3).innerText
'***********************

Might have to play around with the row index. Note that both the row and
cell index are zero-based.

Tim.



Ok Thanks for the helping hand. After the correction, the html of the page
now comes up in a msgbox. Now how do I process this to get the table on the
page into Excel? Do I need to write code to look for the data? Or is there a
simpler way?

Anyway, the table i need consists of rows with 11 cells to each row as seen
in the HTML pasted below, out of which i need "BANBAR" "45" and "0" (cells
1, 3 and 4) on the same row in, say, cells A2, B2 and C2 (if row 1 is
reserved for the table header). The rest of the row is not needed only cells
1, 3 and 4 are enough.

-----Begin HTML copy-paste ----------------------------------------
<tr align="center" bgcolor="#EAE5CC">

<td class="content">BANBAR<input type="hidden"
name="FML_STCK_CD1" value="BANBAR"><input type="hidden" name="FML_ISIN1"
value="INE028A01013"></td>

<td class="content">&nbsp;</td>

<td class="content" align="right">45</td>

<td class="content" align="right">0<input type="hidden"
name="FML_TOT_BLOCK_QTY_ARRY1" value="0"></td>

<td class="content">0</td>

<td class="content" align="right"><a href="javascript://"
onclick="GetQuote('BANBAR','BSE')"><font color="blue">203.50</font></a></td>
<td class="content" align="right">9,157.50</td>

<td class="topadd"><select disabled name="FML_AR1"
class="dropdown"
onChange="ChangeQty('1',document.Verify_order.FML_AR1.selectedIndex)"><option
value="A">Allocate</option>
<option value="R">DeAllocate</option>
</select></td>
<td class="topadd"><input type="textbox" class="textbox_trade"
name="FML_QUANTITY1" size="5" maxlength="8" value="" disabled><input
type="hidden" name="FML_QUANTITY_HIDDEN1" value=""></td>

<td width="5%" class="topadd"><a
href="JavaScript:GoToBuy('BANBAR','Trading_Buy.asp');">Buy</a></td>
<td width="5%" class="topadd"><a
href="JavaScript:GoToBuy('BANBAR','Trading_Sell.asp');">Sell</a></td>

</tr>
-----END of HTML-------------------------------------------------------

Thanks again,
Vivek

 

[Vivek]

Thanks Tim, for helping all the way