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

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

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

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

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

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

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

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

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