Get data from browser
 

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

Get data from browser
 

Google this group for "IE automation"

Tim

"Vivek" wrote in message
...
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

Get data from browser
 

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" <timjwilliams at gmail dot com wrote in message ...
Google this group for "IE automation"

Tim

"Vivek" wrote in message
...
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

Get data from browser
 

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



"Vivek" wrote in message
...
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" <timjwilliams at gmail dot com wrote in message
...
Google this group for "IE automation"

Tim

"Vivek" wrote in message
...
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

Get data from browser
 

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" <timjwilliams at gmail dot com wrote in message ...
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



"Vivek" wrote in message
...
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" <timjwilliams at gmail dot com wrote in message
...
Google this group for "IE automation"

Tim

"Vivek" wrote in message
...
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

Get data from browser
 

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

Tim


"Vivek" wrote in message
...
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" <timjwilliams at gmail dot com wrote in message
...
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



"Vivek" wrote in message
...
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" <timjwilliams at gmail dot com wrote in message
...
Google this group for "IE automation"

Tim

"Vivek" wrote in
message
...
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

Get data from browser
 

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

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

Rick


"Vivek" wrote in message
...
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" <timjwilliams at gmail dot com wrote in message
...
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



"Vivek" wrote in message
...
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" <timjwilliams at gmail dot com wrote in message
...
Google this group for "IE automation"

Tim

"Vivek" wrote in
message
...
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

Get data from browser
 

Good catch.
Sometimes my newsreader gets too "helpful"

Tim

"Rick Rothstein (MVP - VB)" wrote in
message ...
Try putting the argument in quotes and see if that helps...

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

Rick


"Vivek" wrote in message
...
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" <timjwilliams at gmail dot com wrote in message
...
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



"Vivek" wrote in
message
...
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" <timjwilliams at gmail dot com wrote in message
...
Google this group for "IE automation"

Tim

"Vivek" wrote in
message
...
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

Get data from browser
 

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.as p');"Sell</a</td

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

Thanks again,
Vivek

"Tim Williams" <timjwilliams at gmail dot com wrote in message ...
Good catch.
Sometimes my newsreader gets too "helpful"

Tim

"Rick Rothstein (MVP - VB)" wrote in
message ...
Try putting the argument in quotes and see if that helps...

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

Rick


"Vivek" wrote in message
...
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" <timjwilliams at gmail dot com wrote in message
...
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



"Vivek" wrote in
message
...
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" <timjwilliams at gmail dot com wrote in message
...
Google this group for "IE automation"

Tim

"Vivek" wrote in
message
...
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

Get data from browser
 

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.



"Vivek" wrote in message
...
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.as p');"Sell</a</td

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

Thanks again,
Vivek

"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
Good catch.
Sometimes my newsreader gets too "helpful"

Tim

"Rick Rothstein (MVP - VB)" wrote in
message ...
Try putting the argument in quotes and see if that helps...

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

Rick


"Vivek" wrote in
message
...
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" <timjwilliams at gmail dot com wrote in message
...
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



"Vivek" wrote in
message
...
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" <timjwilliams at gmail dot com wrote in message
...
Google this group for "IE automation"

Tim

"Vivek" wrote in
message
...
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

Get data from browser
 

Thanks Tim, for helping all the way :)

"Tim Williams" <timjwilliams at gmail dot com wrote in message ...
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.