Problems with nslookup function

P

protaylort

New Member
Joined
Mar 2, 2015
Messages
1
Dear community,

I have been struggling with rewriting the following function:
Code: 
[COLOR=maroon][B]Public[/B][/COLOR] [COLOR=maroon][B]Function[/B][/COLOR] NSLookup[COLOR=#808030]([/COLOR]lookupVal [COLOR=maroon][B]As[/B][/COLOR] [COLOR=maroon][B]String[/B][/COLOR][COLOR=#808030],[/COLOR] Optional addressOpt [COLOR=maroon][B]As[/B][/COLOR] [COLOR=maroon][B]Integer[/B][/COLOR][COLOR=#808030])[/COLOR] [COLOR=maroon][B]As[/B][/COLOR] [COLOR=maroon][B]String[/B][/COLOR]
   [COLOR=maroon][B]Const[/B][/COLOR] ADDRESS_LOOKUP [COLOR=#808030]=[/COLOR] [COLOR=#008c00]1[/COLOR]
   [COLOR=maroon][B]Const[/B][/COLOR] NAME_LOOKUP [COLOR=#808030]=[/COLOR] [COLOR=#008c00]2[/COLOR]
   [COLOR=maroon][B]Const[/B][/COLOR] AUTO_DETECT [COLOR=#808030]=[/COLOR] [COLOR=#008c00]0[/COLOR]
   
   [COLOR=dimgrey]'Skip everything if the field is blank[/COLOR]
   [COLOR=maroon][B]If[/B][/COLOR] lookupVal [COLOR=#808030]<[/COLOR][COLOR=#808030]>[/COLOR] [COLOR=#0000e6]""[/COLOR] [COLOR=maroon][B]Then[/B][/COLOR]
        [COLOR=maroon][B]Dim[/B][/COLOR] oFSO [COLOR=maroon][B]As[/B][/COLOR] [COLOR=maroon][B]Object[/B][/COLOR][COLOR=#808030],[/COLOR] oShell [COLOR=maroon][B]As[/B][/COLOR] [COLOR=maroon][B]Object[/B][/COLOR][COLOR=#808030],[/COLOR] oTempFile [COLOR=maroon][B]As[/B][/COLOR] [COLOR=maroon][B]Object[/B][/COLOR]
        [COLOR=maroon][B]Dim[/B][/COLOR] sLine [COLOR=maroon][B]As[/B][/COLOR] [COLOR=maroon][B]String[/B][/COLOR][COLOR=#808030],[/COLOR] sFilename [COLOR=maroon][B]As[/B][/COLOR] [COLOR=maroon][B]String[/B][/COLOR]
        [COLOR=maroon][B]Dim[/B][/COLOR] intFound [COLOR=maroon][B]As[/B][/COLOR] [COLOR=maroon][B]Integer[/B][/COLOR]
        [COLOR=maroon][B]Set[/B][/COLOR] oFSO [COLOR=#808030]=[/COLOR] [COLOR=maroon][B]CreateObject[/B][/COLOR][COLOR=#808030]([/COLOR][COLOR=#0000e6]"Scripting.FileSystemObject"[/COLOR][COLOR=#808030])[/COLOR]
        [COLOR=maroon][B]Set[/B][/COLOR] oShell [COLOR=#808030]=[/COLOR] [COLOR=maroon][B]CreateObject[/B][/COLOR][COLOR=#808030]([/COLOR][COLOR=#0000e6]"Wscript.Shell"[/COLOR][COLOR=#808030])[/COLOR]
        
        [COLOR=dimgrey]'Handle the addresOpt operand[/COLOR]
        [COLOR=dimgrey]'Regular Expressions are used to complete a substring match for an IP Address[/COLOR]
        [COLOR=dimgrey]'If an IP Address is found, a DNS Name Lookup will be forced[/COLOR]
        [COLOR=maroon][B]If[/B][/COLOR] addressOpt [COLOR=#808030]=[/COLOR] AUTO_DETECT [COLOR=maroon][B]Then[/B][/COLOR]
            ipLookup [COLOR=#808030]=[/COLOR] FindIP[COLOR=#808030]([/COLOR]lookupVal[COLOR=#808030])[/COLOR]
            [COLOR=maroon][B]If[/B][/COLOR] ipLookup [COLOR=#808030]=[/COLOR] [COLOR=#0000e6]""[/COLOR] [COLOR=maroon][B]Then[/B][/COLOR]
                addressOpt [COLOR=#808030]=[/COLOR] ADDRESS_LOOKUP
            [COLOR=maroon][B]Else[/B][/COLOR]
                addressOpt [COLOR=#808030]=[/COLOR] NAME_LOOKUP
                lookupVal [COLOR=#808030]=[/COLOR] ipLookup
            [COLOR=maroon][B]End[/B][/COLOR] [COLOR=maroon][B]If[/B][/COLOR]
        [COLOR=dimgrey]'Do a regular expression substring match for an IP Address[/COLOR]
        [COLOR=maroon][B]ElseIf[/B][/COLOR] addressOpt [COLOR=#808030]=[/COLOR] NAME_LOOKUP [COLOR=maroon][B]Then[/B][/COLOR]
            lookupVal [COLOR=#808030]=[/COLOR] FindIP[COLOR=#808030]([/COLOR]lookupVal[COLOR=#808030])[/COLOR]
        [COLOR=maroon][B]End[/B][/COLOR] [COLOR=maroon][B]If[/B][/COLOR]
        
        [COLOR=dimgrey]'Run the nslookup command[/COLOR]
        sFilename [COLOR=#808030]=[/COLOR] oFSO[COLOR=#008c00].[/COLOR]GetTempName
        oShell[COLOR=#008c00].[/COLOR]Run [COLOR=#0000e6]"cmd /c nslookup "[/COLOR] [COLOR=#808030]&[/COLOR] lookupVal [COLOR=#808030]&[/COLOR] [COLOR=#0000e6]" > "[/COLOR] [COLOR=#808030]&[/COLOR] sFilename[COLOR=#808030],[/COLOR] [COLOR=#008c00]0[/COLOR][COLOR=#808030],[/COLOR] [COLOR=#0f4d75]True[/COLOR]
        [COLOR=maroon][B]Set[/B][/COLOR] oTempFile [COLOR=#808030]=[/COLOR] oFSO[COLOR=#008c00].[/COLOR]OpenTextFile[COLOR=#808030]([/COLOR]sFilename[COLOR=#808030],[/COLOR] [COLOR=#008c00]1[/COLOR][COLOR=#808030])[/COLOR]
        [COLOR=maroon][B]Do[/B][/COLOR] [COLOR=maroon][B]While[/B][/COLOR] oTempFile[COLOR=#008c00].[/COLOR]AtEndOfStream [COLOR=#808030]<[/COLOR][COLOR=#808030]>[/COLOR] [COLOR=#0f4d75]True[/COLOR]
            sLine [COLOR=#808030]=[/COLOR] oTempFile[COLOR=#008c00].[/COLOR]Readline
            cmdStr [COLOR=#808030]=[/COLOR] cmdStr [COLOR=#808030]&[/COLOR] [COLOR=maroon][B]Trim[/B][/COLOR][COLOR=#808030]([/COLOR]sLine[COLOR=#808030])[/COLOR] [COLOR=#808030]&[/COLOR] vbCrLf
        [COLOR=maroon][B]Loop[/B][/COLOR]
        oTempFile[COLOR=#008c00].[/COLOR][COLOR=maroon][B]Close[/B][/COLOR]
        oFSO[COLOR=#008c00].[/COLOR]DeleteFile [COLOR=#808030]([/COLOR]sFilename[COLOR=#808030])[/COLOR]
        
        [COLOR=dimgrey]'Process the result[/COLOR]
        intFound [COLOR=#808030]=[/COLOR] [COLOR=maroon][B]InStr[/B][/COLOR][COLOR=#808030]([/COLOR][COLOR=#008c00]1[/COLOR][COLOR=#808030],[/COLOR] cmdStr[COLOR=#808030],[/COLOR] [COLOR=#0000e6]"Name:"[/COLOR][COLOR=#808030],[/COLOR] vbTextCompare[COLOR=#808030])[/COLOR]
        [COLOR=maroon][B]If[/B][/COLOR] intFound [COLOR=#808030]=[/COLOR] [COLOR=#008c00]0[/COLOR] [COLOR=maroon][B]Then[/B][/COLOR]
            NSLookup [COLOR=#808030]=[/COLOR] [COLOR=#0000e6]"NotFound"[/COLOR]
            [COLOR=maroon][B]Exit[/B][/COLOR] [COLOR=maroon][B]Function[/B][/COLOR]
        [COLOR=maroon][B]ElseIf[/B][/COLOR] intFound [COLOR=#808030]>[/COLOR] [COLOR=#008c00]0[/COLOR] [COLOR=maroon][B]Then[/B][/COLOR]
            [COLOR=dimgrey]'TODO: Cleanup with RegEx[/COLOR]
            [COLOR=maroon][B]If[/B][/COLOR] addressOpt [COLOR=#808030]=[/COLOR] ADDRESS_LOOKUP [COLOR=maroon][B]Then[/B][/COLOR]
                loc1 [COLOR=#808030]=[/COLOR] [COLOR=maroon][B]InStr[/B][/COLOR][COLOR=#808030]([/COLOR]intFound[COLOR=#808030],[/COLOR] cmdStr[COLOR=#808030],[/COLOR] [COLOR=#0000e6]"Address:"[/COLOR][COLOR=#808030],[/COLOR] vbTextCompare[COLOR=#808030])[/COLOR] [COLOR=#808030]+[/COLOR] [COLOR=maroon][B]InStr[/B][/COLOR][COLOR=#808030]([/COLOR]intFound[COLOR=#808030],[/COLOR] cmdStr[COLOR=#808030],[/COLOR] [COLOR=#0000e6]"Addresses:"[/COLOR][COLOR=#808030],[/COLOR] vbTextCompare[COLOR=#808030])[/COLOR]
                loc2 [COLOR=#808030]=[/COLOR] [COLOR=maroon][B]InStr[/B][/COLOR][COLOR=#808030]([/COLOR]loc1[COLOR=#808030],[/COLOR] cmdStr[COLOR=#808030],[/COLOR] vbCrLf[COLOR=#808030],[/COLOR] vbTextCompare[COLOR=#808030])[/COLOR]
                nameStr [COLOR=#808030]=[/COLOR] [COLOR=maroon][B]Trim[/B][/COLOR][COLOR=#808030]([/COLOR][COLOR=maroon][B]Mid[/B][/COLOR][COLOR=#808030]([/COLOR]cmdStr[COLOR=#808030],[/COLOR] loc1 [COLOR=#808030]+[/COLOR] [COLOR=#008c00]8[/COLOR][COLOR=#808030],[/COLOR] loc2 [COLOR=#808030]-[/COLOR] loc1 [COLOR=#808030]-[/COLOR] [COLOR=#008c00]8[/COLOR][COLOR=#808030])[/COLOR][COLOR=#808030])[/COLOR]
            [COLOR=maroon][B]ElseIf[/B][/COLOR] addressOpt [COLOR=#808030]=[/COLOR] NAME_LOOKUP [COLOR=maroon][B]Then[/B][/COLOR]
                loc1 [COLOR=#808030]=[/COLOR] [COLOR=maroon][B]InStr[/B][/COLOR][COLOR=#808030]([/COLOR]intFound[COLOR=#808030],[/COLOR] cmdStr[COLOR=#808030],[/COLOR] [COLOR=#0000e6]"Name:"[/COLOR][COLOR=#808030],[/COLOR] vbTextCompare[COLOR=#808030])[/COLOR]
                loc2 [COLOR=#808030]=[/COLOR] [COLOR=maroon][B]InStr[/B][/COLOR][COLOR=#808030]([/COLOR]loc1[COLOR=#808030],[/COLOR] cmdStr[COLOR=#808030],[/COLOR] vbCrLf[COLOR=#808030],[/COLOR] vbTextCompare[COLOR=#808030])[/COLOR]
                nameStr [COLOR=#808030]=[/COLOR] [COLOR=maroon][B]Trim[/B][/COLOR][COLOR=#808030]([/COLOR][COLOR=maroon][B]Mid[/B][/COLOR][COLOR=#808030]([/COLOR]cmdStr[COLOR=#808030],[/COLOR] loc1 [COLOR=#808030]+[/COLOR] [COLOR=#008c00]5[/COLOR][COLOR=#808030],[/COLOR] loc2 [COLOR=#808030]-[/COLOR] loc1 [COLOR=#808030]-[/COLOR] [COLOR=#008c00]5[/COLOR][COLOR=#808030])[/COLOR][COLOR=#808030])[/COLOR]
            [COLOR=maroon][B]End[/B][/COLOR] [COLOR=maroon][B]If[/B][/COLOR]
        [COLOR=maroon][B]End[/B][/COLOR] [COLOR=maroon][B]If[/B][/COLOR]
        NSLookup [COLOR=#808030]=[/COLOR] nameStr
    [COLOR=maroon][B]Else[/B][/COLOR]
        NSLookup [COLOR=#808030]=[/COLOR] [COLOR=#0000e6]"N/A"[/COLOR]
    [COLOR=maroon][B]End[/B][/COLOR] [COLOR=maroon][B]If[/B][/COLOR]
[COLOR=maroon][B]End[/B][/COLOR] [COLOR=maroon][B]Function[/B][/COLOR]

[COLOR=maroon][B]Function[/B][/COLOR] FindIP[COLOR=#808030]([/COLOR]strTest [COLOR=maroon][B]As[/B][/COLOR] [COLOR=maroon][B]String[/B][/COLOR][COLOR=#808030])[/COLOR] [COLOR=maroon][B]As[/B][/COLOR] [COLOR=maroon][B]String[/B][/COLOR]
    [COLOR=maroon][B]Dim[/B][/COLOR] RegEx [COLOR=maroon][B]As[/B][/COLOR] [COLOR=maroon][B]Object[/B][/COLOR]
    [COLOR=maroon][B]Dim[/B][/COLOR] valid [COLOR=maroon][B]As[/B][/COLOR] [COLOR=maroon][B]Boolean[/B][/COLOR]
    [COLOR=maroon][B]Dim[/B][/COLOR] Matches [COLOR=maroon][B]As[/B][/COLOR] [COLOR=maroon][B]Object[/B][/COLOR]
    [COLOR=maroon][B]Dim[/B][/COLOR] i [COLOR=maroon][B]As[/B][/COLOR] [COLOR=maroon][B]Integer[/B][/COLOR]
    [COLOR=maroon][B]Set[/B][/COLOR] RegEx [COLOR=#808030]=[/COLOR] [COLOR=maroon][B]CreateObject[/B][/COLOR][COLOR=#808030]([/COLOR][COLOR=#0000e6]"VBScript.RegExp"[/COLOR][COLOR=#808030])[/COLOR]
    
    RegEx[COLOR=#008c00].[/COLOR]Pattern [COLOR=#808030]=[/COLOR] [COLOR=#0000e6]"\b(?:\d{1,3}\.){3}\d{1,3}\b"[/COLOR]
    valid [COLOR=#808030]=[/COLOR] RegEx[COLOR=#008c00].[/COLOR]test[COLOR=#808030]([/COLOR]strTest[COLOR=#808030])[/COLOR]
    [COLOR=maroon][B]If[/B][/COLOR] valid [COLOR=maroon][B]Then[/B][/COLOR]
        [COLOR=maroon][B]Set[/B][/COLOR] Matches [COLOR=#808030]=[/COLOR] RegEx[COLOR=#008c00].[/COLOR]Execute[COLOR=#808030]([/COLOR]strTest[COLOR=#808030])[/COLOR]
        FindIP [COLOR=#808030]=[/COLOR] Matches[COLOR=#808030]([/COLOR][COLOR=#008c00]0[/COLOR][COLOR=#808030])[/COLOR]
    [COLOR=maroon][B]Else[/B][/COLOR]
        FindIP [COLOR=#808030]=[/COLOR] [COLOR=#0000e6]""[/COLOR]
    [COLOR=maroon][B]End[/B][/COLOR] [COLOR=maroon][B]If[/B][/COLOR]
[COLOR=maroon][B]End[/B][/COLOR] [COLOR=maroon][B]Function[/B][/COLOR]
(Found on Tech Efficiency: Excel User Defined Function - nslookup)

My goal is that it does not just return the IP address of the A record on the DNS server of a domain, but that the nslookup would be a MX Records nslookup, and would return all the mail servers of the domain. (I oriented myself on the official Microsoft manual for nslookup: https://technet.microsoft.com/en-us/library/aa998082(v=exchg.65).aspx)

I would appreciate any help, I hope this is somehow doable.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
You must log in or register to reply here.

Similar threads

N
  • Question
Expected Sub, Function or Property Error
Replies
8
Views
5K
ndev2k
N

Forum statistics

Threads
1,215,463
Messages
6,124,965
Members
449,201
Latest member
Jamil ahmed

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top