Get file path by selecting folder

[VARGEESH]

Hi,


I ran the below code to get the file path. By selecting the file it retrieve the file path.

[pre]

Sub GetFilePath()
FilePath = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If FilePath <> False Then
Range("F5").Value = FilePath
End If
End Sub

[/pre]

But I wish to get all the file path by choosing a folder and the path should display as one by one Range from "F5, F6, F7, ... end".


And I need to extract all types of files i.e. xls, xlsx, txt, pdf, etc.


Kindly advise me.


Regards,

Vargeesh M

 

[Luke M]

Check out the posts here:

http://www.ozgrid.com/forum/showthread.php?t=65530


Looks like an exact match to what you need.

 

[VARGEESH]

Hi Luke,


When I execute the below code it thrown an error like "Run time error'445': Object dosen't support this action".


And it was highlighted on

Set fs = Application.FileSearch

[pre]Sub ListAllFiles()
Dim fs As FileSearch, ws As Worksheet, i As Long
Set fs = Application.FileSearch
With fs
.SearchSubFolders = False ' set to true if you want sub-folders included
.FileType = msoFileTypeAllFiles 'can modify to just Excel files eg with msoFileTypeExcelWorkbooks
.LookIn = "C:" 'modify this to where you want to serach
If .Execute > 0 Then
Set ws = Worksheets.Add
For i = 1 To .FoundFiles.Count
ws.Cells(i, 1) = .FoundFiles(i)
Next
Else
MsgBox "No files found"
End If
End With
End Sub

[/pre]

Please suggest me.


Regards,

Vargeesh M

 

[Debraj]

Hi Vargeesh,


Just did a Google search and found there are changes to the Office object model i.e. FileSearch appears not to exists in 2007 or 2010.

The Dir() function is the most straightforward equivalent to Application.FileSearch.


http://support.microsoft.com/kb/920229


BTW.. Just modified little in you coding..

[pre]

Sub GetFilePath()
FilePath = Application.GetOpenFilename("Excel Files (*.xls*),*.xls*,Text files (*.txt), *.txt, PDF files (*.txt), *.txt")
If FilePath <> False Then
Filename = Split(FilePath, "")
For i = 0 To UBound(Filename)
Range("F5").Offset(0, i) = Filename(i)
Next
End If
End Sub

[/pre]

Regards,

Deb

 

[VARGEESH]

Hi Deb,


Thanks for your suggestion.


Please find the below link.


http://www.2shared.com/file/j5nCijSr/FilePath.html


The code you have mentioned searches a file alone. But I need to get all the file path within a selected folder irrespective of file extensions.


Regards!

 

[Debraj]

Hi Vargeesh..


Read the below URL,


http://chandoo.org/forums/topic/total-number-of-files-for-a-given-drive


You can download the files provided by SirJB7, Shrivallabha or Mine..


Regards,

Deb


PS: Just a request. Please try Google Search in the top Right of this page..

otherwise chandoo is planning to improve his site.. Un-neccesarily..

 

[VARGEESH]

Of course Deb. I will do google search.


Thanks for your advice.


The code is: http://www.2shared.com/document/KDM-RKuk/Fiepath.html