none
how to get a Mac Address from another PC RRS feed

  • Question

  • Hello World,

    I have a VBS script I run from time to time when I need to find a Mac or Serial or IP based on the node of a PC. I want to do that from within Access to update the info for a PC in the database. Any ideas? see the script below...

    Thank you!!!

    On error resume next
    'strComputer = Wscript.Arguments.Item(0)
    strComputer=InputBox ("Enter NODENAME to query for Mac Address")

    If strComputer = "" Then strComputer="." 'Use "." (local computer) if no computer-name was entered

    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")

    ReDim arrIP(-1)
    For Each objItem in colItems
      Wscript.Echo objItem.MACAddress 'Uncomment if you want to display the info
       macinfo = objItem.MACAddress
       For Each addr In objItem.IPAddress
        ReDim Preserve arrIP(UBound(arrIP)+1)
        arrIP(UBound(arrIP)) = addr
       Next
    Next

    strIP = Join(arrIP, ",")

    If strComputer = "." Then
      Set objWMIService = GetObject( "winmgmts:\\" & strComputer & "\root\cimv2")
      Set colItems = objWMIService.ExecQuery( "Select * from Win32_ComputerSystem", , 48 )

    'Grab the netbios name of each computer
      For Each objItem in colItems
         host = objItem.Name
    '    WScript.Echo "MAC Address for Computer Name: " & host & " is " & macinfo 'Uncomment to display    
      Next
    Else
      host = strComputer
    End If

    strLine = host & "," & macinfo & "," & strIP & "," & Date

    Dim objFile, objFolder, objFSO
    Dim strFolder, strFile, strLine
    strFolder = "c:\scripts\macinfo"
    strFile = "\" & host & "-MAC.txt"

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    set objFile = nothing
    ' For auditing, we want APPEND mode to track all MAC/IPs for the computer
    Const ForAppending = 8

    Set objFile = objFSO.OpenTextFile (strFolder & strFile, ForAppending, True)

    ' Write Computer Name, MAC address, IP Address(es) and Date to a text file
    objFile.WriteLine(strLine)
    objFile.Close

    wscript.quit(0)


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, October 2, 2015 4:51 PM

Answers

  • Hi Chris,

    Since I cannot test this using a working network, give the following a shot and let me know what happens.

    Public Function GetMACAddress() As String
    'Modified by thedbguy@gmail.com
    '10/3/2015
    'retrieve computer's MAC address
    
    Dim objWMIService As Object
    Dim colItems As Variant
    Dim objItem As Variant
    Dim strComputer As String
    Dim macinfo As String
    
    strComputer = InputBox("Enter NODENAME to query for Mac Address")
    
    If strComputer = "" Then strComputer = "." 'Use "." (local computer) if no computer-name was entered
    
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    
    For Each objItem In colItems
       macinfo = objItem.MACAddress
       Debug.Print macinfo
    Next
    
    GetMACAddress = macinfo
    
    End Function
    

    In the Immediate Window, just type: GetMACAddress and then tell me what shows up underneath it.

    Cheers!

    • Marked as answer by KCDW Monday, October 19, 2015 1:22 PM
    Tuesday, October 6, 2015 12:31 AM
  • Hi Chris. By any chance, did you name the module GetMACAddress? If so, try renaming it to modGetMACAddress instead.
    • Marked as answer by KCDW Monday, October 19, 2015 1:23 PM
    Tuesday, October 6, 2015 2:36 PM
  • For example, if you use a button to assign the MAC address to a field on the form, you could try:

    Me.FieldName=GetMACAddress()

    • Marked as answer by KCDW Monday, October 19, 2015 1:23 PM
    Tuesday, October 6, 2015 4:58 PM

