jhize
12-01-2006, 08:12 AM
Hi all,
First off, I am in serious need of a solution and I posted on several forums to find that solution. I will post those links in a followup. They can be disregarded.
What follows is a thorough explanation of what I am looking for.
I have included an example workbook to illustrate the way I need this web data formatted after “import”.
I have eliminated the possibility of using a querytables.add as it opens a new instance of IE. I have eliminated the use of using an innertext statement as in CASE 3, it combines the data into a single string and cannot be separated via parsing (due to employee names, titles, and addresses differing in length (Example: with innertext I would get Smith, John RJSMIT1+1…R is his middle initial, JSMITH is his ID) . Get External Data is not an option as I am attempting to do this through VBA.
Using InnerHTML statement returns exactly what I need, but all lumped into a single cell. I need the HTML tags removed and each element in its own cell.
Here is the code in its infancy stage:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim IeApp As InternetExplorer
Dim sURL As String
Dim IeDoc As Object
Dim I As Long
Dim nm As String
Dim searchperson As Variant
searchperson = Sheets("sheet2").Range("c10").Value
Set IeApp = New InternetExplorer
IeApp.Visible = True 'will be false
sURL = "********" 'url taken out for security purposes
IeApp.navigate sURL
Do
Loop Until IeApp.readyState = READYSTATE_COMPLETE
Set IeDoc = IeApp.document
IeDoc.all.f_simple.Value = (Sheets("sheet2").Range("c10").Value)
IeDoc.all.submit.Click
' Now auto navigating to search return page -------------/////
Application.Wait (Now + TimeValue("0:00:02"))
Do
Loop Until IeApp.readyState = READYSTATE_COMPLETE
' search results ------------------------////
' This works but lumps everything into "A1" with html tags
Set mytable = IeApp.document.getElementById("maindetail")
Sheets("sheet1").Range("a1").Value = IeApp.document.getElementById("maindetail").innerHTML
'Sheets("sheet1").Range("a1").Value = IeApp.document.getElementById("list").innerText
' This section is designed to breakapart the innerhtml and place each element into its own cell
' I have not been able to get it to work
Dim d As Object
Set d = IeApp.document
Dim e As Object ' the elements of the document
Dim t As Object ' the table required
Dim r As Object ' the rows of the table
Dim c As Object ' the cells of the rows.
'Dim I As Long - duplicate declaration from above
Dim J As Long
Sheets("sheet1").Activate
For Each e In d.all
If e.nodeName = "maindetail" Then
J = J + 1
End If
If J = n Then
Set t = e
tabno = tabno + 1
nextrow = nextrow + 1
Set rng = Range("A" & nextrow)
' This where the Code goes into break mode
For Each r In t.Rows
For Each c In r.Cells
rng.Value = c.innerText
Set rng = rng.Offset(, 1)
I = I + 1
Next c
nextrow = nextrow + 1
Set rng = rng.Offset(1, -I)
I = 0
Next r
Exit For
End If
Next e
End Sub
Additionally, the relevant source code from the target website is provided. I am not sure what value can be captured to determine CASE SELECT.
Case 1
No Matching Entries
Will return a msgbox saying so
<!-- Content Goes Below Here -->
<P class="errormessage">No Results Found<P>
Case 2
Single employee return.
<!—Static Content Above Here>
<!-- Content Goes Below Here -->
<TABLE>
<TR>
<TD>
<TABLE><TR><TD>
<TABLE id="maindetail">
<TR><TH COLSPAN="2">Last, First Ini</TH></TR>
<TR><TD>
<FORM name="detailrecord">
<TABLE>
<TR><TD>Security Officer</TD></TR>
<TR><TD>Security Company</TD></TR>
<TR><TD> Company Address </TD></TR>
<TR><TD> Company City, State, zip </TD></TR>
<TR><TD> United States</TD></TR>
</TABLE>
</FORM>
</TD><TD>
<TABLE> <TR><TD>Office:</TD><TD>+1 123 456 7890</TD></TR>
<TR><TD>Cell:</TD><TD></TD></TR>
<TR><TD>Pager:</TD><TD></TD></TR>
<TR><TD>Fax:</TD><TD></TD></TR>
</TABLE>
</TD></TR>
</TABLE>
AND
<TABLE id="misc"> <TR>
<TD>
<TABLE>
<TR><TD>CUID:</TD><TD>FirstInitialLastname</TD></TR>
<TR><TD>Employee Type:</TD><TD>Contractor</TD></TR>
<TR><TD>Employee Number:</TD><TD>503595c</TD></TR>
<TR><TD>M-Net ID:</TD><TD>503595</TD></TR>
<TR><TD>RC:</TD><TD>I04920000</TD></TR>
<TR><TD><A HREF="/emd/faq.html#7" TARGET="_blank">Role</A>:</TD><TD>QC-Service & Support</TD></TR>
</TABLE>
AND
<FORM name="mgmt">
<TABLE class="mgmt">
<TR><TH colspan=3>MY COMPANY Management Chain</TH></TR>
<TR><TD><IMG SRC="/emd/images/bul_YELLOW.gif" WIDTH="12" HEIGHT="12" TITLE="YELLOW"> <A HREF="/emd/search.html?f_mnetid=419285">CEO Name</A> </TD><TD class="mail_checkbox"><INPUT type="checkbox" name="mgmt" value="CEO.guy@myCompany.com"</TD<TD class="mail_checkbox">
This continues with several more managers – all I need is the last one (Direct Supervisor)
<INPUT type="checkbox" name="mgmt" value="Direct.Supervisor@MyCompany.com"</TD><TD class="side_phone">+1 123 456 7890</TD></TR>
</TABLE>
</FORM>
Case 3
In the event that several employees have the same name, the search returns page returns those multiple listings. The idea here is to return a msgbox with the listing so that the user can select one of them. The CUID is unique and will be used to return a CASE 2 search result.
<!—Static Content Above Here>
<!-- Content Goes Below Here -->
<TABLE><TR>
<TD>Search found 4 entries.</TD> ?-------Need to capture the number
<TD> </TD>
<TD><DIV id="dwim">
<form name="simplesearch" method="POST" action="/emd/search.html">
<input type="text" name="f_simple" value="" size="20" maxlength="40" accesskey="S">
<input type="SUBMIT" value="Simple Search" name="Submit">
<SPAN CLASS="smalltext"><A HREF="/emd/index.html?searchtype=advanced" accesskey="Z">Advanced Search</A></SPAN>
</form>
</DIV>
</TD>
</TR></TABLE>
<TABLE id="list">
<TR>
<TH>Name</TH>
<TH>CUID</TH>
<TH>Work Phone</TH>
<TH>E-mail</TH>
<TH>City, State</TH>
<TH>Employee Type</TH>
</TR>
<TR class="EVEN">
<TD><A HREF="/emd/search.html?f_mnetid=075306">Smith, John C</A></TD>
<TD>JCSMIT1</TD>
<TD>+1 555 1212 1234</TD>
<TD><A HREF="mailto:John.Smith4@mycompany.com">John.Smith4@mycompany.com</A></TD>
<TD>Denver, CO</TD>
<TD>Occupational</TD>
</TR>
<TR class="ODD">
<TD><A HREF="/emd/search.html?f_mnetid=413149">Smith, John G</A></TD>
<TD>JGSMIT2</TD>
<TD>+1 555 1212 1235</TD>
<TD><A HREF="mailto:John.Smith@ mycompany.com ">John.Smith@ mycompany.com </A></TD>
<TD>Denver, CO</TD>
<TD>Management</TD>
</TR>
<TR class="EVEN">
<TD><A HREF="/emd/search.html?f_mnetid=456829">Smith, John P</A></TD>
<TD>JXSMIT7</TD>
<TD>+1 555 1212 1236</TD>
<TD><A HREF="mailto:John.Smith5@ mycompany.com ">John.Smith5@ mycompany.com </A></TD>
<TD>Provo, UT</TD>
<TD>Occupational</TD>
</TR>
<TR class="ODD">
<TD><A HREF="/emd/search.html?f_mnetid=515786">Smith, John W</A></TD>
<TD>JWSMIT2</TD>
<TD>+1 555 1212 1235</TD>
<TD><A HREF="mailto:John.Smith2@ mycompany.com ">John.Smith2@ mycompany.com </A></TD>
<TD>Glendale, AZ</TD>
<TD>Contractor</TD>
</TR>
</TABLE>
I will post example.xls momentarily.
First off, I am in serious need of a solution and I posted on several forums to find that solution. I will post those links in a followup. They can be disregarded.
What follows is a thorough explanation of what I am looking for.
I have included an example workbook to illustrate the way I need this web data formatted after “import”.
I have eliminated the possibility of using a querytables.add as it opens a new instance of IE. I have eliminated the use of using an innertext statement as in CASE 3, it combines the data into a single string and cannot be separated via parsing (due to employee names, titles, and addresses differing in length (Example: with innertext I would get Smith, John RJSMIT1+1…R is his middle initial, JSMITH is his ID) . Get External Data is not an option as I am attempting to do this through VBA.
Using InnerHTML statement returns exactly what I need, but all lumped into a single cell. I need the HTML tags removed and each element in its own cell.
Here is the code in its infancy stage:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim IeApp As InternetExplorer
Dim sURL As String
Dim IeDoc As Object
Dim I As Long
Dim nm As String
Dim searchperson As Variant
searchperson = Sheets("sheet2").Range("c10").Value
Set IeApp = New InternetExplorer
IeApp.Visible = True 'will be false
sURL = "********" 'url taken out for security purposes
IeApp.navigate sURL
Do
Loop Until IeApp.readyState = READYSTATE_COMPLETE
Set IeDoc = IeApp.document
IeDoc.all.f_simple.Value = (Sheets("sheet2").Range("c10").Value)
IeDoc.all.submit.Click
' Now auto navigating to search return page -------------/////
Application.Wait (Now + TimeValue("0:00:02"))
Do
Loop Until IeApp.readyState = READYSTATE_COMPLETE
' search results ------------------------////
' This works but lumps everything into "A1" with html tags
Set mytable = IeApp.document.getElementById("maindetail")
Sheets("sheet1").Range("a1").Value = IeApp.document.getElementById("maindetail").innerHTML
'Sheets("sheet1").Range("a1").Value = IeApp.document.getElementById("list").innerText
' This section is designed to breakapart the innerhtml and place each element into its own cell
' I have not been able to get it to work
Dim d As Object
Set d = IeApp.document
Dim e As Object ' the elements of the document
Dim t As Object ' the table required
Dim r As Object ' the rows of the table
Dim c As Object ' the cells of the rows.
'Dim I As Long - duplicate declaration from above
Dim J As Long
Sheets("sheet1").Activate
For Each e In d.all
If e.nodeName = "maindetail" Then
J = J + 1
End If
If J = n Then
Set t = e
tabno = tabno + 1
nextrow = nextrow + 1
Set rng = Range("A" & nextrow)
' This where the Code goes into break mode
For Each r In t.Rows
For Each c In r.Cells
rng.Value = c.innerText
Set rng = rng.Offset(, 1)
I = I + 1
Next c
nextrow = nextrow + 1
Set rng = rng.Offset(1, -I)
I = 0
Next r
Exit For
End If
Next e
End Sub
Additionally, the relevant source code from the target website is provided. I am not sure what value can be captured to determine CASE SELECT.
Case 1
No Matching Entries
Will return a msgbox saying so
<!-- Content Goes Below Here -->
<P class="errormessage">No Results Found<P>
Case 2
Single employee return.
<!—Static Content Above Here>
<!-- Content Goes Below Here -->
<TABLE>
<TR>
<TD>
<TABLE><TR><TD>
<TABLE id="maindetail">
<TR><TH COLSPAN="2">Last, First Ini</TH></TR>
<TR><TD>
<FORM name="detailrecord">
<TABLE>
<TR><TD>Security Officer</TD></TR>
<TR><TD>Security Company</TD></TR>
<TR><TD> Company Address </TD></TR>
<TR><TD> Company City, State, zip </TD></TR>
<TR><TD> United States</TD></TR>
</TABLE>
</FORM>
</TD><TD>
<TABLE> <TR><TD>Office:</TD><TD>+1 123 456 7890</TD></TR>
<TR><TD>Cell:</TD><TD></TD></TR>
<TR><TD>Pager:</TD><TD></TD></TR>
<TR><TD>Fax:</TD><TD></TD></TR>
</TABLE>
</TD></TR>
</TABLE>
AND
<TABLE id="misc"> <TR>
<TD>
<TABLE>
<TR><TD>CUID:</TD><TD>FirstInitialLastname</TD></TR>
<TR><TD>Employee Type:</TD><TD>Contractor</TD></TR>
<TR><TD>Employee Number:</TD><TD>503595c</TD></TR>
<TR><TD>M-Net ID:</TD><TD>503595</TD></TR>
<TR><TD>RC:</TD><TD>I04920000</TD></TR>
<TR><TD><A HREF="/emd/faq.html#7" TARGET="_blank">Role</A>:</TD><TD>QC-Service & Support</TD></TR>
</TABLE>
AND
<FORM name="mgmt">
<TABLE class="mgmt">
<TR><TH colspan=3>MY COMPANY Management Chain</TH></TR>
<TR><TD><IMG SRC="/emd/images/bul_YELLOW.gif" WIDTH="12" HEIGHT="12" TITLE="YELLOW"> <A HREF="/emd/search.html?f_mnetid=419285">CEO Name</A> </TD><TD class="mail_checkbox"><INPUT type="checkbox" name="mgmt" value="CEO.guy@myCompany.com"</TD<TD class="mail_checkbox">
This continues with several more managers – all I need is the last one (Direct Supervisor)
<INPUT type="checkbox" name="mgmt" value="Direct.Supervisor@MyCompany.com"</TD><TD class="side_phone">+1 123 456 7890</TD></TR>
</TABLE>
</FORM>
Case 3
In the event that several employees have the same name, the search returns page returns those multiple listings. The idea here is to return a msgbox with the listing so that the user can select one of them. The CUID is unique and will be used to return a CASE 2 search result.
<!—Static Content Above Here>
<!-- Content Goes Below Here -->
<TABLE><TR>
<TD>Search found 4 entries.</TD> ?-------Need to capture the number
<TD> </TD>
<TD><DIV id="dwim">
<form name="simplesearch" method="POST" action="/emd/search.html">
<input type="text" name="f_simple" value="" size="20" maxlength="40" accesskey="S">
<input type="SUBMIT" value="Simple Search" name="Submit">
<SPAN CLASS="smalltext"><A HREF="/emd/index.html?searchtype=advanced" accesskey="Z">Advanced Search</A></SPAN>
</form>
</DIV>
</TD>
</TR></TABLE>
<TABLE id="list">
<TR>
<TH>Name</TH>
<TH>CUID</TH>
<TH>Work Phone</TH>
<TH>E-mail</TH>
<TH>City, State</TH>
<TH>Employee Type</TH>
</TR>
<TR class="EVEN">
<TD><A HREF="/emd/search.html?f_mnetid=075306">Smith, John C</A></TD>
<TD>JCSMIT1</TD>
<TD>+1 555 1212 1234</TD>
<TD><A HREF="mailto:John.Smith4@mycompany.com">John.Smith4@mycompany.com</A></TD>
<TD>Denver, CO</TD>
<TD>Occupational</TD>
</TR>
<TR class="ODD">
<TD><A HREF="/emd/search.html?f_mnetid=413149">Smith, John G</A></TD>
<TD>JGSMIT2</TD>
<TD>+1 555 1212 1235</TD>
<TD><A HREF="mailto:John.Smith@ mycompany.com ">John.Smith@ mycompany.com </A></TD>
<TD>Denver, CO</TD>
<TD>Management</TD>
</TR>
<TR class="EVEN">
<TD><A HREF="/emd/search.html?f_mnetid=456829">Smith, John P</A></TD>
<TD>JXSMIT7</TD>
<TD>+1 555 1212 1236</TD>
<TD><A HREF="mailto:John.Smith5@ mycompany.com ">John.Smith5@ mycompany.com </A></TD>
<TD>Provo, UT</TD>
<TD>Occupational</TD>
</TR>
<TR class="ODD">
<TD><A HREF="/emd/search.html?f_mnetid=515786">Smith, John W</A></TD>
<TD>JWSMIT2</TD>
<TD>+1 555 1212 1235</TD>
<TD><A HREF="mailto:John.Smith2@ mycompany.com ">John.Smith2@ mycompany.com </A></TD>
<TD>Glendale, AZ</TD>
<TD>Contractor</TD>
</TR>
</TABLE>
I will post example.xls momentarily.