MAC Address

  • Thread starter Thread starter sparx
  • Start date Start date
S

sparx

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.
 
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.
Click to expand...

Why not just lock the spreadsheet:

Tools --> Options --> Security
 
I have locked actual sheets & so - what I need to do is stop people fro
using excel files even if they get the file - so instead of putting
password on the actual file, I want some way of stopping all th
formula's working within a file so if I can read their MAC address an
I write a code that uses their MAC address and another password, the
the file is specific th their PC and stops them sharing the files wit
others and passing the passwords I supply on - it also lets me know wh
has the file because they will request a password from me
 
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
 
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
 
Ps. You may want to add an:

application.calculate

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