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
> > > > >
> > >

Hi


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

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

Hi


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