Good practice for workbook referencing and storing files

[will_raz]

Hello!

I'm pretty new to VBA and was hoping someone could give me good practices for how to reference workbooks. My main concern is that if I write a lot of code that references different workbooks with a full file name path, if I were to have to move these workbooks to another folder, I would have to change all instances in my code where I call that reference path.

Is it possible to have a "source folder" that it can somehow look for? I wasn't able to find too much info on this, so any advice is much appreciated.

Thanks!

Will

 

[sastoka]

There are two option i can think of.

First one is still harcoding but with a Variable.

you defined a varible such as
filepath1 = the path to the workbook 1
filepath2 = the path to the workbook 2

and using when you need to specified the location of a certain file (with the help of a concatenate "&" ). In this case when you move your file, all you have to do is update the variable at the top of the macro and it will work.

the second option is to request the user to specified the location of the workbook and assign this path to a variable. that way you'll be certain to have the right workbook each time. but if your working with multiple workbook ( 5 or more) it will be probably be a hassle to locate the file each time the macro is run.

That's what I think would work the best but maybe someone will have a better option.

Regards.

 

[will_raz]

Thanks for your input! For now I will put it in a variable like you suggested. There's definitely more than 5 workbooks unfortunately, but hardcoding it will be much better.

Thanks for your help!

 

[AkaTrouble]

another option is to have a helper cell that contains the primary path and then in the code reference this cell to concatenate the complete filename and path statement

this could also be set as a global variable hard coded into the vba or on open workbook module or even a folder picker routine that could either be run when code activates or on open to set working folder variable

example of a folder picker function

Function GetFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Dim sInitDir As String
sInitDir = CurDir ' Store initial directory
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = strPath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
' Reset directory before exit
ChDrive sInitDir ' Return to the Initial Drive
ChDir sInitDir ' Resets directory for Initial Drive
GetFolder = sItem
Set fldr = Nothing
End Function