Hi,
How can I change the Excel Default file location to C:\My Folder when workbook1.xlsm is opened then
restore the user's orginal default location when workbook1.xlsm is closed?
Thanks again
Mohamed
Change Excel Default File Location
-
- Administrator
- Posts: 79561
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Change Excel Default File Location
Create code in the ThisWorkbook module:
Code: Select all
Private strDefaultPath As String
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DefaultFilePath = strDefaultPath
End Sub
Private Sub Workbook_Open()
strDefaultPath = Application.DefaultFilePath
Application.DefaultFilePath = "C:\My Folder"
End Sub
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Change Excel Default File Location
Thanks Hans, much appreciated.
Kind Regards
Mohamed
Kind Regards
Mohamed
-
- 5StarLounger
- Posts: 826
- Joined: 24 Jan 2010, 15:56
Re: Change Excel Default File Location
As a matter of interest, why would you want/need to do that?
Regards,
Rory
Rory
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Change Excel Default File Location
Hi Rory,
Sorry for the late reply.
Workbook on usb, will be used on different pc's or laptop - depends which is available.
Workbook, zips to default file path , exports ranges to deafult file path , clean-up after use is to delete files in default file path otherwise
workbook/files wil be on several pc's.
Don't want all data in original default file to be deleted (main user will be really upset) - so I thought best option is to change default file path.
Is that good practice?
Your criticism is welcomed & appreciated - will just make my learing curve shorter!
Thanks
Mohamed
Sorry for the late reply.
Workbook on usb, will be used on different pc's or laptop - depends which is available.
Workbook, zips to default file path , exports ranges to deafult file path , clean-up after use is to delete files in default file path otherwise
workbook/files wil be on several pc's.
Don't want all data in original default file to be deleted (main user will be really upset) - so I thought best option is to change default file path.
Is that good practice?
Your criticism is welcomed & appreciated - will just make my learing curve shorter!
Thanks
Mohamed
-
- 5StarLounger
- Posts: 826
- Joined: 24 Jan 2010, 15:56
Re: Change Excel Default File Location
What I meant was, why not specify the path for each of your operations rather than changing the default? That way, if your program crashes, or the user wants to use another workbook while yours is open, you haven't messed with their settings.
Regards,
Rory
Rory
-
- 5StarLounger
- Posts: 658
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Change Excel Default File Location
If the files are on a location relative to the location of the file with the macro, consider ThisWorkbook.Path to find out where that is and act accordingly.
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Change Excel Default File Location
Thank you Rory,
I did not consider the program crashing & the consequences.
Thank you Jan Karel - i will look into your suggestion.
I did not consider the program crashing & the consequences.
Thank you Jan Karel - i will look into your suggestion.