List of files in dir

I used to have some code for this, but it's out of date now. All I need is
to have Word or whatever agent create a list of the files in a specified
directory, and pop it into an Word document. Simple enough, but it's beyond
me. TIA

Jezebel
Thu Apr 27 09:18:58 CDT 2006

Posting here is easier than reading one single help topic?


"MaxWedge426" <MaxWedge426@discussions.microsoft.com> wrote in message
news:1A752231-04B7-4E01-AEAC-9A4AE2FB3B41@microsoft.com...
>I used to have some code for this, but it's out of date now. All I need is
> to have Word or whatever agent create a list of the files in a specified
> directory, and pop it into an Word document. Simple enough, but it's
> beyond
> me. TIA

Dave
Thu Apr 27 09:29:30 CDT 2006

Hi,

If you want only a folder, then you can use the following:

Option Explicit

Public Function fDirectoryListArray( _
sPath As String, _
sExtension As String) As Variant

Dim MyFile As String
Dim Counter As Long

'Create a dynamic array variable, and then declare its initial size
Dim DirectoryListArray() As String
ReDim DirectoryListArray(1000)
Counter = 0
'Loop through all the files in the directory by using Dir$ function
MyFile = Dir$(sPath & "*" & sExtension)
Do While MyFile <> ""
DirectoryListArray(Counter) = MyFile
MyFile = Dir$
Counter = Counter + 1
Loop

'Reset the size of the array without losing its values by using Redim
Preserve
If Counter <> 0 Then
ReDim Preserve DirectoryListArray(Counter - 1)
Else
ReDim Preserve DirectoryListArray(0)
End If

fDirectoryListArray = DirectoryListArray

End Function

Public Sub test()
Dim aDir As Variant
Dim iCount As Integer
Dim oDoc As Document
Set oDoc = Documents.Add
aDir = fDirectoryListArray(sPath:="C:\Documents and Settings\DLett\My
Documents\", sExtension:=".doc")
For iCount = 0 To UBound(aDir)
oDoc.Range.InsertAfter aDir(iCount) & vbCrLf
Next iCount
End Sub

If you want an entire directory, then let me know. I think I have code for
that somewhere.

HTH,
Dave

"MaxWedge426" <MaxWedge426@discussions.microsoft.com> wrote in message
news:1A752231-04B7-4E01-AEAC-9A4AE2FB3B41@microsoft.com...
>I used to have some code for this, but it's out of date now. All I need is
> to have Word or whatever agent create a list of the files in a specified
> directory, and pop it into an Word document. Simple enough, but it's
> beyond
> me. TIA

garfield-n-odie
Thu Apr 27 13:00:31 CDT 2006

See http://word.mvps.org/FAQs/MacrosVBA/InsertFileNames.htm .

MaxWedge426 wrote:
> I used to have some code for this, but it's out of date now. All I need is
> to have Word or whatever agent create a list of the files in a specified
> directory, and pop it into an Word document. Simple enough, but it's beyond
> me. TIA

MaxWedge426
Thu Apr 27 20:10:02 CDT 2006

> Posting here is easier than reading one single help topic?
Well, I'd have to count that as hardly helpful, and just a tad toward the
snarky side - just a little. I know how to do it with DOS, but that's not
needed. If I KNEW the simple Help topic and where it was, I wouldn't dream
of disturbing Her Highness. So, you had to comment and couldn't just scroll
down?

MaxWedge426
Thu Apr 27 20:12:01 CDT 2006

Thanks, Dave - That looks like just what I need.

Jack

MaxWedge426
Thu Apr 27 20:13:01 CDT 2006

Very nice. Much appreciated.

Ed
Fri Apr 28 09:44:31 CDT 2006

So you couldn't just read and scroll - you had to get "snarky" back?
Especially when typing "dir" in the VBA Help index yields "DIR Function" and
"DIR Function Example"? And a search of the Word MVP FAQs would have turned
up the page garfield-n-odie gave you? And a Google groups search of this
newsgroup would have turned up more code than you can use?

Not that I myself haven't been guilty of this and much more in my beginning
days, which weren't all that long ago. I have found it is usually easier
for the good people who donate their time here helping others if the poster
has looked first and is having problems than to respond to "please write
this app for me for free." The first demonstrates that you're trying to
learn; the second reminds me too much of my teenagers!

Jezebel has an incredible track record here; her willingness to help and
follow a thread through to great depths is well documented. Knowing nothing
of you other than the words we read here, and reading through too many
requests for everything from homework answers to complete apps, a judgment
was made (one that appears to possibly be more accurate than not). If you'd
like to join us in our pursuit of VBA excellence and share your knowledge
and experience, welcome!

Ed

"MaxWedge426" <MaxWedge426@discussions.microsoft.com> wrote in message
news:E13F0692-C34A-49A2-B34C-EEEC931F827C@microsoft.com...
>> Posting here is easier than reading one single help topic?
> Well, I'd have to count that as hardly helpful, and just a tad toward the
> snarky side - just a little. I know how to do it with DOS, but that's not
> needed. If I KNEW the simple Help topic and where it was, I wouldn't
> dream
> of disturbing Her Highness. So, you had to comment and couldn't just
> scroll
> down?

Greg
Fri Apr 28 10:03:28 CDT 2006

Ed,

Personnally I wouldn't want to get too close or snugle up with Jezebel
with your idea (which I believe, but can't swear to in a court of law,
is seriously misconceived) that the being that refers to itself as
Jezebel is a she ;-). I agree wholeheartedly with your assessment of
Jezebel's skills, however I think the name is more appropriate to warn
of the often waspish personality than as an indication of gender.