All replies

  • Hi. Just looking at that, it seems like it would work with VBA as well. Where are you hanging up?
    Friday, October 2, 2015 6:44 PM
  • Hey DB guy,

    I'm hanging up wherever I can.

    Where do I put it. How do I use it.

    Should I place the code in a standard module and call it from the Form?

    Should I place it in the Form's module?

    how should I change the last section to instead of writing to a text file to write into the individual Fields in the Form.

    What should be changed so that I am using this with VBA from an Access Form and not a VBS from the desktop?

    Thanks for responding DB guy!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, October 2, 2015 7:17 PM
  • Hi Chris. I see what you mean. I'm on my way out right now but will try to respond back later after I play with it a little to make sure I tell you something that I know will work. Cheers!
    Friday, October 2, 2015 8:05 PM
  • Thanks DB guy,

    Have a great weekend


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, October 2, 2015 9:58 PM
  • Hi Chris. I tested this out, and it does work with VBA. However, I have a question for you. Since I don't have a network at home, I did not enter any node name to check the MAC address of my computer. I got two message boxes showing two different MAC addresses: one for my WiFi and one for my VM (my NIC is not connected, or I probably would have seen three message boxes). However, when I opened the text file it created, I only saw one MAC address entry. Is this also what you get when using the script on the network? If so, my question to you for modifying the code for your form is which MAC address do you want returned to your form? I see from the text file that the script returned the last MAC address found. Is that what you want as well? Also, you mentioned something about assigning the values into separate fields on your form. What do you mean by that? If the MAC address was 1A:2B:3C:4D:5E:6F, did you want the whole thing together or each section one at a time? Thanks!
    • Proposed as answer by Tony---- Monday, October 5, 2015 6:44 AM
    • Unproposed as answer by KCDW Monday, October 5, 2015 6:16 PM
    Saturday, October 3, 2015 4:52 PM
  • 1) I tested this out, and it does work with VBA.

    2) ...question for you. Since I don't have a network at home, I did not enter any node name to check the MAC address of my computer. I got two message boxes showing two different MAC addresses: one for my WiFi and one for my VM (my NIC is not connected, or I probably would have seen three message boxes). However, when I opened the text file it created, I only saw one MAC address entry. Is this also what you get when using the script on the network?

    3) If so, my question to you for modifying the code for your form is which MAC address do you want returned to your form? I see from the text file that the script returned the last MAC address found. Is that what you want as well?

    4) Also, you mentioned something about assigning the values into separate fields on your form. What do you mean by that? If the MAC address was 1A:2B:3C:4D:5E:6F, did you want the whole thing together or each section one at a time? Thanks!

    Hi DB guy and thanks again for looking into this with me.

    1) Great how did you call it, and even though it did ~work~ it seems a bit cumbersome. Perhaps there is a better method?

    2) When you don't specify a computer name (node) then the script looks at the local machine for information. I haven't tried it on Laptops yet as I haven't had the need. When you say it picked up the WiFi, does that mean it picked up your router? and your VM? hadn't tried in there yet either. I always get 1 Mac Address returned unless I have multiple nic cards active on the network from the same machine. Rare but there are a couple of these. Usually only when additional IP's are enabled.

    3) In our case the NIC that is active. The others are locked in the image before distribution.

    4) What I need to do is update the existing record for the node. The fields on the Form are using the current record [node], find the following [IP], [mac], [Serial].

    I can get all this with a script but I want to use Access to run from the current Record to update the record with this data as well as a date time stamp of the update.

    So I'm guessing I need to have a button on the Form that looks at the Record and says for this records node update the Mac etc.

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Monday, October 5, 2015 7:26 PM
  • Hi Chris,

    Since I cannot test this using a working network, give the following a shot and let me know what happens.

    Public Function GetMACAddress() As String
    'Modified by thedbguy@gmail.com
    '10/3/2015
    'retrieve computer's MAC address
    
    Dim objWMIService As Object
    Dim colItems As Variant
    Dim objItem As Variant
    Dim strComputer As String
    Dim macinfo As String
    
    strComputer = InputBox("Enter NODENAME to query for Mac Address")
    
    If strComputer = "" Then strComputer = "." 'Use "." (local computer) if no computer-name was entered
    
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    
    For Each objItem In colItems
       macinfo = objItem.MACAddress
       Debug.Print macinfo
    Next
    
    GetMACAddress = macinfo
    
    End Function
    

    In the Immediate Window, just type: GetMACAddress and then tell me what shows up underneath it.

    Cheers!

    • Marked as answer by KCDW Monday, October 19, 2015 1:22 PM
    Tuesday, October 6, 2015 12:31 AM
  • Good Morning DB guy,

    I get the following error in the immediate window when I enter "GetMACAddress"

    ---------------------------
    Microsoft Visual Basic for Applications
    ---------------------------
    Compile error:

    Sub or Function not defined
    ---------------------------
    OK   Help  
    ---------------------------

    If I use, ?GetMACAddress

    a blank row

    Where GetMACAddress? returns

    ---------------------------
    Microsoft Visual Basic for Applications
    ---------------------------
    Compile error:

    Expected: expression
    ---------------------------
    OK   Help  
    ---------------------------


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, October 6, 2015 2:32 PM
  • From the Help

    There is no procedure by this name in the current scope, but there is a variable by this name. You can call a procedure, but not a variable. This error has the following cause and solution:

    • The name of a variable is used as a procedure call. The error may also be caused by misspelling the name of a valid procedure, because that can be misconstrued as an implicitly defined variable. Check the spelling of the procedure name, and make sure the procedure you are trying to call isn't private to another module.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, October 6, 2015 2:34 PM
  • Hi Chris. By any chance, did you name the module GetMACAddress? If so, try renaming it to modGetMACAddress instead.
    • Marked as answer by KCDW Monday, October 19, 2015 1:23 PM
    Tuesday, October 6, 2015 2:36 PM
  • Hi Chris. By any chance, did you name the module GetMACAddress? If so, try renaming it to modGetMACAddress instead.

    No,

    Actually I added the function to an existing module. I'll try adding to a new Module with that name.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, October 6, 2015 2:41 PM
  • Hi DB Guy,

    That works.

    I don't know why I had to place it in a module by itself.

    So now all that needs to be done is call the function from a Form Field to get the value stored in the Table, right?


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, October 6, 2015 2:45 PM
  • So, how many MAC address did you get?
    Tuesday, October 6, 2015 4:03 PM
  • Just the active 1.

    Still haven't figured out how to get the response in the Record though.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, October 6, 2015 4:11 PM
  • For example, if you use a button to assign the MAC address to a field on the form, you could try:

    Me.FieldName=GetMACAddress()

    • Marked as answer by KCDW Monday, October 19, 2015 1:23 PM
    Tuesday, October 6, 2015 4:58 PM
  • Alright DB guy, I am catching on but now lets say I want to instead of use a command button, I want to use the after update event of the field where I enter the node to supply the node in the code to look up and return the value. How would I change that Function to accommodate? example

    Field: Node, Enter the node value here, the after update event fires and returns the value to...

    Field: MacAddress, The Mac Address is returned here

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, October 6, 2015 7:47 PM
  • I should say that

    Option Compare Database
    Option Explicit
    
    Public Function GetMACAddress() As String
    'Modified by thedbguy@gmail.com
    '10/3/2015
    'retrieve computer's MAC address
    
    Dim objWMIService As Object
    Dim colItems As Variant
    Dim objItem As Variant
    Dim strComputer As String
    Dim macinfo As String
    
    strComputer = Forms.GetMac.Node
    
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    
    For Each objItem In colItems
       macinfo = objItem.MacAddress
       Debug.Print macinfo
    Next
    
    GetMACAddress = macinfo
    
    End Function
    

    Returns the correct value however I need to take care of issues with NULL and deleting Node and changing nodes.

    Any thoughts???

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, October 6, 2015 7:52 PM
  • Even more cool would be if I could have the code loop through all the records and update every Nodes Mac Address.

    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, October 6, 2015 9:42 PM
  • Hi Chris,

    I'm not in front of a computer right now, but it shouldn't be a problem to do that. If you haven't already, please post your table structure, so I can work on it when I get a chance.

    Wednesday, October 7, 2015 2:15 AM
  • Good busy Morning today,

    Table structure is simple for testing, 4 Fields

    1. MacID
    2. Node
    3. Mac
    4. IP

    The Form is equally simple with only the After Update Event code that calls the Function and the Function itself in a separate module.

    Also would like to modify the Function to use the IP to find the Mac.

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, October 7, 2015 3:42 PM
  • So the following gets the Mac from the IP

    Public Function GetMacIPSource() As String
    Dim objWMIService As Object
    Dim colItems As Variant
    Dim objItem As Variant
    Dim strComputer As String
    Dim macinfo As String
    
     strComputer = Forms.getMac.IP
    
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    
    For Each objItem In colItems
       macinfo = objItem.MacAddress
       Debug.Print macinfo
    Next
    
    GetMacIPSource = macinfo
    
    End Function
    

    However I still need to deal with the occasional PC or Laptop that is not plugged into the network so the error message doesn't pop up. Also still need to loop through all Nodes in database to pull the mac.

    In a sense of redundancy, would like to modify the code from the node call to get the mac to also update the IP as well as the IP call to get the mac to return the node.

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, October 7, 2015 9:25 PM
  • Hi Chris,

    I only have a couple of minutes this morning, so I am just posting the below code but will follow up later.

    Public Function GetMACAddress(Optional strNode = ".") As String
    'thedbguy@gmail.com
    '10/3/2015
    'retrieve computer's MAC address
    
    Dim objWMIService As Object
    Dim colItems As Variant
    Dim objItem As Variant
    Dim macinfo As String
    
    Set objWMIService = GetObject("winmgmts:\\" & strNode & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    
    For Each objItem In colItems
       macinfo = objItem.MACAddress
    Next
    
    GetMACAddress = macinfo
    
    End Function
    

    Thursday, October 8, 2015 1:09 PM
  • Hi Chris,

    To handle errors for when computers are not connected to the network, you could implement an Error Handler routine.

    To loop through the records and modify the data with the MAC address, you can create a recordset and loop through each record and then call the function in each iteration. Or, you can create an UPDATE query where you SET the field to the value returned by the function. For example: UPDATE TableName SET MACFieldName=GetMACAddress([Node])

    Hope that helps...

    Saturday, October 10, 2015 5:28 PM
  • Hi Chris,

    I only have a couple of minutes this morning, so I am just posting the below code but will follow up later.

    Public Function GetMACAddress(Optional strNode = ".") As String
    'thedbguy@gmail.com
    '10/3/2015
    'retrieve computer's MAC address
    
    Dim objWMIService As Object
    Dim colItems As Variant
    Dim objItem As Variant
    Dim macinfo As String
    
    Set objWMIService = GetObject("winmgmts:\\" & strNode & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    
    For Each objItem In colItems
       macinfo = objItem.MACAddress
    Next
    
    GetMACAddress = macinfo
    
    End Function
    

    So I could not get this code to work even after trying to modifying. Your earlier post answered my initial question so I will mark it as answered. Your later post seems to be a general method rather than a specific process.

    If you could please elaborate on your post of 10/10/2015 5:25 PM and review your posted code here.

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Monday, October 19, 2015 1:21 PM
  • Hi Chris,

    The difference between the first code I posted and the second one is that the last code accepts an argument when you call the function GetMACAddress(), and I showed you how to call it in my subsequent post, passing it a name of a field called "Node" as an example. Basically, the second function won't prompt you for a node anymore. Instead, it will take the value of the field as you use it in a query. For example:

    SELECT Node, GetMACAddress(Node) As MAC FROM TableName

    Hope that helps...

    Tuesday, October 20, 2015 1:33 AM