retrieve filenames from given directory into excel

[giel.vanboxtel]

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

 

[CLR]

I use Jim Cone's fine Add-in called "ListFiles" for this sort of thing.
It's available for free at.........

http://www.realezsites.com/bus/primitivesoftware/products.php

Vaya con Dios,
Chuck, CABGx3

 

[giel.vanboxtel]

I use Jim Cone's fine Add-in called "ListFiles" for this sort of thing.
It's available for free at.........

http://www.realezsites.com/bus/primitivesoftware/products.php

Vaya con Dios,
Chuck, CABGx3

Many thanks chuck

But I need this as a part of another "sub" so I'm looking for code

Regards,

Giel

 

[Arvi Laanemets]

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

 

[giel.vanboxtel]

erweurw

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

 

[giel.vanboxtel]

Arvi,

Did you check this code?
When I'm calling this function I get no results.

Regards,
Giel

 

[Arvi Laanemets]

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

 

[giel.vanboxtel]

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

 

[giel.vanboxtel]

I know now what the problem was in the first time. It seems like it
does not work in office 2000

Giel

 

[Arvi Laanemets]

Hi

I know now what the problem was in the first time. It seems like it
does not work in office 2000

???
I have Office2000 !


Btw., I often use this UDF combined with function ROW(), and another 2
UDF's, which you find below.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

--------------
Public Function GetThisFolder(Optional MyTime As Date)
GetThisFolder = ThisWorkbook.Path
End Function

Public Function GetSubfolder(MyFolder As String, FolderNum As Integer,
Optional MyTime As Date)
Dim fs, f, f1, s, sf
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(MyFolder)
Set sf = f.SubFolders
i = 0
For Each f1 In sf
i = i + 1
If i = FolderNum Then GetSubfolder = f1.Name
Next
End Function

 

[giel.vanboxtel]

Extention ee

what is that Estonia?

Regards,
Giel

PS. If I can do something for you? I'm in the printing business. If you
have PDF material to print, I can do that for you and send it anywhere
you like. thats for free of course...

 

[Arvi Laanemets]

Hi

Extention ee

what is that Estonia?
Yes



Regards,
Giel

PS. If I can do something for you? I'm in the printing business. If you
have PDF material to print, I can do that for you and send it anywhere
you like. thats for free of course...

Thanks for offer, but hardly I'll need this )


Arvi Laanemets