PDA

View Full Version : Sleeper: Searching Web Data



BexleyManor
11-22-2004, 04:50 AM
Hi all,

Having done most of the hard work on this one, I'm having a mental block as to the final solution??!! :dunno

From the code you will see that I can pick out individual data from the webpage, however I would like the code to pick out all instances or the requested data.



Private Sub cmdGETproxy_Click()
Dim Wkb As Workbook, Rng As Range
Set Wkb = Workbooks.Open("http://aliveproxy.com/")
Set Rng = Wkb.Worksheets(1).Cells.Find("High Anonymity")
MsgBox "High Anonymity: " & Rng.Offset(0, -2).Value & Chr(10) & "SSL/HTTPS: " & Rng.Offset(0, 1).Value & Chr(10) & "Host Country: " & Rng.Offset(0, -1).Value
Wkb.Close
end sub


Any suggestions kind folks?? :hi:

mvidas
11-22-2004, 09:19 AM
Hi Bexley,

Try the following. It sets another range variable to the first instance of "High Anonymity", then loops through all other cells that contain that until it reaches the first again. Let me know if you have any questions!

Matt


Private Sub cmdGETproxy_Click()
Dim Wkb As Workbook, Rng As Range, Rng1 As Range
Set Wkb = Workbooks.Open("http://aliveproxy.com/")
Set Rng = Wkb.Worksheets(1).Cells.Find("High Anonymity")
If Not Rng Is Nothing Then
Set Rng1 = Rng
Msgbox "High Anonymity: " & Rng.Offset(0, -2).Value & Chr(10) & "SSL/HTTPS: " & Rng.Offset(0, 1).Value & Chr(10) & "Host Country: " & Rng.Offset(0, -1).Value
Set Rng = Wkb.Worksheets(1).Cells.FindNext(Rng)
Do Until Rng1.Address = Rng.Address
Msgbox "High Anonymity: " & Rng.Offset(0, -2).Value & Chr(10) & "SSL/HTTPS: " & Rng.Offset(0, 1).Value & Chr(10) & "Host Country: " & Rng.Offset(0, -1).Value
Set Rng = Wkb.Worksheets(1).Cells.FindNext(Rng)
Loop
Else
Msgbox "None found."
End If
Wkb.Close
End Sub

Matt

BexleyManor
11-23-2004, 05:26 AM
Hi Matt,

Many thanks for the input, much appreciated.

All seems to work ok until it gets to the end of the loop then I get an application error.

Is there any way I can have the code read the column of data top to bottom then exit when it reaches the end??

Thanks.

mvidas
11-23-2004, 09:04 AM
Hi Matt,
Many thanks for the input, much appreciated.
All seems to work ok until it gets to the end of the loop then I get an application error.
Hmm, not sure why you'd get an application error, what line is it erroring at?


Is there any way I can have the code read the column of data top to bottom then exit when it reaches the end??
To do that you'd have to loop through each cell, which would take a longer time. Thats why I have it the way it does, so it compares the found cell to the first found cell (and when they match, the region has been completely searched). Try this variation, it may prove more useful in reality for you. It takes all findings and adds them to a range variable 'Found', and then loops through all the findings at the end. This way if you're going to do something with them other than msgbox, it may be a little easier for you. Also you can specify the column to search through. Here's the revision:


Private Sub cmdGETproxy_Click()
Dim Wkb As Workbook, Rng As Range, Col As Range, Found As Range
Set Wkb = Workbooks.Open("http://aliveproxy.com/")
Set Col = Wkb.Sheets(1).Columns("C") 'Column to search through
Set Col = Intersect(Col, Col.Parent.UsedRange) 'trim column to used area
Set Rng = Col.Find("High Anonymity", Col.Cells(Col.Cells.Count))
If Not Rng Is Nothing Then
Set Found = Rng
Set Rng = Col.FindNext(Rng)
Do Until Rng.Address = Found.Cells(1).Address
Set Found = Union(Found, Rng)
Set Rng = Col.FindNext(Rng)
Loop
Else
Msgbox "None found."
End If
For Each Rng In Found.Cells
Msgbox "High Anonymity: " & Rng.Offset(0, -2).Value & Chr(10) & "SSL/HTTPS: " & Rng.Offset(0, 1).Value & Chr(10) & "Host Country: " & Rng.Offset(0, -1).Value
Next Rng
Wkb.Close
End Sub

Of course let me know if its still not behaving
Matt