Close workbook when changing ip or mas address

[K0st4din]

Hi everyone,
I'm a bit new to this stuff and so I'm structuring my query as follows: Is there a way when I send someone a workbook with data information in it, when it's already downloaded to the user's computer, to somehow get their ip and mac addresses and secretly save them somewhere (perhaps in the workbook itself), as I said I have no idea how things will happen, but, If this user decides to forward this file to someone else, then the second person will not be able to open it and to close his workbook, i.e. He is not allowed to see this workbook. At least that's how I imagine it, and you can give me any other suggestions. Thanks in advance for the tips and suggestions!

 

[Logit]

Working macro code :

Option Explicit
   
'----------------------------------------------------------------------------
'This module contains 3 functions for determing the public IP, the local IP
'and the MAC address of the computer that runs those functions.

'Written By:    Christos Samaras
'Date:          22/11/2014

'Site:          http://www.myengineeringworld.net
'----------------------------------------------------------------------------
       
Function GetMyPublicIP() As String

    Dim HttpRequest As Object
   
    On Error Resume Next
    'Create the XMLHttpRequest object.
    Set HttpRequest = CreateObject("MSXML2.XMLHTTP")

    'Check if the object was created.
    If Err.Number <> 0 Then
        'Return error message.
        GetMyPublicIP = "Could not create the XMLHttpRequest object!"
        'Release the object and exit.
        Set HttpRequest = Nothing
        Exit Function
    End If
    On Error GoTo 0
   
    'Create the request - no special parameters required.
    HttpRequest.Open "GET", "http://myip.dnsomatic.com", False
   
    'Send the request to the site.
    HttpRequest.Send
       
    'Return the result of the request (the IP string).
    GetMyPublicIP = HttpRequest.ResponseText

End Function

Function GetMyLocalIP() As String

    'Declaring the necessary variables.
    Dim strComputer     As String
    Dim objWMIService   As Object
    Dim colItems        As Object
    Dim objItem         As Object
    Dim myIPAddress     As String
   
    'Set the computer.
    strComputer = "."
   
    'The root\cimv2 namespace is used to access the Win32_NetworkAdapterConfiguration class.
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
   
    'A select query is used to get a collection of IP addresses from the network adapters that have the property IPEnabled equal to true.
    Set colItems = objWMIService.ExecQuery("SELECT IPAddress FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
   
    'Loop through all the objects of the collection and return the first non-empty IP.
    For Each objItem In colItems
        If Not IsNull(objItem.IPAddress) Then myIPAddress = Trim(objItem.IPAddress(0))
        Exit For
    Next
   
    'Return the IP string.
    GetMyLocalIP = myIPAddress

End Function

Function GetMyMACAddress() As String

    'Declaring the necessary variables.
    Dim strComputer     As String
    Dim objWMIService   As Object
    Dim colItems        As Object
    Dim objItem         As Object
    Dim myMACAddress    As String
   
    'Set the computer.
    strComputer = "."
   
    'The root\cimv2 namespace is used to access the Win32_NetworkAdapterConfiguration class.
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
   
    'A select query is used to get a collection of network adapters that have the property IPEnabled equal to true.
    Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
   
    'Loop through all the collection of adapters and return the MAC address of the first adapter that has a non-empty IP.
    For Each objItem In colItems
        If Not IsNull(objItem.IPAddress) Then myMACAddress = objItem.MACAddress
        Exit For
    Next
   
    'Return the IP string.
    GetMyMACAddress = myMACAddress

End Function

Reference C3, C5, C7 data. Be certain to hide the "IP & MAC Address" worksheet from the end user. You can 'write' the data to a small text file and save it in an obscure location.
Write a macro that checks this data when the workbook opens. Proceed from there.

Example code/instructions for adding a macro to workbook. You'll edit the code to add the macro above and then run it.

How to dynamically add and run a VBA macro from Visual Basic - Microsoft Support

 

[K0st4din]

Hello, before I start the tests, I would like to ask the following things:
1. I read the given link and it said that I should choose a certain library according to the version of excel. - Am I to understand that this applies only while I prepare the file, because when I send it, I have no idea who is working with which office package?
2. I also saw that they say to save the given bas file in the C directory of my computer. So how will things happen on other people's computers, I don't have access to them? and
3. After I make the userform, which macro should I put in there or the one you shared with me? Sorry if my questions are dumb, but I don't understand it 100%.
I would be grateful if you could help me a little more!

 

[Logit]

1. I read the given link and it said that I should choose a certain library according to the version of excel. - Am I to understand that this applies only while I prepare the file, because when I send it, I have no idea who is working with which office package?

Yes, just on your computer.

2. I also saw that they say to save the given bas file in the C directory of my computer. So how will things happen on other people's computers, I don't have access to them?

You'll create a macro that stores the small text file on the other person's computer. It will most likely need to be on THEIR "C" drive as well.

3. After I make the userform, which macro should I put in there or the one you shared with me?

What USER FORM are you going to create and for what purpose ?

 

[K0st4din]

 

[Logit]

You'll paste the following macro (which is indicated in the instructions provided on the web page :

Private Sub Command1_Click()
      Dim oXL As Excel.Application
      Dim oBook As Excel.Workbook
      Dim oSheet As Excel.Worksheet
      Dim i As Integer, j As Integer
      Dim sMsg As String

    ' Create a new instance of Excel and make it visible.
      Set oXL = CreateObject("Excel.Application")
      oXL.Visible = True

    ' Add a new workbook and set a reference to Sheet1.
      Set oBook = oXL.Workbooks.Add
      Set oSheet = oBook.Sheets(1)

    ' Demo standard Automation from out-of-process,
    ' this routine simply fills in values of cells.
      sMsg = "Fill the sheet from out-of-process"
      MsgBox sMsg, vbInformation Or vbMsgBoxSetForeground

      For i = 1 To 100
         For j = 1 To 10
            sMsg = "Cell(" & Str(i) & "," & Str(j) & ")"
            oSheet.Cells(i, j).Value = sMsg
         Next j
      Next i

    ' You're done with the first test, now switch sheets
    ' and run the same routine via an inserted Microsoft Visual Basic
    ' for Applications macro.
      MsgBox "Done.", vbMsgBoxSetForeground
      Set oSheet = oBook.Sheets.Add
      oSheet.Activate

      sMsg = "Fill the sheet from in-process"
      MsgBox sMsg, vbInformation Or vbMsgBoxSetForeground

    ' The Import method lets you add modules to VBA at
    ' run time. Change the file path to match the location
    ' of the text file you created in step 3.
      oXL.VBE.ActiveVBProject.VBComponents.Import "C:\KbTest.bas"

    ' Now run the macro, passing oSheet as the first parameter
      oXL.Run "DoKbTest", oSheet

    ' You're done with the second test
      MsgBox "Done.", vbMsgBoxSetForeground

    ' Turn instance of Excel over to end user and release
    ' any outstanding object references.
      oXL.UserControl = True
      Set oSheet = Nothing
      Set oBook = Nothing
      Set oXL = Nothing

   End Sub