Closed Thread
Results 1 to 8 of 8

MAC Address

  1. #1
    Forum Contributor
    Join Date
    11-23-2004
    Location
    Bristol
    MS-Off Ver
    2016
    Posts
    225

    Question MAC Address

    Please can anybody help - I want to stop people running excel files of mine on their computers on my network - so i am asking if somebody knows of a VBA code that when they open my files, sheet1 will display their MAC address of their PC so I can make a formula that compares my MAC address with a password that will stop formula's working within my files if the MAC addresses dont match.

  2. #2
    Mangus Pyke
    Guest

    Re: MAC Address

    On Mon, 18 Jul 2005 13:31:05 -0500, sparx
    <[email protected]> wrote:
    >Please can anybody help - I want to stop people running excel files of
    >mine on their computers on my network - so i am asking if somebody
    >knows of a VBA code that when they open my files, sheet1 will display
    >their MAC address of their PC so I can make a formula that compares my
    >MAC address with a password that will stop formula's working within my
    >files if the MAC addresses dont match.


    Why not just lock the spreadsheet:

    Tools --> Options --> Security
    --
    "Learning is a behavior that results from consequences."
    B.F. Skinner

  3. #3
    Forum Contributor
    Join Date
    11-23-2004
    Location
    Bristol
    MS-Off Ver
    2016
    Posts
    225
    I have locked actual sheets & so - what I need to do is stop people from using excel files even if they get the file - so instead of putting a password on the actual file, I want some way of stopping all the formula's working within a file so if I can read their MAC address and I write a code that uses their MAC address and another password, then the file is specific th their PC and stops them sharing the files with others and passing the passwords I supply on - it also lets me know who has the file because they will request a password from me.

  4. #4

    Re: MAC Address

    I believe that www.freevbcode.com would have an easy vb way to get to
    your MAC address from a simple API Call (a VBA function)

    from there you can just put your code on application_startup or
    workbook_open or something along those lines

    proabbly wont work if they hit disable macros-- but who knows

    -aaron


  5. #5
    Dave Peterson
    Guest

    Re: MAC Address

    I searched the *scripting* newsgroup for "Mac address" and found some code to
    borrow. The script I found worked with my version of windows (XP home). I'm
    not sure if nbtstat is available on all versions of windows.


    Please Login or Register  to view this content.
    You could even make it so that that function actually does something important.
    (You could have users who are smart enough to figure out what that function
    "adds" to the formula.)

    Ps. Even if you do all this, you'll have to protect the project.

    Inside the VBE:
    Tools|vbaproject properties|protection tab

    But this protection is can be broken pretty easily--but it'll stop most.

    ==========
    ps. if your version of windows doesn't have nbtstat, then may be able to use an
    alternate method.

    This also displayed that MAC address for me (winXP home):

    Option Explicit
    Sub testme01()

    Dim strComputer As String
    Dim objWMIService As Object
    Dim colAdapters As Object
    Dim objAdapter As Object

    strComputer = "."
    Set objWMIService = GetObject _
    ("winmgmts:" & "!\\" & strComputer & "\root\cimv2")
    Set colAdapters = objWMIService.ExecQuery _
    ("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    For Each objAdapter In colAdapters
    MsgBox "Physical address: " & objAdapter.MACAddress
    Next objAdapter
    End Sub[/CODE]

    But I don't know when WMI became available.

    ====

    You may want to post back to one of the Scripting newsgroups and explain what
    you want. And include what version of Windows you're running. (Or even search
    google!)

    ==========

    You could even use the computer name (instead of MAC Address):

    Option Explicit
    Sub testme()

    Dim WSH As Object
    'Dim WSH As IWshRuntimeLibrary.WshNetwork

    Set WSH = CreateObject("WScript.network")
    'Set WSH = New IWshRuntimeLibrary.WshNetwork

    MsgBox WSH.ComputerName
    MsgBox WSH.UserName

    End Sub




    sparx wrote:
    >
    > Please can anybody help - I want to stop people running excel files of
    > mine on their computers on my network - so i am asking if somebody
    > knows of a VBA code that when they open my files, sheet1 will display
    > their MAC address of their PC so I can make a formula that compares my
    > MAC address with a password that will stop formula's working within my
    > files if the MAC addresses dont match.
    >
    > --
    > sparx
    > ------------------------------------------------------------------------
    > sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
    > View this thread: http://www.excelforum.com/showthread...hreadid=388084


    --

    Dave Peterson
    Last edited by zbor; 07-17-2012 at 04:45 AM. Reason: Code tags

  6. #6
    Dave Peterson
    Guest

    Re: MAC Address

    Ps. You may want to add an:

    application.calculate

    right before the "end sub" of the auto_open procedure.

    sparx wrote:
    >
    > Please can anybody help - I want to stop people running excel files of
    > mine on their computers on my network - so i am asking if somebody
    > knows of a VBA code that when they open my files, sheet1 will display
    > their MAC address of their PC so I can make a formula that compares my
    > MAC address with a password that will stop formula's working within my
    > files if the MAC addresses dont match.
    >
    > --
    > sparx
    > ------------------------------------------------------------------------
    > sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
    > View this thread: http://www.excelforum.com/showthread...hreadid=388084


    --

    Dave Peterson

  7. #7
    Registered User
    Join Date
    07-17-2012
    Location
    Qatar
    MS-Off Ver
    Excel 2003
    Posts
    1

    Thumbs up Re: MAC Address

    Hi Dave,

    Thanks for the code. When I execute the code I am getting the file not found error. I think the nbstat.txt file is not creating as per the code.
    Please help me to solve this and get the MAC address.

    regards,
    Leo GK

    Quote Originally Posted by Dave Peterson View Post
    I searched the *scripting* newsgroup for "Mac address" and found some code to
    borrow. The script I found worked with my version of windows (XP home). I'm
    not sure if nbtstat is available on all versions of windows.


    Option Explicit
    Public OKMACAddr As Boolean

    Function GetMACAddress()

    Dim Net As Object
    Dim SH As Object
    Dim FSO As Object
    Dim TS As Object
    Dim Data As String
    Dim MACAddress As String

    Set Net = CreateObject("wscript.network")

    Set SH = CreateObject("wscript.shell")
    SH.Run "%comspec% /c nbtstat -a " _
    & Net.computername & " > c:\nbtstat.txt", 0, True
    Set SH = Nothing
    Set Net = Nothing

    Set FSO = CreateObject("scripting.filesystemobject")
    Set TS = FSO.opentextfile("c:\nbtstat.txt")
    MACAddress = ""
    Do While Not TS.AtEndOfStream
    Data = UCase(Trim(TS.readline))
    If InStr(Data, "MAC ADDRESS") Then
    MACAddress = Trim(Split(Data, "=")(1))
    Exit Do
    End If
    Loop

    TS.Close
    Set TS = Nothing
    FSO.deletefile "c:\nbtstat.txt"
    Set FSO = Nothing
    GetMACAddress = MACAddress
    End Function

    Sub auto_open()

    Dim myMACAddr As String

    myMACAddr = Application.Clean(GetMACAddress)

    Select Case UCase(myMACAddr)
    Case Is = "##-##-##-##-##-##", "##-##-##-##-##-##"
    'ok
    OKMACAddr = True
    Case Else
    OKMACAddr = False
    MsgBox "Not authorized to use this workbook!"
    'uncomment this line, save your work before you test it.
    'thisworkbook.Close savechanges:=false
    End Select

    End Sub

    But if users open your workbook with macros disabled (or stop the auto_open from
    firing), then this won't help.

    One way around it is to create a userdefined function that checks to make sure
    that they're valid users. Then you can sprinkle your UDF into formulas that
    will blow up if they're not authorized.

    A really basic example. Say you have some formulas that return numbers:
    =sum(a1:a99)

    Change it to look like:
    =sum(a1:a99)+myfunc()

    And put this a general module in that project.

    Function myFunc()
    If OKMACAddr Then
    myFunc = 0
    Else
    myFunc = CVErr(xlErrRef)
    End If
    End Function

    You could even make it so that that function actually does something important.
    (You could have users who are smart enough to figure out what that function
    "adds" to the formula.)

    Ps. Even if you do all this, you'll have to protect the project.

    Inside the VBE:
    Tools|vbaproject properties|protection tab

    But this protection is can be broken pretty easily--but it'll stop most.

    ==========
    ps. if your version of windows doesn't have nbtstat, then may be able to use an
    alternate method.

    This also displayed that MAC address for me (winXP home):

    Option Explicit
    Sub testme01()

    Dim strComputer As String
    Dim objWMIService As Object
    Dim colAdapters As Object
    Dim objAdapter As Object

    strComputer = "."
    Set objWMIService = GetObject _
    ("winmgmts:" & "!\\" & strComputer & "\root\cimv2")
    Set colAdapters = objWMIService.ExecQuery _
    ("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    For Each objAdapter In colAdapters
    MsgBox "Physical address: " & objAdapter.MACAddress
    Next objAdapter
    End Sub

    But I don't know when WMI became available.

    ====

    You may want to post back to one of the Scripting newsgroups and explain what
    you want. And include what version of Windows you're running. (Or even search
    google!)

    ==========

    You could even use the computer name (instead of MAC Address):

    Option Explicit
    Sub testme()

    Dim WSH As Object
    'Dim WSH As IWshRuntimeLibrary.WshNetwork

    Set WSH = CreateObject("WScript.network")
    'Set WSH = New IWshRuntimeLibrary.WshNetwork

    MsgBox WSH.ComputerName
    MsgBox WSH.UserName

    End Sub




    sparx wrote:
    >
    > Please can anybody help - I want to stop people running excel files of
    > mine on their computers on my network - so i am asking if somebody
    > knows of a VBA code that when they open my files, sheet1 will display
    > their MAC address of their PC so I can make a formula that compares my
    > MAC address with a password that will stop formula's working within my
    > files if the MAC addresses dont match.
    >
    > --
    > sparx
    > ------------------------------------------------------------------------
    > sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
    > View this thread: http://www.excelforum.com/showthread...hreadid=388084


    --

    Dave Peterson

  8. #8
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: MAC Address

    leogk, this is a 7 years old post and Dave is incative.

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1