Number of Rows in Html Table

[ancestetta]

Hello every body,

I want to read out the number of ros of an html table.
table.Rows.Count doesn't work!

Is there any special expression to do that??

Thank you very much!

 

[PHV]

What is your actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ancestetta]

Sub ExtractDataFromHtml()



Dim objExcel As Worksheet
Dim objIE As SHDocVw.InternetExplorer

Dim varTables, varTable
Dim varRows, varRow
Dim varCells, varCell
Dim lngRow As Integer
Dim lngColumn As Integer
Dim strBuffer
Dim VINS
Dim URL As String
Dim count As Integer
Dim RowIndex As Integer
Dim check As String
Dim FromDate As String
Dim ToDate As String
Dim FromTime As String
Dim Totime As String
Dim Hall As String
Dim Vin As String
Dim UrlPart1, UrlPart2 As String
Dim VinCol, CheckPtCol, TypeCol, CarrierCol, TimeCol, StatusCol As Integer

VinCol = 1 'first column: VIN
CheckPtCol = 2 'second column: check point
TypeCol = 3 'third column: Type (E70, F25...)
CarrierCol = 4 'fourth column: carrier number
TimeCol = 5 'fifth column: time stamp
StatusCol = 6 'sixth column: status



Set objExcel = ActiveWindow.ActiveSheet
FromDate = Date
ToDate = DateAdd("m", 3, Date)
FromTime = "00:00:10"
Totime = "23:59:10"
Hall = 10
Vin = "LL71528"


UrlPart1 = "

http://wwips75.w10:7763/baa/reports...packageUser=apipso75.w10:IPS_BAAR&bipAll=on&"

UrlPart2 = "&baaSubmit"



'objExcel.Cells(27, 3).Value = myfunction(5, 9)


count = objExcel.UsedRange.Rows.count + 1 'Number of VINS in current sheet
RowIndex = 2 'Index for while loop
Set objIE = CreateObject("InternetExplorer.Application") 'Internet Explorer Object



While RowIndex < count 'start

If Not IsEmpty(objExcel.Cells(RowIndex, 1)) Then

objExcel.Cells(RowIndex, TimeCol).NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"

Vin = Trim(objExcel.Cells(RowIndex, 1).Value)

URL = UrlPart1 & "fromDate=" & FromDate & "&fromTime=" & FromTime & "&toDate=" & ToDate & "&toTime=" & Totime & "&carrier=N/A&carrier_end=N/A&hall=" & Hall & "&vin=" & Vin & UrlPart2
'URL = BuildUrl(FromDate, FromTime, ToDate, Totime, Hall, Vin)


'open url and wait until page is loaded



objIE.Navigate URL

While objIE.Busy
Application.Wait (10)
Wend

While objIE.Document.ReadyState <> "complete"
Application.Wait (10)
Wend



'look for all tables
Set varTables = objIE.Document.All.tags("table")

For Each varTable In varTables


check = varTable.Rows(0).Cells(0).innertext

If check = "VIN " Then

Set varCells = varTable.Rows(1).Cells 'get cells of the second row only

lngColumn = 1 'This will be the output column

For Each varCell In varCells
objExcel.Cells(RowIndex, lngColumn).Value = varCell.innertext
lngColumn = lngColumn + 1
Next

End If

Next



End If

RowIndex = RowIndex + 1 'increment counter

Wend

objIE.Quit

End Sub

 

[ancestetta]

Dditionally to the condition: If Not IsEmpty(objExcel.Cells(RowIndex, 1)) Then
I want to add if objExcel.Rows.Count <> 1 then
i.e. if the number of rows of the table is different from 1.

Thanks

 

[PHV]

What about varTable.Rows.length ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gruuuu]

You'll probably have to Set the table as an iHTMLElement, then use tableVariableName.children.count
This assume there is no <th> tag though.
so you could loop through the children and check the tag name:

Dim tableVar as iHTMLElement
Dim tblChildVar as iHTMLElement
Dim tblRowCount as Integer

For Each tblChildVar in tableVar.Children
    If tblChildVar.tagname = "TR" then
        tblRowCount = tblRowCount +1
    End If
Next tblChildVar

 

[ancestetta]

Thnaks for your help. What shall i do to add the type iHTMLElement?

I got the failure: user-defined type not defined

Thank you

 

[ancestetta]

Got it, I had had to add the MS HTML library.
But tblRowCount turns 0, although the table has 2 rows :-(

 

[ancestetta]

Here is the html code of my html table:

<table cellspacing="0" cellpadding="4" width="100%" border="1" id="data">
<thead style="display: table-header-group;">
   <tr>
      <th width="10%"  align="center"><b> VIN </b></th>
      <th width="10%"  align="center"><b> CURRENT CKPNT </b></th>
      <th width="10%"  align="center"><b> TYPE </b></th>
      <th width="10%"  align="center"><b> CARRIER# </b></th>
      <th width="10%"  align="center"><b> BIP TIME STAMP</b></th>
      <th width="10%"  align="center"><b> CURRENT STATUS </b></th>
   </tr>
</thead>
<tbody>

  
 <tr>
         <td width="10%" align="center">L413957&nbsp</td>
         <td width="10%" align="center">Z6000&nbsp</td>
         <td width="10%" align="center">E-70&nbsp</td>
         <td width="10%" align="center">00091&nbsp</td>
         <td width="10%" align="center">2010-11-03 09:13:43.0&nbsp</td>
         <td width="10%" align="center">OK&nbsp</td>
      </tr>

  
</tbody>
  
</table>

 

[ancestetta]

Thank you PHV, that's exactly what I need!!!
Sorry I overlooked your post.