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
__________________________

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" wrote in
message ...
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,

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"
wrote:

RBHere is one way of reading the body text of a web page, you can
pass the
RBwebtx string looking for the values and related text as required.
You need
RBto make a reference to Microsoft Office Object Library.
RB
RBPrivate sURL As String, webtx As string
RBPrivate ie As Object
RBSub 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
RBEnd Sub
RB
RBCheers
RBNigel
RB
RB"Richard Buttrey" wrote
in
RBmessage ...
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
__________________________

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


"Richard Buttrey" wrote in
message ...
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"
wrote:

RBHere is one way of reading the body text of a web page, you can
pass the
RBwebtx string looking for the values and related text as required.
You need
RBto make a reference to Microsoft Office Object Library.
RB
RBPrivate sURL As String, webtx As string
RBPrivate ie As Object
RBSub 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
RBEnd Sub
RB
RBCheers
RBNigel
RB
RB"Richard Buttrey" wrote
in
RBmessage ...
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
__________________________