Thread: Copy PC MAC address to worksheet: how to get rid of MsgBox?

Hi,

I want to read a PC's MAC address and copy it to a worksheet. I have a code that works (see attached). I work on WindowsXP with Excel2002.

The problem is that I had to put a MsgBox on line 19. If I remove the MsgBox command the sh.Exec on line 4 seems to be executed too slowly before opening MAC.txt on line 21. The code is executed but Sheet1 remains empty. That is why I put the MsgBox break.

Does anyone know an elegant way to get rid of this MsgBox? I do not want to bother a user every single time with the MsgBox.

I have tried pause commands or loops which check the existence of the MAC.txt file, but without success.

If someone knows a better way to identify the MAC address alone, i.e. without the "Physical....", this would be even better

Help appreciated.

g

Hello Gstremer,

I needed to do the same thing a few years back. Here is macro using API code to retrieve the Mac address. Add a standard module to your workbook and then copy and paste the macro code into it. This code obviates the need to store the address in a file, open it, etc. The macro funtion returns a formatted string containing the MAC address for the NIC. Your routine would become this...

Code:

Sub MAC_test() 

  ActiveWorkbook.Worksheets("Sheet1").Range("A1").Value = GetMACcAddress()

End Sub

Macro Code:

Code:

'///////////////////////////////////////////////////////////'
'/                                                         /'
'/  Written by: dimport                                    /'
'/  Published by:   w0lf                                   /'
'/  Published on:   2003-06-21                             /'
'/  Website: http://www.osix.net/modules/article/?id=2     /'
'/                                                         /'
'///////////////////////////////////////////////////////////'


Option Explicit

Public Const NCBASTAT As Long = H33
Public Const NCBNAMSZ As Long = 16
Public Const HEAP_ZERO_MEMORY As Long = H8
Public Const HEAP_GENERATE_EXCEPTIONS As Long = H4
Public Const NCBRESET As Long = H32

Public Type NET_CONTROL_BLOCK 'NCB
   ncb_command As Byte
   ncb_retcode As Byte
   ncb_lsn As Byte
   ncb_num As Byte
   ncb_buffer As Long
   ncb_length As Integer
   ncb_callname As String * NCBNAMSZ
   ncb_name As String * NCBNAMSZ
   ncb_rto As Byte
   ncb_sto As Byte
   ncb_post As Long
   ncb_lana_num As Byte
   ncb_cmd_cplt As Byte
   ncb_reserve(9) As Byte ' Reserved, must be 0
   ncb_event As Long
End Type

Public Type ADAPTER_STATUS
   adapter_address(5) As Byte
   rev_major As Byte
   reserved0 As Byte
   adapter_type As Byte
   rev_minor As Byte
   duration As Integer
   frmr_recv As Integer
   frmr_xmit As Integer
   iframe_recv_err As Integer
   xmit_aborts As Integer
   xmit_success As Long
   recv_success As Long
   iframe_xmit_err As Integer
   recv_buff_unavail As Integer
   t1_timeouts As Integer
   ti_timeouts As Integer
   Reserved1 As Long
   free_ncbs As Integer
   max_cfg_ncbs As Integer
   max_ncbs As Integer
   xmit_buf_unavail As Integer
   max_dgram_size As Integer
   pending_sess As Integer
   max_cfg_sess As Integer
   max_sess As Integer
   max_sess_pkt_size As Integer
   name_count As Integer
End Type
  
Public Type NAME_BUFFER
   name As String * NCBNAMSZ
  
name_num As Integer
   name_flags As Integer
End Type

Public Type ASTAT
   adapt As ADAPTER_STATUS
   NameBuff(30) As NAME_BUFFER
End Type

Public Declare Function Netbios Lib "netapi32.dll" _
   (pncb As NET_CONTROL_BLOCK) As Byte
    
Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
   (hpvDest As Any, ByVal _
    hpvSource As Long, ByVal _
    cbCopy As Long)
    
Public Declare Function GetProcessHeap Lib "kernel32" () As Long
 
