How To Log User's IP Address?

[CaliKidd]

I'd like to write some Excel VBA code that identifies the user's IP address and saves that info to a cell in a spreadsheet. Any ideas?

 

[Sandeep Warrier]

Hi

See if you can work with this

http://www.vbaexpress.com/kb/getarticle.php?kb_id=537

 

[CaliKidd]

Thanks, the code worked, but the IP that it found is the IP assigned by my router. I'd like to grab the IP address I use to access the internet. Any ideas?

 

[Blade Hunter]

Probably programatically use the response from a web based service like www.whatismyip.com

Or rather the scrape link:
www.whatismyip.com/automation/n09230945.asp

 

[CaliKidd]

OK, Blade Hunter, I like the scrape link idea. It provides the IP address I want to capture, but I'm not sure how to write the VBA code to grab this from the webpage and then save it into a worksheet cell. Can you assist?

 

[Sandeep Warrier]

Web query seems like an overkill for this, but one possible way

Sub Test()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.whatismyip.com/automation/n09230945.asp", Destination:=Range( _
        "$A$1"))
        .Name = "n09230945_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[brettdj]

Using the scrape link from Blade Hunter in xmlhttp (thx to Matty Vidas for dragging me off IE)

Sub IP()
    Dim objxmlhttp
    Dim strHtml As String
    Set objxmlhttp = CreateObject("Microsoft.XMLHTTP")
    With objxmlhttp
        .Open "GET", "http://www.whatismyip.com/automation/n09230945.asp"
        .Send
        Do While .readystate <> 4
        DoEvents
        Loop
        strHtml = .responseText
    End With
    MsgBox strHtml
End Sub

 

[CaliKidd]

Wonderful! Both examples work, so I have not just one solution, but two ways of getting this done! Thank you both for taking the time to help me out. I will study the code logic from both examples to edify my VBA skills.