Change Excel Default File Location

[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

[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

[MSingh]

Thanks Hans, much appreciated.

Kind Regards
Mohamed

[rory]

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

[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

[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.

[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.

[MSingh]

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

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