Changing folder reference in VBA for Excel 2

[Marita]

I have a macro that updates numerous files. The file paths are recorded in the code. However, the folder name changes monthly(Oct, Nov, etc). How can I best write a macro that will edit/replace the folder name?

Any help will be appreciated.
Marita

 

[VBAjedi]

The easiest thing would be to not put the folder name in the code!

Instead, you can prompt the user for it via an inputbox. Or, if you don't want the user to have to do anything, you can store a list of directory paths on a hidden sheet. Or, if the folder naming format is always followed exactly, you could generate the folder path dynamically by checking the current date.

VBAjedi

 

[Marita]

Thank you very much for your reply, Jedi!

I tried not including the path in the code, and it seemed to work today on the LAN (I think, I only tried it with one module), but did not work on my laptop at home tonight.

If the input box is the answer, any help on how I can do that?



Marita

 

[tbl]

I think that VBAjedi's suggestion was very sound. I do know that if you do not precisely specify the path EVERY time that VBA gives a save or read instruction, you will never get consistent operation.
Why don't you use something like this..

MainPath = "C:\Folder"
SubPath = "Month" & "Year"
FullPath = Mainpath & SubPath

You would have to derive Month and Year of course to suit yourself, but each time that you need the path you just use Fullpath.

Richard

 

[VBAjedi]

The input box would be ideal to get any filepath or part of a filepath that may not always be the same (like when you're on different computers that don't have identical directory structures, or when the file is saved on a network drive that is accessed with a different path depending on whether you are at work or at home). The biggest drawback to this approach is that your user has to be computer-savvy enough to know or be able to find the correct path. If in most cases the path will be the same, you can make that the default to make it easier. To build on tbl's code, here's an inputbox that guesses the user wants the current month's folder, but lets them change it:

x = Format(Date, "mmm") & "\" ' Guess they want current month's folder
MainPath = "C:\My Folder\"
FullPath = InputBox("What directory is this file stored in?", "Box Title", MainPath & x)
FileName = "MyFile.xls"
FilePath = FullPath & FileName

You could also load the MainPath and FileName variables from named ranges or textboxes on your worksheet so your user could set their "defaults" that will appear automatically every time.

VBAjedi

 

[Marita]

Thanks to both of you, that is very helpful.

Stars to you!