Passing IP address from Excel to a Web site Locator

[Richard Buttrey]

I have an Excel spreadsheet which collects the IP address logs from my
broadband router and analyses them in a pivot table. (Number of
occurrences and port attacked)

I'd like to develop the spreadsheet a bit further, by passing each IP
address in turn to one of the many IP address locator web sites, and
read back the results to the spreadsheet.

Is this possible, and can anyone suggest the sort of VBA code I might
need to tackle this?

Many thanks,




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Nigel]

Here is one way of reading the body text of a web page, you can pass the
webtx string looking for the values and related text as required. You need
to make a reference to Microsoft Office Object Library.

Private sURL As String, webtx As string
Private ie As Object
Sub WebLink()
Set ie = CreateObject("InternetExplorer.Application")
sURL = "http://www.xyz..com"
ie.Navigate sURL
'wait for response
Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop
' get html page body text
webtx = ie.Document.body.innertext
ie.Visible = True
'close ie and remove memory references
ie.Quit
Set ie = Nothing
End Sub

Cheers
Nigel

 

[Richard Buttrey]

Nigel,

Many thanks for this. I've just been experimenting with it.
It seems to return text from the site which handles the IP lookup,
(e.g. http://www.geobytes.com/IpLocator.htm )

but I can't see how to pass a particular IP address to the 'locate'
box, and then how the VBA code would 'press' the submit button.

Are you able to offer any further advice on what additional code I
might need.

Kind regards,

Richard


On Sun, 15 Aug 2004 06:50:12 +0100, "Nigel"

RB>Here is one way of reading the body text of a web page, you can
pass the
RB>webtx string looking for the values and related text as required.
You need
RB>to make a reference to Microsoft Office Object Library.
RB>
RB>Private sURL As String, webtx As string
RB>Private ie As Object
RB>Sub WebLink()
RB> Set ie = CreateObject("InternetExplorer.Application")
RB> sURL = "http://www.xyz..com"
RB> ie.Navigate sURL
RB> 'wait for response
RB> Do Until Not ie.Busy And ie.ReadyState = 4
RB> DoEvents
RB> Loop
RB> ' get html page body text
RB> webtx = ie.Document.body.innertext
RB> ie.Visible = True
RB> 'close ie and remove memory references
RB> ie.Quit
RB> Set ie = Nothing
RB>End Sub
RB>
RB>Cheers
RB>Nigel
RB>
in
RB>message RB>> I have an Excel spreadsheet which collects the IP address logs
from my
RB>> broadband router and analyses them in a pivot table. (Number of
RB>> occurrences and port attacked)
RB>>
RB>> I'd like to develop the spreadsheet a bit further, by passing
each IP
RB>> address in turn to one of the many IP address locator web sites,
and
RB>> read back the results to the spreadsheet.
RB>>
RB>> Is this possible, and can anyone suggest the sort of VBA code I
might
RB>> need to tackle this?
RB>>
RB>> Many thanks,
RB>>
RB>>
RB>>
RB>>
RB>> __
RB>> Richard Buttrey
RB>> Grappenhall, Cheshire, UK
RB>> __________________________
RB>

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Nigel]

Hi Richard,
I see, the code I use reutrns the text as you say. However looking at the
site you cite, you might be able to pass the parameters in the URL to the
receiving script?
I also see there are some hidden form fields that may also need to be sent
along with the URL. I guess to get it right you may need to contact the
webmaster at the site for their assistance.
Assuming you get this far you then need to parse the text file looking for
the text items you require. The good news is that the data returned to the
html page is presented in a form, searching this for the required field
names and their value would retrun what you need.

Cheers
Nigel