Find File By Name

[EMoe]

Hello Programmers,

Is it possible to open a particular file *by name *regardless of what
folder it may be in on C drive?

Example filename Store Inventory.xls somewhere on C drive

The reason is because the file name stays the same, however the file
moves to a different folder with the current month's name. So to keep
from changing the destination on the macro each month, I just want the
code to find the file, by its name.

Thanks,
EMoe

 

[Bernie Deitrick]

EMoe,

It would be better if you knew the folder name based on the current date, along the lines of

Workbooks.Open "C:\Folder 1\" & Format(Now(),"mmmmyy") & "\Store Inventory.xls"

Otherwise, you could use code like this

With Application.FileSearch
.NewSearch
.LookIn = "C:\"
.SearchSubFolders = True
.FileName = "Store Inventory.xls"
If .Execute > 0 Then
Workbooks.Open .FoundFiles(1)
End If
End With

which will work as long as there is only one Store Inventory.xls on your C drive.

HTH,
Bernie
MS Excel MVP

 

[EMoe]

Thanks Bernie, I give it a shot!

EMoe