Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Restrict workbook to network

  Asked By: Ted    Date: Feb 03    Category: MS Office    Views: 1469
  

I have created a workbook for use on the network at work. What would be
the best way to restrict it's use so it only runs at work? I'm not sure
if there is a way to verify the network, so possibly verify the Drive
letter and name it resides on? Or create a roster of users and verify
the application username matches a name on the roster? This is probably
not the best method (would need to update the roster as people come and
go, and would also need to know the exact username IT has given that
person).

Share: 

 

28 Answers Found

 
Answer #1    Answered By: Eshe Chalthoum     Answered On: Feb 03

Well.. just about everything you described is POSSIBLE.
what would you PREFER.

ActiveWorkbook.Path will give you the PATH name of the file.
I use absolute pathnames instead of mapped drives because users
may have different drives mapped.
so, my shortcuts point to \\servername\pathname rather than
"G:\pathname"
In you macro, test for this path, then if doesn't match, exit sub.

If you want to match usernames, let me know and I'll locate the code for that.

 
Answer #2    Answered By: Waggoner Fischer     Answered On: Feb 03

All users  should have the same drive
mapped. This workbook  will reside on the network, on a specific drive
for our department (we have about a dozen drives specific to our
department), so that should be the same for everyone, correct? So in
that case, is the absolute pathname still the best? Or do you just
recommend doing it that way no matter what? We tried using the
servername in a Word template for something else, and could not get
it to work. So, if the servername is something like ismd002, the
drive letter is P, and the drive name is DataKinetics, how would you
specify the path? And then what would be the best way to test for
this in VBA?

 
Answer #3    Answered By: Davi Costa     Answered On: Feb 03

Another piece of code you might want to incorporate is this snippit
that checks the domain the user is logged in under:

Sub GetNetworkDomain()
Dim wsh
Set wsh = CreateObject("WScript.Network")
MsgBox wsh.UserDomain
End Sub

Drop that into a module, step into it until wsh is set, then pull up
"wsh" in a watch window to see what other properties are defined in
that object in your environment for more items to check on. I'm not
on a network  (at home at the moment), so I only show my computer's
name in that field. As I recall, when logging in on an network, it
populates with the domain the user logged into.

 
Answer #4    Answered By: Sydney Thompson     Answered On: Feb 03

I take it you're familiar with VBA modules:

in a code module, paste:
Sub test()
MsgBox "Path: " & ActiveWorkbook.Path & _
Chr(13) & "Name: " & ActiveWorkbook.Name & _
Chr(13) & "FullName: " & ActiveWorkbook.FullName
End Sub

then execute it.

It will show you what Excel sees as the name and path of the active workbook.
to make "double-sure" you COULD even test the ActiveWorkbook.FullName
to make sure that it is Exactly the file you're expecting them to use.

Like:

if (ActiveWorkBook.FullName <> "P:\user name\FileName.xls") then
msgbox "Copies of this file are not allowed"
exit sub
end if

 
Answer #5    Answered By: Kim Cruz     Answered On: Feb 03

Just a thought, although I admit I don't have the code to back it
up. :)

In our office, many users  have drives mapped using different letter
designations to the same servers. Files also move, but still stay on
the network.

Could you pull the domain name of the network  they are signed into
and match that with your network domain?

 
Answer #6    Answered By: Adelbert Fischer     Answered On: Feb 03

That's what I was referring to in my first response.
IF the shortcut used the absolute name
\\servername\pathname\filename.xls
then, it would be independent of the drive map.
and he could use activeworkbook.fullname or path.

Joe indicated that in HIS office, these drive maps are "fixed".
Where I am, with 1000 PC's, NOTHING is "fixed"!!!
but it sounds like Joe will be fine with the drive map.

 
Answer #7    Answered By: Bian Nguyen     Answered On: Feb 03

One more thing...this is saved as an Excel Template which the user
will open to enter data and then it will do a number of calculations.
It is saved as VancoKinetics7.XLT. This works fine if I right click
the icon and OPEN it. However, I want them to just click the icon,
which opens a NEW file. When I do this the Sub Test() you suggested
doesn't work. It doesn't give me a Path, the Name is VancoKinetics71,
and the FullName is VancoKinetics71 (same as the name, without a
Path). Since there is no Path in the FullName, it will run no matter
where it is opened from. In Word there is difference between
Document_New and Document_Open. In Excel there is only a
Workbook_Open choice (no WorkBook_New). Does this make a difference
in how the workbook  is started (New vs Open)? If so, how do I verify
that it is only running on the specified network  path if they just
click the icon and open a NEW workbook? I don't want someone to copy
this template to, say, a flashdrive and install it somewhere else.

 
Answer #8    Answered By: Daniel Jones     Answered On: Feb 03

