DOS access from Excel

[Guest]

If anyone can help me with this I would really appreciate it. Is there a way
to access the computers MAC address from within VBA in Excel? I would like to
collect the MAC address when the user opens the file to prevent it from being
used outside the office.

Thanks

Frank Pytel

 

[Guest]

In a DOS CMD window you could enter:

ipconfig/all > c:\ip.txt

Excel VBA can do the same thing:


Sub BigMac()
x = Shell("cmd.exe /c ipconfig/all > c:\ip.txt", 1)
End Sub

VBA can then open the .txt file, read it and take action.

 

[Guest]

Gary;

That's cool. Thanks. I don't need it in a file though. What I would like to
do is set this up as a password. Ideally when the workbook is opened, VBA or
a macro would automatically gather the MAC address. This address would be
checked within a VBA array, (can you create an array in VBA, like JScript?),
against known MAC addresses. If it matches the spreadsheet opens, otherwise
it throws an error, message or shuts down.

What do you think "Gary"s Student"?

Thank You
Frank Pytel

 

[Guest]

I think you have a great idea. There is probably an API that can get this
MAC address directly. I'll rummage around and update the post tomorrow if I
find anything

 

[Guest]

Gary;

Thanks. That would be way cool.

Frank Pytel

 

[Steve Yandl]

You can take advantage of Windows Script Host for one method to return the
MAC for the PC. Try something like:

________________________________________

Sub GetMyMAC()
Set objShell = CreateObject("WScript.Shell")

Set objWshExec = objShell.Exec("ipconfig /all")

Set objStdOut = objWshExec.StdOut

Do Until objStdOut.AtEndOfStream
strLine = objStdOut.ReadLine
If InStr(strLine, "Physical Address") > 0 Then
arrText = Split(strLine, ":")
strIPaddress = arrText(1)
End If
Loop

MsgBox strIPaddress
End Sub

_______________________________________

Steve Yandl

 

[Steve Yandl]

It isn't really important in vbScript to set the objects to nothing but is
good practice in VBA. The sub posted above will work fine but it would be
better with the extra line I show below (plus I changed the variable name to
avoid confusion). You might also consider creating a 'Scripting.Dictionary"
object rather than an array to do the comparison as it has an 'Exists'
method that can make for faster checks than plowing through an array.

______________________________________

Sub GetMyMAC()
Set objShell = CreateObject("WScript.Shell")

Set objWshExec = objShell.Exec("ipconfig /all")

Set objStdOut = objWshExec.StdOut

Do Until objStdOut.AtEndOfStream
strLine = objStdOut.ReadLine
If InStr(strLine, "Physical Address") > 0 Then
arrText = Split(strLine, ":")
strMACaddress = arrText(1)
End If
Loop

MsgBox strMACaddress

Set objShell = Nothing

End Sub
______________________________________

Steve Yandl

 

[Guest]

Thanks Steve;

I am winding down with my 8 yr old. I'll try it tommorrow and let you know.

Thanks Again

Frank

 

[Steve Yandl]

You're welcome.

The Exec method of the shell object allows you to run command line programs
and retrieve output or errors. The StdOut property is a text stream that in
this case is the output of "ipconfig /all". We read the text stream one
line at a time identifying the one with the text "Physical Address" in it.
We use the 'split' method to create an array of strings from that line with
the colon being the separator and simply retrieve the second member of that
two member array.

Steve

 

[Guest]

I think you have a great idea. There is probably an API that can get this
MAC address directly. I'll rummage around and update the post tomorrow if I
find anything

It is possible to get a collection of MAC addresses for a computer using WMI
Win32_NetworkAdapter class MACAddress property, then compare that collection
with an array of MAC addresses:

Sub MACTest()

s = Timer

MACArray = Array _
("00:04:61:50:20:07" & _
"01:03:51:60:11:08" & _
"BB:02:12:57:21:09" & _
"CC:04:41:45:00:55")

