1

I have an excel file which has lot of formulas for calculation written in macros. I want this excel file to run in only particular computer/ laptop. I want to manage list of computers who can access this excel file.

If the file gets copied to any other system, it must not open. Precisely, we check check for MAC address of that system who is authorised to access.

Please provide a solution.

javacoder
  • 61
  • 4
  • Read [**How do I ask a good question?**](https://stackoverflow.com/help/how-to-ask) As currently posted, this question is almost certainly going to get closed as off-topic or too broad. – Andrew Henle Jul 30 '19 at 10:49

1 Answers1

0

In Excel VBA, you will need a module, you can call it whatever. One public function to set the toggle like so. You will need to set the Constant 'test' to the mac address of the PC you want to allow access for

Public Function notmacaddress()

Dim m As String
Const test = "MM:MM:MM:SS:SS:SS"

m = GetMACAddress

If m <> test Then
    notmacaddress = True
Else
    notmacaddress = False
End If

End Function

And a private function to get the mac address of the PC attempting to open the workbook

Function GetMACAddress() As String
    Dim sComputer As String
    Dim oWMIService As Object
    Dim cItems As Object
    Dim oItem As Object
    Dim myMacAddress As String

    sComputer = "."

    Set oWMIService = GetObject("winmgmts:\\" & sComputer & "\root\cimv2")

    Set cItems = oWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")

    For Each oItem In cItems
        If Not IsNull(oItem.IPAddress) Then myMacAddress = oItem.macAddress
        Exit For
    Next
    'it will return mac address in format MM:MM:MM:SS:SS:SS
    GetMACAddress = myMacAddress

End Function

Then in the open event of the workbook add the code

Private Sub Workbook_Open()

If notmacaddress Then
    ThisWorkbook.Close
End If

End Sub

Oh and reference to the Get mac address function here

SlappedProgrammer
  • 194
  • 1
  • 11