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

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


<giel.vanboxtel@nextprint.nl> wrote in message
news:1149360482.354299.318660@j55g2000cwa.googlegroups.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
>
>
> <giel.vanboxtel@nextprint.nl> wrote in message
> news:1149360482.354299.318660@j55g2000cwa.googlegroups.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



<giel.vanboxtel@nextprint.nl> wrote in message
news:1149360482.354299.318660@j55g2000cwa.googlegroups.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
>
>
>
> <giel.vanboxtel@nextprint.nl> wrote in message
> news:1149360482.354299.318660@j55g2000cwa.googlegroups.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
>
>
>
> <giel.vanboxtel@nextprint.nl> wrote in message
> news:1149360482.354299.318660@j55g2000cwa.googlegroups.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


<giel.vanboxtel@nextprint.nl> wrote in message
news:1149406397.285123.100230@y43g2000cwc.googlegroups.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
> >
> >
> >
> > <giel.vanboxtel@nextprint.nl> wrote in message
> > news:1149360482.354299.318660@j55g2000cwa.googlegroups.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
>
>
> <giel.vanboxtel@nextprint.nl> wrote in message
> news:1149406397.285123.100230@y43g2000cwc.googlegroups.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
> > >
> > >
> > >
> > > <giel.vanboxtel@nextprint.nl> wrote in message
> > > news:1149360482.354299.318660@j55g2000cwa.googlegroups.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

giel.vanboxtel@nextprint.nl 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
> >
> >
> > <giel.vanboxtel@nextprint.nl> wrote in message
> > news:1149406397.285123.100230@y43g2000cwc.googlegroups.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
> > > >
> > > >
> > > >
> > > > <giel.vanboxtel@nextprint.nl> wrote in message
> > > > news:1149360482.354299.318660@j55g2000cwa.googlegroups.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
> > > > >
> > >