WQLQuery = "Select * From Win32_NetworkAdapter"

Set objWMI = GetObject("winmgmts:root\cimv2")

Set colAdapters = objWMI.ExecQuery(WQLQuery)

For Each objAdapter In colAdapters
For Each MACAddress In MACArray
If MACAddress = objAdapter.MACAddress Then
'Debug.Print MACAddress, objAdapter.Name
End If
Next
Next

Debug.Print "WMI:", Timer - s

End Sub


I also modified Steve Yandl's code using objStdOut.ReadAll to store the
entire stdOut in a string and perform the same comparison:

Sub GetMyMAC()

s = Timer

MACArray = Array _
("00-04-61-50-20-07", _
"01-03-51-60-11-08", _
"BB-02-12-57-21-09", _
"CC-04-41-45-00-55")

Set objShell = CreateObject("WScript.Shell")
Set objWshExec = objShell.Exec("ipconfig /all")

Set objStdOut = objWshExec.StdOut
strIpConfig = objStdOut.ReadAll

For Each MACAddress In MACArray
If InStr(strIpConfig, MACAddress) Then
'Debug.Print MACAddress
End If
Next

Debug.Print "WshShell: ", Timer - s
End Sub


I then added code to your sample using FileSystemObject to read ip.txt into
a string:


Sub BigMac()

s = Timer

MACArray = Array _
("00-04-61-50-20-07", _
"01-03-51-60-11-08", _
"BB-02-12-57-21-09", _
"CC-04-41-45-00-55")

x = Shell("cmd.exe /c ipconfig/all > c:\ip.txt", 0)
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile("C:\ip.txt", 1)
strIpConfig = f.ReadAll

For Each MACAddress In MACArray
If InStr(strIpConfig, MACAddress) Then
'Debug.Print MACAddress
End If
Next

f.Close

Debug.Print "FSO:", Timer - s

End Sub


I ran all three subs a few times and it looks like that FSO method is the
fastest (about 15 times faster than WshShell sample and 20 times than WMI on
my machine) even with opening and reading a text file from the disk.

 

[Javier Amian]

I think it is much easier than what you guys are doing...

I would do the following:

1.- Get the MAC address of the router or server in your network and save
it to check against.
2.- Run a Shell in your VBA with like this:

Shell("cmd /c arp -a >c:\localarp.txt", 1)

this will create a file with the entries of the ARP table in the local
machine. The router or the server MAC address for the network were your
script is run will definitely be in this file.
3.- Search the file for the MAC address you saved in step 1.

Alternatively, I would hide another file in some shared drive in the
office network and have your script check if it exists.
Chances are if someone took the script home, this file will not be
found...

Njoy

 

[Guest]

I think it is much easier than what you guys are doing...

I would do the following:

1.- Get the MAC address of the router or server in your network and save
it to check against.
2.- Run a Shell in your VBA with like this:

Shell("cmd /c arp -a >c:\localarp.txt", 1)

this will create a file with the entries of the ARP table in the local
machine. The router or the server MAC address for the network were your
script is run will definitely be in this file.
3.- Search the file for the MAC address you saved in step 1.

Alternatively, I would hide another file in some shared drive in the
office network and have your script check if it exists.
Chances are if someone took the script home, this file will not be
found...

Njoy

I think it can be done that way too, but using ipconfig /all is probably
better because you can use it with standalone computers. I am no expert in
this, I was just interested in comparing different methods of getting MAC.
Maybe some other value could also be used for protecting workbooks, operating
system serial number or something else.

 

[Guest]

To All:

Thank you for all of your input. I have been scrambling lately so I haven't
had time to try to implement this. If anyone has, I know we would all love to
hear the results. This would be a great security feature for developers and
companies alike. I hope to have time this weekend to sit down and try all of
these wonderful suggestions.

Thanks Again

Frank Pytel