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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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,214,965
Messages
6,122,500
Members
449,090
Latest member
RandomExceller01

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