Default File Location

[Derek Ruesch]

I have a user who keeps changing the path of the default
file location. Is there a way to block edit access to the
default file location setting so that this user can't
change it?

It is critical that the default file location not be
changed because I have an Excel macro that looks into the
default file location for data and then imports it into a
spreadsheet. If the default file location changes, then
then my Excel macro doesn't find the data.

Also is there another way to change the default file
location without going into Tools>>Options>>General Tab
and then changing the path in the "Default File Location"
text box?

I would greatly appreciate any help. Thanks.

Derek

 

[Gord Dibben]

Derek

Rather than preventing the user from changing the path, you could check to see
what the path is then grab it to use somehow in your code.

Sub whatpath()
MsgBox "The current default file path is " & _
Application.DefaultFilePath
End Sub

Gord Dibben Excel MVP

 

[Derek Ruesch]

Gord,

My Excel program is distributed to many users who each
store the information that I want to grab in different
locations. That is why I set the default file location on
each user's computer to point to the location where the
data is that I need. Then my program opens the file that I
need from the default file location and copies the data
that I need from this file into a new excel file. If the
default file location changes, then I don't get the data
that I need. This is why it is imperative that this
default file location not change.

I use the following line of code to open the "MR.xls" file
from the user's default file location:

Workbooks.Open "MR.XLS"

The user is running Windows 2000 and Excel 2000. The
default file location refers to a folder on a networked
file server. The default file location reads "Z:/Test"
where the Z: drive is a drive mapped to my file server.

Thanks for the help.

Derek

 

[Dave Peterson]

You could just use the folder that contains your workbook (all of them are in
the same folder??)

Option Explicit
Sub testme()

Dim myPath As String
Dim myFileName As String

myFileName = ThisWorkbook.Path & "\" & "mr.xls"

If Dir(myFileName) = "" Then
'not found
MsgBox "where is it?"
Else
Workbooks.Open myFileName
End If

End Sub

 

[Derek Ruesch]

Unfortunately all of the users have these files in their
own private folder on a file server and the path and name
of this folder is different for each user. (The contents
of the files are different for each user but the filename
is the same). Getting back to my original question, is
there a way to disable access to the default file location
parameter so that the user can not change this? Also how
can the user change the default file location without
going into Tools>>Options>>General Tab and then changing
the default file location in the default file location
text box?

An example of the default file location for a user
is "Z:/Test" where Z: refers to a mapped network drive.

Thanks for the help.

Derek

 

[Gord Dibben]

Derek

I know of no way to prevent the users from changing their default file
location in Excel Tools>Options>General.

Since they are on server, perhaps your Administrator could set folder
permissions to zilch for all users with the exception of one folder. Not
being on a server or an Administrator, I have no clue.

Also, users can change the default folder location through editing the
Registry if allowed access to the Registry.

There's a thought....maybe you make the changes to the Registry to set all
users' folders as you wish.

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options>Default Path

Gord Dibben Excel MVP

 

[Dave Peterson]

I don't know of a way to stop this. And just as I wouldn't want a program to do
this to my settings, I wouldn't do it to others.

Maybe you could use a different technique.

You could look where you expected it. If you didn't find it, you could have the
user point to it.

Or each time they save the file, you could write its location to the registry.
Look for SaveSetting in VBA's help.

Then you could use GetSetting to retrieve the location.

But this, too, will fail if the user moves the file via windows Explorer.