Greg
Fri Apr 28 10:06:08 CDT 2006

Dave,

I took the liberty to abbreviate your code a little and add a Directory
picker function and extension input box:

Public Sub CreateFileList()
Dim aDir As Variant
Dim i As Integer
Dim oDoc As Document
Dim PathToUse As String
Dim pExt As String
PathToUse$ = GetPathToUse
If PathToUse = "No selection" Or PathToUse = "Error" Then Exit Sub
pExt = InputBox("Enter file extension or '*' for all file types.", _
"Extension", "*")
Set oDoc = Documents.Add
aDir = fDirList(PathToUse, "." & pExt)
For i = 0 To UBound(aDir)
oDoc.Range.InsertAfter aDir(i) & vbCrLf
Next i
End Sub
Public Function fDirList(sPath As String, sExt As String) As Variant
Dim MyFile As String
Dim Counter As Long
'Create a dynamic array variable declare a liberal initial size
Dim DirListArray() As String
ReDim DirListArray(0) As String
Counter = 0
'Loop through all the files in the directory by using Dir$ function
MyFile = Dir$(sPath & "*" & sExt)
Do While MyFile <> ""
DirListArray(Counter) = MyFile
MyFile = Dir$
Counter = Counter + 1
ReDim Preserve DirListArray(Counter)
Loop
fDirList = DirListArray
End Function
Private Function GetPathToUse() As Variant
On Error GoTo Handler
'Get the folder containing the files. Note - The "Copy Dialog" is used
to
'to display the "open" option
With Dialogs(wdDialogCopyFile)
If .Display <> 0 Then
GetPathToUse = .Directory
Else
GetPathToUse = "No selection"
Exit Function
End If
End With
If Left(GetPathToUse, 1) = Chr(34) Then
GetPathToUse = Mid(GetPathToUse, 2, Len(GetPathToUse) - 2)
End If
Exit Function
Handler:
GetPathToUse = "Error"
Err.Clear
End Function

Dave
Fri Apr 28 10:25:23 CDT 2006

Greg,

Looks good, but I have to confess that the bulk of the routine is not mine;
as I'm sure you noticed, I got a lot of the basic stuff from the Word MVP's
site. I simply turned it into a function because I've found that I reuse
that quite a bit. I do like the added function of the dialog box (much more
dynamic). I didn't have a good reason for not including that in my basic
routine.

Dave

"Greg Maxey" <gmaxey@mvps.org> wrote in message
news:1146236768.014336.258670@v46g2000cwv.googlegroups.com...
> Dave,
>
> I took the liberty to abbreviate your code a little and add a Directory
> picker function and extension input box:
>
> Public Sub CreateFileList()
> Dim aDir As Variant
> Dim i As Integer
> Dim oDoc As Document
> Dim PathToUse As String
> Dim pExt As String
> PathToUse$ = GetPathToUse
> If PathToUse = "No selection" Or PathToUse = "Error" Then Exit Sub
> pExt = InputBox("Enter file extension or '*' for all file types.", _
> "Extension", "*")
> Set oDoc = Documents.Add
> aDir = fDirList(PathToUse, "." & pExt)
> For i = 0 To UBound(aDir)
> oDoc.Range.InsertAfter aDir(i) & vbCrLf
> Next i
> End Sub
> Public Function fDirList(sPath As String, sExt As String) As Variant
> Dim MyFile As String
> Dim Counter As Long
> 'Create a dynamic array variable declare a liberal initial size
> Dim DirListArray() As String
> ReDim DirListArray(0) As String
> Counter = 0
> 'Loop through all the files in the directory by using Dir$ function
> MyFile = Dir$(sPath & "*" & sExt)
> Do While MyFile <> ""
> DirListArray(Counter) = MyFile
> MyFile = Dir$
> Counter = Counter + 1
> ReDim Preserve DirListArray(Counter)
> Loop
> fDirList = DirListArray
> End Function
> Private Function GetPathToUse() As Variant
> On Error GoTo Handler
> 'Get the folder containing the files. Note - The "Copy Dialog" is used
> to
> 'to display the "open" option
> With Dialogs(wdDialogCopyFile)
> If .Display <> 0 Then
> GetPathToUse = .Directory
> Else
> GetPathToUse = "No selection"
> Exit Function
> End If
> End With
> If Left(GetPathToUse, 1) = Chr(34) Then
> GetPathToUse = Mid(GetPathToUse, 2, Len(GetPathToUse) - 2)
> End If
> Exit Function
> Handler:
> GetPathToUse = "Error"
> Err.Clear
> End Function
>

