If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Default File Location
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 |
#3
|
|||
|
|||
Default File Location
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 . |
#4
|
|||
|
|||
Default File Location
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 |
#5
|
|||
|
|||
Default File Location
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 . |
#6
|
|||
|
|||
Default File Location
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 . |
#7
|
|||
|
|||
Default File Location
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 |
Thread Tools | |
Display Modes | |
|
|