Excel

Return IP Address into cell A1

Ease of Use

Easy

Version tested with

2000, 2003 

Submitted by:

brettdj

Description:

The IP address of the machine running this code is dumped into cell A1 of the active worksheet 

Discussion:

You want to return an IP address with VBA 

Code:

instructions for use

			

Option Explicit Sub IPtest() Dim wsh As Object Dim RegEx As Object, RegM As Object Dim FSO As Object, fil As Object Dim ts As Object, txtAll As String, TempFil As String Set wsh = CreateObject("WScript.Shell") Set FSO = CreateObject("Scripting.FileSystemObject") Set RegEx = CreateObject("vbscript.regexp") TempFil = "C:\myip.txt" ' Save ipconfig info to temporary file wsh.Run "%comspec% /c ipconfig > " & TempFil, 0, True With RegEx .Pattern = "(\d{1,3}\.){3}\d{1,3}" .Global = False End With Set fil = FSO.GetFile(TempFil) ' Access temporary file Set ts = fil.OpenAsTextStream(1) txtAll = ts.ReadAll Set RegM = RegEx.Execute(txtAll) ' Return IP address to Activesheet cell A1 by parsing text ActiveSheet.Range("A1").Value = RegM(0) ActiveSheet.Range("A1").EntireColumn.AutoFit ts.Close ' Remove temp file Kill TempFil Set ts = Nothing Set wsh = Nothing Set fil = Nothing Set FSO = Nothing Set RegM = Nothing Set RegEx = Nothing End Sub

How to use:

  1. Copy the code above.
  2. Open your workbook.
  3. Hit Alt+F11 to open the Visual Basic Editor (VBE).
  4. From the menu, choose Insert-Module.
  5. Paste the code into the code window at right.
  6. Close the VBE, and save the file if desired.
 

Test the code:

  1. Run the macro by going to Tools-Macro-Macros and double-click IPtest.
 

Sample File:

ReturnIPaddress(KB22).zip 16.29KB 

Approved by mdmackillop


This entry has been viewed 458 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express