Thanks. That would be way cool.
Frank Pytel
"Gary''s Student" wrote:
> 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
> --
> Gary''s Student - gsnu200739
>
>
> "Frank Pytel" wrote:
>
> > 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
> >
> > "Gary''s Student" wrote:
> >
> > > 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.
> > >
> > > --
> > > Gary''s Student - gsnu200739
> > >
> > >
> > > "Frank Pytel" wrote:
> > >
> > > > 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
________________________________________
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
"Frank Pytel" <fpy...@sc.rr.com.do.not.spam.jerk> wrote in message
news:307C7737-C450-4A8B...@microsoft.com...
______________________________________
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
"Steve Yandl" <syandl...@comcast.net> wrote in message
news:7MqdndKy45FHylLb...@comcast.com...
I am winding down with my 8 yr old. I'll try it tommorrow and let you know.
Thanks Again
Frank
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
"Frank Pytel" <fpy...@sc.rr.com.do.not.spam.jerk> wrote in message
news:09980CAD-D9EB-4A4A...@microsoft.com...
> 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.
--
urkec
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.
--
Gary''s Student - gsnu200739
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
*** Sent via Developersdex http://www.developersdex.com ***
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
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.
--
urkec
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