How to lookup an IP address in an Excel spreadsheet?

Posted by Kevin Williams on Super User See other posts from Super User or by Kevin Williams
Published on 2009-11-25T17:56:53Z Indexed on 2010/03/25 1:03 UTC
Read the original article Hit count: 536

Filed under:

excel

|

vbs

|

ip-address

|

gethostbyname

I am working with a decent sized spreadsheet of domains and server names. Another user of the spreadsheet needs the IP address for each of the DNS entries on the worksheet. Instead of manually adding and then having to maintain this list I was hoping there was an easy way to do an IPAddress lookup to display the IP address in a cell.

I've seen some VBScripts that call gethostbyname, e.g.:

Declare Function GetHostByName Lib "wsock32.dll" Alias "gethostbyname" (ByVal Host As String) As Long

But I'm not a VB expert so I'm not sure if this is the right way to go.

Any advice/links would be appreciated!

also if this is a question better suited for Stack Overflow - let me know, I'm new here.

© Super User or respective owner

Related posts about excel

Excel error "This workbook contains Excel 4.0 macros or Excel 5.0 modules"

as seen on Super User - Search for 'Super User'
I have a workbook that was protected via the Protect Workbook feature. It was sent to someone else to modify. When they sent it back, it was unprotected and when I try to reprotect it I get this error, "This workbook contains Excel 4.0 macros or Excel 5.0 modules. If you would like to… >>> More
Open excel 2007 excel files and save as 97-2003 formats in VBA

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a weird situation where I have a set of excel files, all having the extension .xls., in a directory where I can open all of them just fine in Excel 2007. The odd thing is that I cannot open them in Excel 2003, on the same machine, without opening the file first in 2007 and going and saving… >>> More
Creating Excel or Excel compatible Spreadsheets on the server side in C#

as seen on Stack Overflow - Search for 'Stack Overflow'
I'd like to make server-side excel compatible spreadsheets that maybe use OpenXML or a structured data format. I've used Office Interop before to generate Excel spreadsheets, but those apps run on a PC that has office installed. For this web project I'm building, the server doesn't have office installed… >>> More
vba access values from excel workbook from another excel workbook

as seen on Stack Overflow - Search for 'Stack Overflow'
Any ideas why this code will not work? Workbooks.Open Filename:="C:\a.xls" Workbooks("a.xls").Activate Worksheets("b").Select Dim OutputArray(10, 10) OutputArray(1, 1) = Workbooks("c.xls").Worksheets("worksheet_otherthan_default").Select.Range(A1).Value I'm trying to run a macro in one file (unnamed… >>> More
Excel workbook event order and usage when closing Excel

as seen on Stack Overflow - Search for 'Stack Overflow'
Given the following workbook events: BeforeClose BeforeSave Please tell me: - The firing order in the case of multiple workbooks alreay opened (wb1, wb2 and wb3 are opened in this order) and the user closes Excel. You can assume all 3 needs saving. - What happen if user cancels one of the saving… >>> More

Related posts about vbs

Networkmapping script (VBS) Vista doesn't work, XP does

as seen on Server Fault - Search for 'Server Fault'
Hi all, I've got a weird problem, (like always :p ) Okay: Situation: Windows 2003 domain with XP clients. With a GPO I'm running a VBS script on login to map a few drives. This works great on XP, but not on Vista. If I manually run the script after the user has logged on, it works. So I know the… >>> More
cscript - Invalid procedure call or argument when running a vbs file

as seen on Server Fault - Search for 'Server Fault'
I've been trying to use check_time.vbs to check the Windows time. Here's the script: http://pastebin.com/NfUrCAqU The help message could be display: C:\Program Files\NSClient++\scripts>cscript //NoLogo check_time.vbs /? check_time.vbs V1.01 Usage: cscript /NoLogo check_time.vbs serverlist warn… >>> More
Using 32bit COM object from C# or VBS on Vista 64bit and getting error 80004005

as seen on Stack Overflow - Search for 'Stack Overflow'
I need some mind reading here, since I am trying to do what I do not completely understand. There is a 32-bit application (electronic trading application called CQG) which provides a COM API for external access. I have sample programs and scripts which access this API from Excel, .NET (C++, VB and… >>> More
WMI/VBS/HTML System Information Script

as seen on Stack Overflow - Search for 'Stack Overflow'
Hey guys; havin' a problem with this code here; can't seem to work out whats goin' wrong with it. All other variables seem to print fine in the HTML ouput; but I get an error that relates to the cputype variable. I get the following error C:\Users\Methical\Desktop\sysinfo.vbs(235,1) Microsoft… >>> More
VBS Runtime error code 800A01B6

as seen on Stack Overflow - Search for 'Stack Overflow'
I am a newbie to VBS scripting. I am getting above error on line 54, character 5 in script below. This error says "Object doesn't support this property or method: 'MimeMapArray'". And line it is referring to is: MimeMapArray(i) = CreateObject("MimeMap") Can u tell me what I am doing wrong?… >>> More