Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
navigating to an unnamed spawned window ?
I have the following VBA code to launch an IE application, navigate to a URL,
and submit a form. The page which pops-up does not have a directly addressable URL, but is generated and directly filled-in by java when the form is submitted. What is the VBA statement needed in the following code to get a handle to the unnamed spawned page so that objects on the page can be referenced ? Sub Clt_Data_Fetch() Dim ie As Object ' launch IE and navigate to URL Set ie = CreateObject("InternetExplorer.Application") ie.Visible = True ie.navigate "http://thisistheURL/index.html" ' wait for login page to come up Do While ie.Busy: DoEvents: Loop Do Until ie.ReadyState < 1: Loop ' fill in login form and submit ie.document.all.Item("name").Value = "loginstring" ie.document.all.Item("passwd").Value = "passwordstring" ie.document.all.Item("submit").Click ' wait for spawned page to come up Do While ie.Busy: DoEvents: Loop Do Until ie.ReadyState < 1: Loop Application.Wait (Now() + TimeValue("0:00:07")) ' close login page, leaving spawned page visible ie.Quit ' *********** what is statement needed here to make reference to a 'table' object with ' ID = "abcTab" in the unnamed spawned page ****************** endSub -- tmp2100 Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
navigating to an unnamed spawned window ?
If you know the URL of the spawned window (look at the source for the form you're submitting)
then you can use the function below. It will return the document object for the first window it finds with an address "like" the passed URL. It a window is not found it will return nothing. dim oDoc, oTable set oDoc=GetHTMLDocument("htt p://thisistheURL/responsedir/") if not oDoc is nothing then set oTable=oDoc.getElementById("abcTab") end if Tim '################################################# 'Find an IE window with matching location and get the document from ' the loaded page. Assumes no frames. Function GetHTMLDocument(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 'see if IE is already open 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 Exit For End If End If Next o Set GetHTMLDocument = retVal End Function "tmp2100" <u21084@uwe wrote in message news:5f51884e56fca@uwe... I have the following VBA code to launch an IE application, navigate to a URL, and submit a form. The page which pops-up does not have a directly addressable URL, but is generated and directly filled-in by java when the form is submitted. What is the VBA statement needed in the following code to get a handle to the unnamed spawned page so that objects on the page can be referenced ? Sub Clt_Data_Fetch() Dim ie As Object ' launch IE and navigate to URL Set ie = CreateObject("InternetExplorer.Application") ie.Visible = True ie.navigate "http://thisistheURL/index.html" ' wait for login page to come up Do While ie.Busy: DoEvents: Loop Do Until ie.ReadyState < 1: Loop ' fill in login form and submit ie.document.all.Item("name").Value = "loginstring" ie.document.all.Item("passwd").Value = "passwordstring" ie.document.all.Item("submit").Click ' wait for spawned page to come up Do While ie.Busy: DoEvents: Loop Do Until ie.ReadyState < 1: Loop Application.Wait (Now() + TimeValue("0:00:07")) ' close login page, leaving spawned page visible ie.Quit ' *********** what is statement needed here to make reference to a 'table' object with ' ID = "abcTab" in the unnamed spawned page ****************** endSub -- tmp2100 Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
navigating to an unnamed spawned window ?
Tim:
This appears to work. If I monitor the value of oDoc, the correct URL does show up. However, when the set oTable=oDoc.getElementById("abcTab") statement executes, a VB runtime error #438 occurs: "object doesnt support this property or method" Any suggestions on what's wrong? Tim Williams wrote: If you know the URL of the spawned window (look at the source for the form you're submitting) then you can use the function below. It will return the document object for the first window it finds with an address "like" the passed URL. It a window is not found it will return nothing. dim oDoc, oTable set oDoc=GetHTMLDocument("htt p://thisistheURL/responsedir/") if not oDoc is nothing then set oTable=oDoc.getElementById("abcTab") end if Tim '################################################ # 'Find an IE window with matching location and get the document from ' the loaded page. Assumes no frames. Function GetHTMLDocument(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 'see if IE is already open 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 Exit For End If End If Next o Set GetHTMLDocument = retVal End Function I have the following VBA code to launch an IE application, navigate to a URL, and submit a form. [quoted text clipped - 36 lines] endSub -- tmp2100 Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
navigating to an unnamed spawned window ?
Sorry, my mistake in the function.
Set retVal = o should be: Set retVal = o.document Tim -- Tim Williams Palo Alto, CA "tmp2100 via OfficeKB.com" <u21084@uwe wrote in message news:5f5d79c69b1d1@uwe... Tim: This appears to work. If I monitor the value of oDoc, the correct URL does show up. However, when the set oTable=oDoc.getElementById("abcTab") statement executes, a VB runtime error #438 occurs: "object doesnt support this property or method" Any suggestions on what's wrong? Tim Williams wrote: If you know the URL of the spawned window (look at the source for the form you're submitting) then you can use the function below. It will return the document object for the first window it finds with an address "like" the passed URL. It a window is not found it will return nothing. dim oDoc, oTable set oDoc=GetHTMLDocument("htt p://thisistheURL/responsedir/") if not oDoc is nothing then set oTable=oDoc.getElementById("abcTab") end if Tim '################################################ # 'Find an IE window with matching location and get the document from ' the loaded page. Assumes no frames. Function GetHTMLDocument(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 'see if IE is already open 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 Exit For End If End If Next o Set GetHTMLDocument = retVal End Function I have the following VBA code to launch an IE application, navigate to a URL, and submit a form. [quoted text clipped - 36 lines] endSub -- tmp2100 Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
navigating to an unnamed spawned window ?
Tim:
I also found that changing the statement: set oTable=oDoc.getElementById("abcTab") to: set oTable=oDoc.document.getElementById("abcTab") also works as an alternative to your fix. Now that oTable is set to the correct element, what statement(s) are needed to cause the contents of oTable to be pasted back to the Excel spreadsheet from which this macro is being run ? ================================================== ================ Tim Williams wrote: Sorry, my mistake in the function. Set retVal = o should be: Set retVal = o.document Tim Tim: This appears to work. If I monitor the value of oDoc, the [quoted text clipped - 55 lines] endSub -- tmp2100 Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200604/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
navigating to an unnamed spawned window ?
The document object model doesn't psermit a copy/paste operation, but you can access the individual rows/cells and get the content
from selected ones... Eg: Msgbox oTable.rows(2).cells(2).innerHTML Tim -- Tim Williams Palo Alto, CA "tmp2100 via OfficeKB.com" <u21084@uwe wrote in message news:5f696cb675e15@uwe... Tim: I also found that changing the statement: set oTable=oDoc.getElementById("abcTab") to: set oTable=oDoc.document.getElementById("abcTab") also works as an alternative to your fix. Now that oTable is set to the correct element, what statement(s) are needed to cause the contents of oTable to be pasted back to the Excel spreadsheet from which this macro is being run ? ================================================== ================ Tim Williams wrote: Sorry, my mistake in the function. Set retVal = o should be: Set retVal = o.document Tim Tim: This appears to work. If I monitor the value of oDoc, the [quoted text clipped - 55 lines] endSub -- tmp2100 Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200604/1 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
navigating to an unnamed spawned window ?
Tim:
I presume that to pull in a big table, all I need is to do is, for example, size the rows and cells counts large enough to match or exceed the size of the target table ? for example, Msgbox oTable.rows(10000).cells(100000).innerHTML In your example, what is the subsequent statement needed to pull the complete Msgbox content into the spreadsheet from which the macro is being run ? ================================================== ============================= Tim Williams wrote: The document object model doesn't psermit a copy/paste operation, but you can access the individual rows/cells and get the content from selected ones... Eg: Msgbox oTable.rows(2).cells(2).innerHTML Tim Tim: I also found that changing the statement: [quoted text clipped - 23 lines] endSub -- tmp2100 Message posted via http://www.officekb.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
navigating to an unnamed spawned window ?
rows(x) and cells(y) are indexes, not counts.
Rows have cells, so your example only pulls the content from the 100000th cell on the 10000th row. Is your table really this large ? You could try reading in the entire table using oTable.innerText and see what you get. Otherwise iterate through all the cells copying each value in turn. -- Tim Williams Palo Alto, CA "tmp2100 via OfficeKB.com" <u21084@uwe wrote in message news:5f6d118e84103@uwe... Tim: I presume that to pull in a big table, all I need is to do is, for example, size the rows and cells counts large enough to match or exceed the size of the target table ? for example, Msgbox oTable.rows(10000).cells(100000).innerHTML In your example, what is the subsequent statement needed to pull the complete Msgbox content into the spreadsheet from which the macro is being run ? ================================================== ============================= Tim Williams wrote: The document object model doesn't psermit a copy/paste operation, but you can access the individual rows/cells and get the content from selected ones... Eg: Msgbox oTable.rows(2).cells(2).innerHTML Tim Tim: I also found that changing the statement: [quoted text clipped - 23 lines] endSub -- tmp2100 Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unnamed Macro Error | Excel Worksheet Functions | |||
Removing unnamed Legend lines from an Excel template | Charts and Charting in Excel | |||
The window opens in a smaller window not full sized window. | Excel Discussion (Misc queries) | |||
Two Excel sessions spawned at startup | Excel Discussion (Misc queries) | |||
Finding unnamed cells in a range | Excel Discussion (Misc queries) |