To check if a drive is on the network  you can use the DriveType
property (3 for network) of the drive object along the following lines.

Sub ShowDriveList()
Dim objDrive As Object
Dim msg As String
For Each objDrive In CreateObject
("Scripting.FileSystemObject").Drives
Select Case objDrive.DriveType
Case 0: msg = msg & vbNewLine & objDrive.DriveLetter & ":
Unknown"
Case 1: msg = msg & vbNewLine & objDrive.DriveLetter & ":
Removable Drive"
Case 2: msg = msg & vbNewLine & objDrive.DriveLetter & ":
Hard Disk Drive"
Case 3: msg = msg & vbNewLine & objDrive.DriveLetter & ":
Net work  Drive"
Case 4: msg = msg & vbNewLine & objDrive.DriveLetter & ":
CD-ROM Drive"
Case 5: msg = msg & vbNewLine & objDrive.DriveLetter & ":
RAM Disk Drive"
End Select
Next
MsgBox msg
End Sub

To restrict usage, you could use the Workbook_Open event to check the
drive type and if not = 3, close the file with "Network Only...."
message.

 
Answer #9    Answered By: Mercedes Andrews     Answered On: Feb 03

To check if a drive is on the network  you can use the DriveType
property (3 for network) of the drive object along the following lines.

Sub ShowDriveList()
Dim objDrive As Object
Dim msg As String
For Each objDrive In CreateObject
("Scripting.FileSystemObject").Drives
Select Case objDrive.DriveType
Case 0: msg = msg & vbNewLine & objDrive.DriveLetter & ":
Unknown"
Case 1: msg = msg & vbNewLine & objDrive.DriveLetter & ":
Removable Drive"
Case 2: msg = msg & vbNewLine & objDrive.DriveLetter & ":
Hard Disk Drive"
Case 3: msg = msg & vbNewLine & objDrive.DriveLetter & ":
Net work  Drive"
Case 4: msg = msg & vbNewLine & objDrive.DriveLetter & ":
CD-ROM Drive"
Case 5: msg = msg & vbNewLine & objDrive.DriveLetter & ":
RAM Disk Drive"
End Select
Next
MsgBox msg
End Sub

To restrict usage, you could use the Workbook_Open event to check the
drive type and if not = 3, close the file with "Network Only...."
message.

 
Answer #10    Answered By: Fuzairah Neeman     Answered On: Feb 03

Regarding rostering people who can see this file, this is also possible.
Whenever a person gets access to lan they have a user name (either logging
through windows envoirnement, Novell, citrix etc). You can write a code to check
the user name and have a list of people who should have access on a seperate
sheet. This way if the user name (log in name) matches what is written on the
tab, then you would be able to restrict people to see the file.

You can also give access level so they can edit the file or not.

 
Answer #11    Answered By: Brandi Ramirez     Answered On: Feb 03

thanks for all of the help so far. I would rather not save it as
an .XLS and run it from that. We had one previously with the formulas
in the cells. It was always getting corrupted, or someone would save
changes when closing and the workbook  would keep the values they had
input. That could lead to errors if someone wasn't careful and left
an old value somewhere. So I did this to clean up the worksheets, let
the program make the decisions on which formula to use based on user
input, hide the formulas so they could not get corrupted and make
data input easier through UserForms. I would prefer to have it
accessed as an .XLT. So, that seems to leave out the verify pathname
option to prevent it's unauthorized use (outside of our department).
I'm thinking using a roster to match the Excel username against the
roster might be the way to go. Would you recommend adding another
worksheet and list them there or have a separate workbook? I'm new to
this, so I've never pulled the data from another workbook. Either
way, what would be the best way to verify the Excel username against
the roster?

 
Answer #12    Answered By: Archie Parker     Answered On: Feb 03

I have been watching this particular thread half-heartedly, but I can't
help but wonder why you cannot achieve your goals with the (XLT)?! The
path will always point to the XLT regardless of the name assigned to the
XLS workbook  created. So even though a number 1 or 2, etc is assigned to
the workbook upon template execution, the named template will still be
in the same location, so the check can still work. Unless I am missing
something, this appears to be the case.

 
Answer #13    Answered By: Hamdan Younis     Answered On: Feb 03

you're right, it will work  if they right-click the template and
choose OPEN from the menu, as opposed to left-clicking the template
icon, which creates a NEW workbook  from the template and just opens
without having to choose from a menu (which is how people are used to
opening something). And as someone else pointed out, since the newly
created workbook has not been saved anywhere, there is no fullpath to
check against (and when I showed the message box with this info, this
was proven: the path was blank). I could instruct them to right-click
and choose OPEN instead, but then if someone does left-click, the
check will not work. And, I am trying to make this as easy as
possible. One thought I had today, and I think I have seen something
about it in the past, is there a way to change the default left-click
event from NEW to OPEN? That way someone could still just left-click,
but now it will OPEN the .XLT instead of create  NEW .XLS?

 
Answer #14    Answered By: Laaibah Malik     Answered On: Feb 03

