how to get a Mac Address from another PC

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

Answer 1

Hi. Just looking at that, it seems like it would work with VBA as well. Where are you hanging up?

Answer 2

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

Answer 3

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!

Answer 4

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

Answer 5

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!

Answer 6

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

Answer 7

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 [e-mail]
'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!

Answer 8

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

Answer 9

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

Answer 10

Hi Chris. By any chance, did you name the module GetMACAddress? If so, try renaming it to modGetMACAddress instead.

Answer 11

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

Answer 12

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

Answer 13

So, how many MAC address did you get?

Answer 14

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

Answer 15

For example, if you use a button to assign the MAC address to a field on the form, you could try:

Me.FieldName=GetMACAddress()

Answer 16

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

Answer 17

I should say that

Option Compare Database
Option Explicit

Public Function GetMACAddress() As String
'Modified by [e-mail]
'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

Answer 18

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

Answer 19

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.

Answer 20

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

Answer 21

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

Answer 22

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
'[e-mail]
'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

Answer 23

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...

Answer 24

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
'[e-mail]
'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

Answer 25

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...