Public Declare Function HeapAlloc Lib "kernel32" _
    (ByVal hHeap As Long, ByVal dwFlags As Long, _
     ByVal dwBytes As Long) As Long
    
Public Declare Function HeapFree Lib "kernel32" _
    (ByVal hHeap As Long, _
     ByVal dwFlags As Long, _
     lpMem As Any) As Long

Public Function GetMACAddress() As String 'retrieve the MAC Address for the network controller
  'installed, returning a formatted string
  
   Dim tmp As String
   Dim pASTAT As Long
   Dim NCB As NET_CONTROL_BLOCK
   Dim AST As ASTAT 'The IBM NetBIOS 3.0 specifications defines four basic
  'NetBIOS environments under the NCBRESET command. Win32
  'follows the OS/2 Dynamic Link Routine (DLR) environment.
  'This means that the first NCB issued by an application
  'must be a NCBRESET, with the exception of NCBENUM.
  'The Windows NT implementation differs from the IBM
  'NetBIOS 3.0 specifications in the NCB_CALLNAME field.
   NCB.ncb_command = NCBRESET
   Call Netbios(NCB)
  
  'To get the Media Access Control (MAC) address for an
  'ethernet adapter programmatically, use the Netbios()
  'NCBASTAT command and provide a "*" as the name in the
  'NCB.ncb_CallName field (in a 16-chr string).
   NCB.ncb_callname = "* "
   NCB.ncb_command = NCBASTAT
  
  'For machines with multiple network adapters you need to
  'enumerate the LANA numbers and perform the NCBASTAT
  'command on each. Even when you have a single network
  'adapter, it is a good idea to enumerate valid LANA numbers
  'first and perform the NCBASTAT on one of the valid LANA
  'numbers. It is considered bad programming to hardcode the
  'LANA number to 0 (see the comments section below).
   NCB.ncb_lana_num = 0
   NCB.ncb_length = Len(AST)
  
   pASTAT = HeapAlloc(GetProcessHeap(), HEAP_GENERATE_EXCEPTIONS _
            Or HEAP_ZERO_MEMORY, NCB.ncb_length)
           
   If pASTAT = 0 Then
      Debug.Print "memory allocation failed!"
      Exit Function
   End If
  
   NCB.ncb_buffer = pASTAT
   Call Netbios(NCB)
  
   CopyMemory AST, NCB.ncb_buffer, Len(AST)
  
   tmp = Format$(Hex(AST.adapt.adapter_address(0)), "00") & " " _
       & Format$(Hex(AST.adapt.adapter_address(1)), "00") & " " _
       & Format$(Hex(AST.adapt.adapter_address(2)), "00") & " " _
       & Format$(Hex(AST.adapt.adapter_address(3)), "00") & " " _
       & Format$(Hex(AST.adapt.adapter_address(4)), "00") & " " _
       & Format$(Hex(AST.adapt.adapter_address(5)), "00")
          
   HeapFree GetProcessHeap(), 0, pASTAT
  
   GetMACAddress = tmp

End Function

Sincerely,
Leith Ross

Leith Ross,

Thanks! Works great. Two comments:
- I had to replace H33, H8, H4, H32 by &H33, &H8, &H4, &H32 for my configuration
- Does this tool always take the ethernet card address, i.e. wireless card is neglected?

Thanks again,

g

Hello Gstremer,

Sorry by the "H". I forgot than when I used the code last I had the same problem. Thought I had saved the corrections. Won't happen again.

The code retrieves whatever the MAC for your default NIC card is. As long as the card is IEEE 802 compliant, it shouldn't matter if it is ethernet or wireless.

Sincerely,
Leith Ross

Leith Ross,

Thanks.

Unfortunately I found out that your macro does not return the MAC address of the ethernet card when the network cable is unplugged. Also even when the wireless network is connected the corresponding MAC address is not returned. Any idea?

Best regards,

g

Hello Gstremer,

I'll have to do a little research on your questions. I don't know that I will be able to answer your question today, but I will post what I find out.

Sincerely,
Leith Ross