I still can't help but think that you are making this harder than it
seems. Your objective is to ensure the program is coming from your
network, or at least that the user is connected to the network.
Regardless, the template (XLT) will reside in a specific location on the
network. When the program opens, it checks for the XLT file NOT the
newly opened XLS file. Since the XLT is already on the network  you can
check for it, and if it is there then run the program.

Another option is to automatically save the newly opened XLS file to a
specific location on the network upon the file being opened. If the
network is not available you can shut the program down.

 
Answer #15    Answered By: Daw Boonliang     Answered On: Feb 03

Put a file with a silly name in a known network  location. Make it read only.
In your workbook-open sub check that the silly file exists at the right
location. If it does not exist, then close the workbook  (with or without rude
message).

 
Answer #16    Answered By: Christie Bradley     Answered On: Feb 03

my first thought when reading your reply was "pure genius". I like
the way you think, even the part about the message with rude comments
(I tend to use those). What do you recommend as the best way to do
this? Searching the internet I've seen a few different ways - is one
way better than others, or is it just personal preference? I know
there are usually a number of different ways to do a certain task in
VBA; I'm not familiar enough to know the best.

 
Answer #17    Answered By: Wendy Harrison     Answered On: Feb 03

Before I get to that, please indulge me with a thought on the XLT versus XLS
issue.

I would definitely use an XLS and make it read only (the file setting) This
will ensure your users  cannot overwrite it with their stuff.

There will only be one way to open it and it will need to be saved under
another name.

Put a macro in the Workbook_open event like this:

Put a file named Windows.txt in F:\CommonFiles\ (or in any directory which
will be mapped the same for all users and change the path in the macro). Then
right click the file, select properties, and make it read only. In fact make
it hidden as well as this will make it much more difficult for them to copy
it.

Don't try it on the final version until you have checked that it works for you
and you are able to open the file again. I forgot the line "Exit Sub" and was
unable to reopen the file so I had to recreate it.

I have chosen the name Windows.txt as something that is unlikely to attract
attention. If you call it "Joes File to stop the excel thingy working.txt" it
will work  just as well but they might wonder why it is there.

Option Explicit

Private Sub Workbook_Open()
On Error GoTo ErrHandler
Dim myFile As String
myFile = "F:\CommonFiles\Windows.txt"
Open myFile For Input As #1
Close #1
Exit Sub

ErrHandler:
MsgBox "You must be connected to the network  to use this workbook"
ThisWorkbook.Close False

End Sub

 
Answer #18    Answered By: Noah Evans     Answered On: Feb 03

Yes, I've used "rostering". However, I'm in a unique position in that
I am an Oracle DBA, so I can interrogate the Oracle DB and determine
what roles the user is assigned to determine their authorizations dynamically.
Meaning that once the user is set up by the Security Admin people, they
AUTOMATICALLY are authorized to execute my application.
I don't have to maintain a separate worksheet with the list of authorized users.
Mainly because in my case, trying to keep 180 names up-to-date in each of
10 (or so) applications would be a maintenance nightmare!

 
Answer #19    Answered By: Candace Foster     Answered On: Feb 03

Give it a shot.
Just create  a NEW XLS file.
Create a Workbook_Open subroutine
and have it display the fullname (activeworkbook.fullname)
then save it as a template and exit.

Now, double-click on the template.
What happens is that you open a COPY of the template, not the actual template.
even though the activeworkbook.NAME is the same as the template (including the
.xlt)

I think it's a function of the intended use of templates.
By definition, a TEMPLATE is supposed to provide the FRAMEWORK for a new file.
Therefore, The Exalted Ones at Mt. Olympus (er... Microsoft) decreed that
you're not really "opening" the template file, but opening a "copy" of the
template file.
But the copy doesn't seem to retain a record of where it came from.

If you can find a way around this, we shall sing your praises...
er.. maybe not.. you don't want to hear me sing.. that's why I'm a programmer!

 
Answer #20    Answered By: Jo Fowler     Answered On: Feb 03

I'm a recording artist/songwriter, so I can
pitchhit for you on the singing part . . .

 
Answer #21    Answered By: Blaze Fischer     Answered On: Feb 03

American Idol is on prime time, he can't possibly be
worse, could he?

 
Answer #22    Answered By: Pam Harrison     Answered On: Feb 03

