Thread
:
retrieve filenames from given directory into excel
View Single Post
#
9
Posted to microsoft.public.excel.worksheet.functions
[email protected]
Posts: n/a
retrieve filenames from given directory into excel
I know now what the problem was in the first time. It seems like it
does not work in office 2000
Giel
wrote:
Arvi,
This is great...
I was planning to make this work with an "open" dialog. Then the user
could choose the directory. But this works even better.
Thanks for the help!
Regards,
Giel
Arvi Laanemets wrote:
Hi
1. Created a new excel file;
2. Actrivated VBA editor;
3. Added a module;
4. Copied the code from my posting into module;
5. Closed the VBA editor;
6. Into any cell, entered the formula
=GetMyFile("C:\Documents and Settings\Arvi\My Documents",1,"doc")
A word document's name from My Documents folder is returned.
=GetMyFile("C:\Documents and Settings\Arvi\My Documents",2,"doc")
A second word document's name from My Documents folder is returned, etc.
Arvi Laanemets
wrote in message
oups.com...
Arvi,
Did you check this code?
When I'm calling this function I get no results.
Regards,
Giel
Arvi Laanemets wrote:
Hi
Maybe you can adjust this function:
----------
Public Function GetMyFile(MyFolder As String, FileNum As Integer,
MyExtension As String)
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(MyFolder)
Set fc = f.Files
i = 0
For Each f1 In fc
If Right(f1.Name, 3) = MyExtension Then
i = i + 1
If i = FileNum Then GetMyFile = f1.Name
End If
Next
End Function
-----------
The function returns the name of n-th file with estimated extension from
estimated folder.
Arvi Laanemets
wrote in message
oups.com...
Hello,
I'm trying to retrive files from a given directory into excel.
I already have this file from another posted message:
Sub GetFileNames()
Dim F As Long
Dim FileName As String
Dim TheNames As Variant
ReDim TheNames(1 To 1)
FileName = Dir$("*.*")
Do While Len(FileName)
F = F + 1
ReDim Preserve TheNames(1 To F)
TheNames(F) = FileName
FileName = Dir$()
Loop
Cells(1, 1).Resize(F, 1).Value = Application.Transpose(TheNames)
End Sub
But I want to be able to set the path myself
thanks
Reply With Quote