Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching fro A file and returming the pathe to that file
I trying to search a specified directory for the location of a specific file
Once the file is found I want to be able to return that directory and path so i can use it in other parts of of the macro Thanks -- Helping Is always a good thing |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching fro A file and returming the pathe to that file
filesearch can be used, but I was told it doesn't work in excel 2007. Here
is a method that will work. I assume that you need to search subdirectories. Public file_loc As String Sub findfile() 'set MyFilename and strfold as required 'file to search for Const MyFileName = "xyz.txt" 'directory to start searching strFolder = "c:\temp" file_loc = "" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set folder = _ fso.GetFolder(strFolder) Call GetWorksheetsSubFolder(strFolder + "\", MyFileName) MsgBox ("File found in folder: " & file_loc) End Sub Sub GetWorksheetsSubFolder(strFolder, MyFileName) Set fso = CreateObject _ ("Scripting.FileSystemObject") Set folder = _ fso.GetFolder(strFolder) If folder.subfolders.Count 0 Then For Each sf In folder.subfolders On Error GoTo 100 Call GetWorksheetsSubFolder(strFolder + sf.Name + "\", MyFileName) If file_loc < "" Then Exit For 100 Next sf End If 'folder size in bytes On Error GoTo 200 If file_loc = "" Then For Each fl In folder.Files If fl.Name = MyFileName Then file_loc = folder.Name End If Next fl End If 200 On Error GoTo 0 End Sub "QuietMan" wrote: I trying to search a specified directory for the location of a specific file Once the file is found I want to be able to return that directory and path so i can use it in other parts of of the macro Thanks -- Helping Is always a good thing |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching fro A file and returming the pathe to that file
Take a look here....Conan just helped me with a similar request. I'm using
2003 and it works great. http://www.microsoft.com/office/comm...a-b2d2523a7cf2 HTH, Don "QuietMan" wrote: I trying to search a specified directory for the location of a specific file Once the file is found I want to be able to return that directory and path so i can use it in other parts of of the macro Thanks -- Helping Is always a good thing |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching and adding in a closed file | Excel Programming | |||
Searching for a file | Excel Programming | |||
Searching for and Pasting Data to a new file | Excel Programming | |||
Searching a txt file for splitting into different worksheets | Excel Programming | |||
File Searching | Excel Programming |