Anyone out there have a clean Get_External_IP_Address function?

[mdommer1]

I need a function that returns the external ip address as a string for use inside a Microsoft Access application. My efforts to find one thru searching google and bing produce answers that do not work in Access/VBA.

Thanks.

[NeoPa]

As there can be multiple external IP addresses, can you further clarify your requirement. I'm hoping you're not talking about your router address (The external address for your network - IE for a single home PC).

I would guess you would need to look into the Scripting libraries for this.

[mdommer1]

sites like http://www.whatismyip.com/ display my external ip address. It is this address that I am interested in grabbing via vba code in access.

[NeoPa]

Oh dear. The address of your router then. At least your post makes it very clear what you're after.

The problem with this is that this information is neither configured into your machine nor easily (or standardly - sorry for the word invention there) enquire of your router which IP Address it is using on its external port.

It may not be totally impossible (Someone may be able to knock up some code that enquires of the link using an HTML interface or even process the response from one of the networking tools (EG. TraceRt, PathPing, etc) but this is in no way trivial. This is because what you're after is not even related to you own PC, but is an attribute of your router (and of your supplier of course, but that's equally complicated to enquire).

[mdommer1]

I saw an example of obtaining your current external ip address using vb.net but shame on me for not saving the link. If I there was a way to stream the html of a site (that rarely changes) of my choosing into a text file I could pick thru the text file to get my address. In a way I'm happy that a bytes administrator with 15000 posts is perplexed. I'd hate to ask a question that had too obvious an answer. So is there a way to capture the html of a site in VBA?

[NeoPa]

I can understand why it would be hard for you to realise why the question isn't as straightforward as you seem to think it is. As a Networks Manager myself, I often find difficulty explaining even some of the basics of networking to those without the experience. Trust me on this one. It makes less sense than you might assume.

If we were to assume then, for the sake of this question, that you are dealing with a very straightforward, home style network, where there is a single point of connection to the outside world through a single router, then I think the web based approach, where the results of a web page are returned and sifted in your code, may well produce results. You will need to be careful if employing this technique commercially - it will almost certainly not be reliable enough - but for a home-based solution it can work.

There is a library (Microsoft HTML Object Library) that you can use. I have to admit to very little experience in this area myself, although it sounds quite a useful area to get experience in. I'm pretty sure I saw a post in a thread recently which covered this (at least in a basic way). If I find it I'll post a link in here. I'm pretty sure we do have experts who can cover this though (hence the remembered thread).

[NeoPa]

As luck would have it, the other expert participant in that thread just posted and bumped it to the top of my subscribed threads. I hope you find this link (Output html file to text file) helpful.

[Delerna]

So in the code posted on the thread that NeoPa posted.
All you need do, if I understand the question correctly, is

1) Take that code and make a function out of it.
2) call the function and pass the address you supplied to it.
http://www.whatismyip.com/
3) Write a function that opens the text file for reading
4) Find the line (in code) that has
"<H1>Your IP Address Is: ???.???.???.???</H1>"
where ???.???.???.??? is the IP address you are after
5) voila.....I tried it and it works

Clunky...yes...but it works and is easy.


Hope I haven't spelled out the obvious here.


PS
Also, NeoPa is correct, its all very well for a web site to say
This is your IP. Sure, it's an IP relating to the request from you to their web server. But its an IP of what? It might be your router. It might be the address of one of your ISP's routers....it might be ??????

[mdommer1]

The address that http://www.whatismypic.com/ provides is close enough for my needs. I'll try your suggestions and piece together a function. I realize that what lies between any given PC and what the rest of the internet sees as it's IP Address may be a lot of smoke and mirrors. If writing Microsoft Access Database applications wasn't so much fun I'd consider getting really deep with that IP routing stuff. Thanks for your suggestions. There seems to be no shortage of brain power across the pond and down under.

[mdommer1]

NeoPa and Delerna,
Your suggested techniques were a success for me!

Further time on whatismyip.com suggested this site for 'scraping':
http://www.whatismyip.com/automation/n09230945.asp

Thanks again. Do you consider active participation on this site as a valid form of compenstation for your time?

[ADezii]

