retrieve filenames from given directory into excel
 

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

retrieve filenames from given directory into excel
 

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/primi...e/products.php

Vaya con Dios,
Chuck, CABGx3


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

retrieve filenames from given directory into excel
 

CLR wrote:
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/primi...e/products.php

Vaya con Dios,
Chuck, CABGx3


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

Many thanks chuck

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

Regards,

Giel

retrieve filenames from given directory into excel
 

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

retrieve filenames from given directory into excel
 

erweurw
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

retrieve filenames from given directory into excel
 

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

retrieve filenames from given directory into excel
 

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

retrieve filenames from given directory into excel
 

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

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

retrieve filenames from given directory into excel
 

Hi


wrote in message
oups.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<attarkon.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

retrieve filenames from given directory into excel
 

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 wrote:
Hi


wrote in message
oups.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<attarkon.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

retrieve filenames from given directory into excel
 

Hi


wrote in message
oups.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