Change Excel Default File Location

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Change Excel Default File Location

Post by MSingh »

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

User avatar
HansV
Administrator
Posts: 79561
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Change Excel Default File Location

Post by HansV »

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

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Re: Change Excel Default File Location

Post by MSingh »

Thanks Hans, much appreciated.

Kind Regards
Mohamed

User avatar
rory
5StarLounger
Posts: 826
Joined: 24 Jan 2010, 15:56

Re: Change Excel Default File Location

Post by rory »

As a matter of interest, why would you want/need to do that?
Regards,
Rory

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Re: Change Excel Default File Location

Post by MSingh »

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

User avatar
rory
5StarLounger
Posts: 826
Joined: 24 Jan 2010, 15:56

Re: Change Excel Default File Location

Post by rory »

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

User avatar
Jan Karel Pieterse
5StarLounger
Posts: 658
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Change Excel Default File Location

Post by Jan Karel Pieterse »

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.
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Re: Change Excel Default File Location

Post by MSingh »

Thank you Rory,
I did not consider the program crashing & the consequences.

Thank you Jan Karel - i will look into your suggestion.