Greg
Fri Apr 28 10:48:19 CDT 2006

Dave,

Yes for the most part my additions are gems gleaned from other stuff I
have read and picked up in the NGs as well. I suppose for blinks
squirrels like me that there are very few nuts left to be found ;-)

Cheers.

Ed
Fri Apr 28 11:47:42 CDT 2006

Again, assumptions reach out to snag me! Thanks for the boost!
Ed

"Greg Maxey" <gmaxey@mvps.org> wrote in message
news:1146236607.979453.232820@y43g2000cwc.googlegroups.com...
> Ed,
>
> Personnally I wouldn't want to get too close or snugle up with Jezebel
> with your idea (which I believe, but can't swear to in a court of law,
> is seriously misconceived) that the being that refers to itself as
> Jezebel is a she ;-). I agree wholeheartedly with your assessment of
> Jezebel's skills, however I think the name is more appropriate to warn
> of the often waspish personality than as an indication of gender.
>

muyBN
Fri Jan 26 21:34:00 CST 2007

And I bet that it was originally written by Jezebel. (;>)
--
Bryan


"Dave Lett" wrote:

> Greg,
>
> Looks good, but I have to confess that the bulk of the routine is not mine;
> as I'm sure you noticed, I got a lot of the basic stuff from the Word MVP's
> site. I simply turned it into a function because I've found that I reuse
> that quite a bit. I do like the added function of the dialog box (much more
> dynamic). I didn't have a good reason for not including that in my basic
> routine.
>
> Dave
>
> "Greg Maxey" <gmaxey@mvps.org> wrote in message
> news:1146236768.014336.258670@v46g2000cwv.googlegroups.com...
> > Dave,
> >
> > I took the liberty to abbreviate your code a little and add a Directory
> > picker function and extension input box:
> >
> > Public Sub CreateFileList()
> > Dim aDir As Variant
> > Dim i As Integer
> > Dim oDoc As Document
> > Dim PathToUse As String
> > Dim pExt As String
> > PathToUse$ = GetPathToUse
> > If PathToUse = "No selection" Or PathToUse = "Error" Then Exit Sub
> > pExt = InputBox("Enter file extension or '*' for all file types.", _
> > "Extension", "*")
> > Set oDoc = Documents.Add
> > aDir = fDirList(PathToUse, "." & pExt)
> > For i = 0 To UBound(aDir)
> > oDoc.Range.InsertAfter aDir(i) & vbCrLf
> > Next i
> > End Sub
> > Public Function fDirList(sPath As String, sExt As String) As Variant
> > Dim MyFile As String
> > Dim Counter As Long
> > 'Create a dynamic array variable declare a liberal initial size
> > Dim DirListArray() As String
> > ReDim DirListArray(0) As String
> > Counter = 0
> > 'Loop through all the files in the directory by using Dir$ function
> > MyFile = Dir$(sPath & "*" & sExt)
> > Do While MyFile <> ""
> > DirListArray(Counter) = MyFile
> > MyFile = Dir$
> > Counter = Counter + 1
> > ReDim Preserve DirListArray(Counter)
> > Loop
> > fDirList = DirListArray
> > End Function
> > Private Function GetPathToUse() As Variant
> > On Error GoTo Handler
> > 'Get the folder containing the files. Note - The "Copy Dialog" is used
> > to
> > 'to display the "open" option
> > With Dialogs(wdDialogCopyFile)
> > If .Display <> 0 Then
> > GetPathToUse = .Directory
> > Else
> > GetPathToUse = "No selection"
> > Exit Function
> > End If
> > End With
> > If Left(GetPathToUse, 1) = Chr(34) Then
> > GetPathToUse = Mid(GetPathToUse, 2, Len(GetPathToUse) - 2)
> > End If
> > Exit Function
> > Handler:
> > GetPathToUse = "Error"
> > Err.Clear
> > End Function
> >
>
>
>