Hard-Coding a File Location

I

ibeetb

I have Excel files in a directory on the network that I want to be updated
even if they are moved WITHIN the SAME directory but say.....in another
subfolder. Right now, they are dependent on each other so I have the
location of them hard-coded . But if I move ONE, Excel wont be able to find
it. How can I not hard-code the location but still have them be able to find
each other?
 
C

chris

Your gonna have use a filesearch in your code like this

Dim FCnt As Integer, MyFilePath As String, Fs As FileSearc
Set Fs = Application.FileSearc
With F
.LookIn = "C:\
.Filename = "MyFileName.xls
.FileType = msoFileTypeExcelWorkbook
.SearchSubFolders = Tru
If .Execute() > 0 The
FCnt = .FoundFiles.Coun
MyFilePath = .FoundFiles(FCnt
Els
MsgBox "MyFileName could not be found!
Exit Su
End I
End With
 
Top