no kidding, on that one . . . you're right, it couldn't be
worse.

 
Answer #23    Answered By: Shannon Hughes     Answered On: Feb 03


I've used the file name technique.
It worked great, until I decided to archive anyting that hadn't been modified
for in the past year. It got archived.
Then, when the application  quit working, I had to revist a program that I hadn't
had to touch for over a year in order to find out what happened.

so I changed the name of the file to: DO_NOT_DELETE.txt
then, I was sure to put some comments describing what it was for.

It's not perfect, but I haven't archived it again either.

 
Answer #24    Answered By: Clinton Edwards     Answered On: Feb 03

I haven't seen an answer to your question about default left-click actions, so
here it goes.

Open "My Computer"
Select Tools->Folder Options
Click the "File Types" tab.
Locate the XLT extension (if you type "X", it'll take you there quicker)
Click the 'Advanced' button.
Select the "Open" action.
Select the "Set Default" button
Select OK/Close as often as necessary to get out.

this will change the default action all of XLT files

As an alternative, you can use a Visual Basic Script to launch the file instead.
The VBScript can do the checking, and to ensure that you only launch the file
from the script, I'll have to look at an example I did several years ago, but I
believe
you can pass a command-line option to the XLT that you can test for.

 
Answer #25    Answered By: Adelaide Fischer     Answered On: Feb 03

I haven't seen an answer to your question about default left-click actions, so
here it goes.

Open "My Computer"
Select Tools->Folder Options
Click the "File Types" tab.
Locate the XLT extension (if you type "X", it'll take you there quicker)
Click the 'Advanced' button.
Select the "Open" action.
Select the "Set Default" button
Select OK/Close as often as necessary to get out.

this will change the default action all of XLT files

As an alternative, you can use a Visual Basic Script to launch the file instead.
The VBScript can do the checking, and to ensure that you only launch the file
from the script, I'll have to look at an example I did several years ago, but I
believe
you can pass a command-line option to the XLT that you can test for.

 
Answer #26    Answered By: Blake Smith     Answered On: Feb 03


Oh, I missed that part. Well that makes it all the different and makes
this request a far greater challenge.


 
Answer #27    Answered By: Ryder Anderson     Answered On: Feb 03

Actually, what Joe WROTE was ...
"...how do I verify that it is only running on the specified network
path..."
... and ...
"I don't want someone to copy this template to, say, a flashdrive and
install it somewhere else."

Now, only Joe knows what he means, but I agree with Craig that this
discussion is trying to find a complicate solution a simple task.

I understood the original request to be ...
"What would be the best way to restrict it's use so it only runs  at work?"

To that, I offered the way in VBA to test the network  domain the user
is logged into, which will restrict the user to be on the company's
domain to *run* the macro. It's impossible to prevent someone from
taking the file somewhere else, but preventing the execution was the
task at hand, as I understood it.

That said, I'm not a professional VBA coder, I only use it
occasionally to solve problems or automate tasks I can't otherwise
solve in Excel. Thus my point of view may not be inclusive enough to
understand the real question.

 
Answer #28    Answered By: Angelica Ramos     Answered On: Feb 03

thanks for all of the replies recently. Sorry I haven't responded
sooner, but my wife just gave birth to our son on Sunday, so I've
been a little busy. That said let me answer one of the basic
questions someone brought up:
The purpose of restricting this Excel workbook  was to prevent it's
use somewhere else. This is for 2 reasons:
1. I put a lot of time into this and I could say I feel somewhat
selfish about giving away all of my work  for free. Granted, until 4
weeks ago I never even recorded a macro, let alone written any VBA
code. I'm sure you guys could have done what I did in much less time,
but...
2. This workbook is used to calculate starting doses of IV
Vancomycin in critically ill patients. This leads to the question of
liability if someone outside of our hospital uses it and something
happens.
Those are the reasons for wanting to limit (impossible to prevent)
it's unauthorized distribution and use. After trying different
things, I decided on using many of the solutions you guys have
proposed. I opened the .XLT file and then saved it as an .XLS file on
the network. Then I made that .XLS file read-only to prevent changes
and password protected the VBA code. This allowed me to verify the
pathname when it is opened, and if not on the correct path, then it
closes. I tried this by moving it off the network  drive onto a folder
on the computer. When I ran it from there, it closed as intended. So
that works fine. I might still add the "check if this file exists in
this location" also as a second verification (never know if IT will
create the same Drive, etc at another location). So for now that
problem is taken care of. If I have any problems with the "check if
file exists", I'll be sure to let you know. And based on what I want
to accomplish from above, does this appear to be a satisfactory
solution, or would someone recommend another way? Your help is always
appreciated.

 
Didn't find what you were looking for? Find more on Restrict workbook to network Or get search suggestion and latest updates.




Tagged: