Web Query

[Vikram Dhemare]

Hi,
I use to get the web data on button click with the codes provided by Dave.
No i would like to modify the codes as per users request. If the innertext
value of 4th column of HTML table is already available in the extracted sheet
then code should skip the row & go for next row of html table something like
if ......... then exit for else. For ready reference some of the codes
provinding here
For Each mRow In mTable.Rows
iCol = 0
For Each mCell In mRow.Cells
rBase.Offset(iRow, iCol).Value = mCell.innerText
iCol = iCol + 1
Next
iRow = iRow + 1
End If
Next

If mCell.innertext value already available then exit for

 

[joel]

Indexing on the webpage starts at 0 so the 4th column is index 3


For Each mRow In mTable.Rows
if mRow.cells(3) <> rBase.Offset(iRow, 4).Value then
iCol = 0
For Each mCell In mRow.Cells
rBase.Offset(iRow, iCol).Value = mCell.innerText
iCol = iCol + 1
Next
iRow = iRow + 1
End If
Next mRow

 

[Vikram Dhemare]

Thanks Sir,
But I am looking for the answer which can find the HTML Cells(1).innertext
of each row, in a worksheet, if found then go for next HTML row if does not
found then copy the value & paste in that sheet at last filled row.
for Example:
With Wks
iRow = .Range("A" & Rows.Count).End(xlUp).Row
End With
End If
For Each mRow In mTable.Rows
With Wks
Set rngC = Columns(1).Find(What:=mRow.Cells(1).innerText, _
LookAt:=xlPart, LookIn:=xlValues)
End With
If Not rngC Is Nothing Then
Exit For
Else
iCol = 0
For Each mCell In mRow.Cells
rBase.Offset(iRow, iCol).Value = mCell.innerText
iCol = iCol + 1
End If
Next
iRow = iRow + 1
End If
Next mRow

Any help will be highly appreciable.

 

[joel]

I ould reverse the procesess you are using. span the webpage then us
find to locate the row and column of the worksheet. Something lik
this


For Each mRow In mTable.Rows
ColCount = 0
For Each mCol In mRow
if ColCount = 0 then
RowHeader = mCol
Else
ColHeader = mTable.Rows(0).cells(ColCount)
With Wks
Set rngC = .Columns(1).Find(What:=RowHeader, _
LookAt:=xlPart, LookIn:=xlValues)
if not rngC is nothing then
Set rngR = .Rows(1).Find(What:=ColHeader, _
LookAt:=xlPart, LookIn:=xlValues)
.Cells(rngC.Row,rngR.column) = mCol.innertext
end if
End With
End if
ColCount = ColCount + 1
next mCol
next mRow

 

[Vicky]

Not getting the desired report., Values returns blanks.

 

[joel]

Without having access to the webpage and the complete code I can'
dubug the problem for you. I added some message boxes to the cod
below. I think your problem may be the row and column headers on th
worksheet don't exactly match the ones on the webpage. You may hav
extra spaces, other white characters in the headers ( webpage o
worksheet), or some of the characters may be capitalized. Sometime
adding a TRIM or forcing all the the to UPPERCASE using the functio
UCASE solves these problems.




VBA Code:
--------------------


For Each mRow In mTable.Rows
ColCount = 0
For Each mCol In mRow
if ColCount = 0 then
RowHeader = mCol
Else
ColHeader = mTable.Rows(0).cells(ColCount)
With Wks
Set rngC = .Columns(1).Find(What:=RowHeader, _
LookAt:=xlPart, LookIn:=xlValues)
if not rngC is nothing then
Set rngR = .Rows(1).Find(What:=ColHeader, _
LookAt:=xlPart, LookIn:=xlValues)
if rngR is nothing then
msgbox("Row Header : " & RowHeader & vbcrlf & _
"Column Header : " & ColHeader)
else
.Cells(rngC.Row,rngR.column) = mCol.innertext
end if
else
msgbox("Cannot find row Header : " & RowHeader)
end if
End With
End if
ColCount = ColCount + 1
next mCol
next mRow

--------------------