Managing macros with hard coded file paths

[Klint4]

We have multiple macros that have been developed by various users in the company. Many of these macros have file paths hard coded into the macro to reference file locations with networked drives.

We are planning on making some changes to the network file structure which will break these macros. I realize we will have to go in and manually change all these, but it would be nice to learn from our mistake. I want to ensure that the end users have the flexibility they want, but not have to touch each macro if/when folder structures change, or files migrate to SharePoint etc.

What is the best way to manage these in the future?
ini files, master reference Excel file...,

Thank you in advance.

 

[Trevor G]

Welcome to the Forum, Do you know about Public Const as you can use this to retain the path for locations and much more and if you change things like servers you only have to update the Public Const and it will deal with everything else.

Example

Public Const strFilePath = "L:\Excel VBA\"

You then use the Const in code

Workbooks.Open strFilePath & "Excel Workbook Name.xls"

 

[Klint4]

So, create a module that is referenced by every macro created that requires file path information, include the Public Constants in that module. Good idea, keeps everything in the VBA development environment.

 

[Trevor G]

Yes easy to manage in one place and maintain.