Here is a simple Routine that I put together that may/may not be of much use to you. It actually extracts IP Addresses from the Output of the IPConfig Command to a Text File via Redirection. The IP Addresses are stored in Procedural Level Variables as you will see:

Dim varRetVal
Dim strLine As String
Dim strDefaultGateway As String
Dim strSubNetMask As String
Dim strIPAddress As String
Dim strDHCPServer As String
Const conPATH_TO_FILE As String = "C:\Test\IPConfig.txt"
 
varRetVal = Shell("cmd.exe /c IPConfig /all > " & conPATH_TO_FILE, vbHide)
 
Open conPATH_TO_FILE For Input As #1
 
Do While Not EOF(1)
  Line Input #1, strLine
    If InStr(strLine, "IP Address") > 1 Then
      strIPAddress = Mid$(strLine, InStr(strLine, ":") + 1)
        Debug.Print "The IP Address of the Local PC is: " & strIPAddress
    ElseIf InStr(strLine, "Subnet Mask") > 1 Then
      strSubNetMask = Mid$(strLine, InStr(strLine, ":") + 1)
        Debug.Print "The IP Address of the Subnet Mask is: " & strSubNetMask
    ElseIf InStr(strLine, "Default Gateway") > 1 Then
      strDefaultGateway = Mid$(strLine, InStr(strLine, ":") + 1)
        Debug.Print "The IP Address of the Default Gateway is: " & strDefaultGateway
    ElseIf InStr(strLine, "DHCP Server") > 1 Then
      strDHCPServer = Mid$(strLine, InStr(strLine, ":") + 1)
        Debug.Print "The IP Address of the DHCP Server is: " & strDHCPServer
    End If
Loop
 
Close #1    ' Close file.

Sample OUTPUT (Stand Alone PC):

The IP Address of the Local PC is:  192.168.1.101
The IP Address of the Subnet Mask is:  255.255.255.0
The IP Address of the Default Gateway is:  192.168.1.1
The IP Address of the DHCP Server is:  192.168.1.1

[Delerna]

@mdommer1
You are very welcome and even more so because you took the time to let us know
Active participation works for me ;)
Who knows what you might say or ask that will turn out to be valuable.

[mdommer1]

Helpers:
Thanks to your suggestions here is the function that will return your current External IP Address:

Function GetExternalIPAddress() As String
On Error GoTo GetExternalIPAddress_Error
 
    Dim iebrowser As InternetExplorer
    Dim dtStartTime As Date
    Dim sDocHTML As String
    Dim strIPAddress As String
 
    'Create a browser object
    Set iebrowser = CreateObject("internetexplorer.application")
    iebrowser.Navigate "http://www.whatismyip.com/automation/n09230945.asp"
 
    'Wait for the page to load
    dtStartTime = Now
    Do While iebrowser.readyState <> READYSTATE_COMPLETE
        If DateDiff("s", dtStartTime, Now) > 10 Then GoTo GetExternalIPAddress_Timeout
    Loop
 
    'Get the page contents
    sDocHTML = iebrowser.Document.documentElement.innerHTML
 
    'destroy the browser object
    Set iebrowser = Nothing
 
    strIPAddress = Mid(sDocHTML, InStr(sDocHTML, "<BODY>") + 6, InStr(sDocHTML, "</BODY>") - InStr(sDocHTML, "<BODY>") - 6)
 
GetExternalIPAddress_Exit:
    GetExternalIPAddress = strIPAddress
    Exit Function
 
GetExternalIPAddress_Timeout:
    strIPAddress = "Timeout"
    GoTo GetExternalIPAddress_Exit
 
GetExternalIPAddress_Error:
    strIPAddress = "Error"
    Resume GetExternalIPAddress_Exit
 
End Function
 
 

[mdommer1]

I almost forgot.. also the reference to the "Microsoft Internet Controls" type library needs to be there.

Thanks again everybody

[mdommer1]

Adezii,
I was looking for more of how the outside world sees from the other side of a router, but thanks. Your routine is helpful to me on another project.

[NeoPa]

@mdommer1
Absolutely. I can hardly think of better.

That, and a pleasant manner when dealing with those attempting to help you, and it seems you have that already ;) I look forward to seeing more of you :)