retrieve filenames from given directory into excel

giel.va...@nextprint.nl

unread,

Jun 3, 2006, 2:48:02 PM6/3/06

to

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

unread,

Jun 3, 2006, 3:04:53 PM6/3/06

to

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.va...@nextprint.nl> wrote in message
news:1149360482.3...@j55g2000cwa.googlegroups.com...

giel.va...@nextprint.nl

unread,

Jun 3, 2006, 3:56:34 PM6/3/06

to

Many thanks chuck

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

Regards,

Giel

Arvi Laanemets

unread,

Jun 3, 2006, 4:52:05 PM6/3/06

to

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.va...@nextprint.nl> wrote in message
news:1149360482.3...@j55g2000cwa.googlegroups.com...

giel.va...@nextprint.nl

unread,

Jun 4, 2006, 3:24:49 AM6/4/06

to

erweurw

giel.va...@nextprint.nl

unread,

Jun 4, 2006, 3:33:17 AM6/4/06

to

Arvi,

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

Regards,
Giel

Arvi Laanemets

unread,

Jun 4, 2006, 5:00:29 AM6/4/06

to

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.va...@nextprint.nl> wrote in message
news:1149406397.2...@y43g2000cwc.googlegroups.com...

giel.va...@nextprint.nl

unread,

Jun 4, 2006, 10:51:13 AM6/4/06

to

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!

giel.va...@nextprint.nl

unread,

Jun 4, 2006, 1:20:39 PM6/4/06

to

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

Giel

Arvi Laanemets

unread,

Jun 5, 2006, 1:44:50 AM6/5/06

to

Hi

<giel.va...@nextprint.nl> wrote in message
news:1149441639.1...@y43g2000cwc.googlegroups.com...

>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.va...@nextprint.nl

unread,

Jun 5, 2006, 2:58:12 PM6/5/06

to

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

unread,

Jun 6, 2006, 1:16:58 PM6/6/06

to

Hi

<giel.va...@nextprint.nl> wrote in message
news:1149533892.8...@u72g2000cwu.googlegroups.com...

> 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