Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HTMLElementCollection Dilemna
I have pulled all my hair out. I've never had a problem automating IE until now. This is the code I have which fills out a form on my company's intranet, submits the values, then searches for the named table, selects it, and pulls the data I need. The problem I have is this particular page has two tables that have the same name and ID. I need to select the second table of the two. I have tried creating a HTMLElementCollection of the document and looping through the items until I reach the table I need but keep getting a type mismatch error when trying to set my collection = mypageHTML.all. If anyone has a better method I'd appreciate the input or some clarification on how to populate the ElementCollection. Best Regards, Mjack Application.ScreenUpdating = False Set ie = CreateObject("InternetExplorer.Application") ie.Visible = True ie.navigate "http://blahblah/cgi-bin/blahblah_blahblah.pl" Do Until ie.readyState = READYSTATE_COMPLETE: DoEvents: Loop With ie.document.forms("F001") ..project_id_pulldown.Value = 646 ..xl_or_html.Value = "HTML" ..output_format.Value = "ALL" ..submit End With Do Until ie.readyState = READYSTATE_COMPLETE: DoEvents: Loop Set myPageHtml = ie.document Set elemColl = myPageHtml.all Set tr = myPageHtml.body.createTextRange 'Set tbl = myPageHtml.getElementById("<tablename") 'tr.moveToElementText (tbl) tr.Select tr.execCommand ("copy") -- mjack003 ------------------------------------------------------------------------ mjack003's Profile: http://www.excelforum.com/member.php...fo&userid=5141 View this thread: http://www.excelforum.com/showthread...hreadid=561191 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HTMLElementCollection Dilemna
NM solved my own problem. For those of you that have a similar problem or need help automating IE. This was my solution. Figured out how to generate a collection of both tables with the same name. Then select the second one by using the index within the collection. Sub NatothInfo() Dim ie As InternetExplorer Dim myPageHtml As HTMLDocument Dim crap Dim wkData As Worksheet Dim elemColl As IHTMLElementCollection Dim tr Dim tbl Dim lastrow As Long Set wkData = Worksheets("Data") Application.ScreenUpdating = False Set ie = CreateObject("InternetExplorer.Application") ie.Visible = True ie.navigate "http://blah/cgi-bin/blahblah.pl" Do Until ie.readyState = READYSTATE_COMPLETE: DoEvents: Loop With ie.document.forms("F001") ..project_id_pulldown.Value = <value ..xl_or_html.Value = "HTML" ..output_format.Value = "ALL" ..submit End With With ie Do While .Busy: DoEvents: Loop ' Loop until page is loaded Do While .readyState < 4: DoEvents: Loop End With Set myPageHtml = ie.document Set elemColl = myPageHtml.getElementsByName("<tablename") Set tr = myPageHtml.body.createTextRange Set tbl = elemColl.Item(1) tr.moveToElementText (tbl) tr.Select tr.execCommand ("copy") ie.Quit Set ie = Nothing With ActiveWorkbook ..Worksheets.Add End With ActiveSheet.Name = "Junk" ActiveSheet.PasteSpecial Format:="HTML" Selection.Copy wkData.Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End Sub -- mjack003 ------------------------------------------------------------------------ mjack003's Profile: http://www.excelforum.com/member.php...fo&userid=5141 View this thread: http://www.excelforum.com/showthread...hreadid=561191 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Dilemna | Excel Discussion (Misc queries) | |||
A Sorting Dilemna | Excel Discussion (Misc queries) | |||
Date Time Stamp Dilemna | Excel Discussion (Misc queries) | |||
SumProduct/CountIf dilemna + Date Range | Excel Worksheet Functions |