Default File Location

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 ToolsOptionsGeneral Tab
and then changing the path in the "Default File Location"
text box?

I would greatly appreciate any help. Thanks.

Derek

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

On Mon, 26 Jan 2004 14:13:48 -0800, "Derek Ruesch"
wrote:

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 ToolsOptionsGeneral Tab
and then changing the path in the "Default File Location"
text box?

I would greatly appreciate any help. Thanks.

Derek

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

-----Original Message-----
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

On Mon, 26 Jan 2004 14:13:48 -0800, "Derek Ruesch"
wrote:

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 ToolsOptionsGeneral Tab
and then changing the path in the "Default File
Location"
text box?

I would greatly appreciate any help. Thanks.

Derek

.

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 wrote:

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

-----Original Message-----
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

On Mon, 26 Jan 2004 14:13:48 -0800, "Derek Ruesch"
wrote:

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 ToolsOptionsGeneral Tab
and then changing the path in the "Default File
Location"
text box?

I would greatly appreciate any help. Thanks.

Derek

.


--

Dave Peterson

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 ToolsOptionsGeneral 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

-----Original Message-----
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 wrote:

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

-----Original Message-----
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

On Mon, 26 Jan 2004 14:13:48 -0800, "Derek Ruesch"
wrote:

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 ToolsOptionsGeneral
Tab
and then changing the path in the "Default File
Location"
text box?

I would greatly appreciate any help. Thanks.

Derek

.


--

Dave Peterson

.

Derek

I know of no way to prevent the users from changing their default file
location in Excel ToolsOptionsGeneral.

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\E xcel\OptionsDefault Path

Gord Dibben Excel MVP

On Thu, 29 Jan 2004 12:53:12 -0800, "Derek Ruesch"
wrote:

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 ToolsOptionsGeneral 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

-----Original Message-----
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 wrote:

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

-----Original Message-----
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

On Mon, 26 Jan 2004 14:13:48 -0800, "Derek Ruesch"
wrote:

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 ToolsOptionsGeneral
Tab
and then changing the path in the "Default File
Location"
text box?

I would greatly appreciate any help. Thanks.

Derek

.


--

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.



Derek Ruesch wrote:

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 ToolsOptionsGeneral 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

-----Original Message-----
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 wrote:

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

-----Original Message-----
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

On Mon, 26 Jan 2004 14:13:48 -0800, "Derek Ruesch"
wrote:

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 ToolsOptionsGeneral
Tab
and then changing the path in the "Default File
Location"
text box?

I would greatly appreciate any help. Thanks.

Derek

.


--

Dave Peterson

.


--

Dave Peterson