Get File names using VBA

[JimPNicholls]

Excel 2k

Hi

I have a folder that has over 200 documents in. I would
like to get a list of these document names into a word
document without manually copying and pasting each
individual file name. Can anyone supply with with the code?

 

[Keith Emery]

Simplest thing is to use DOS!

DIR > myfile.txt

then open with Word

 

[ag]

Hi Jim,

Try this:

Within your word document, create a new macro and enter the following as the
macro text (omitting the -----):
----------
Sub DIR()
'
' DIR Macro
' Macro created 22/09/04 by Andy Gallagher
'
Dim directoryname
directoryname = InputBox("Please enter search directory", "Directory")
Set fso = CreateObject("Scripting.FileSystemObject")
Set mainfolder = fso.GetFolder(directoryname)
Set filecollection = mainfolder.Files
For Each file In filecollection
Selection.Text = file.Name
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.TypeParagraph
Next
End Sub
----------
This should ask you for the directory you require to search through and
output the filenames to the current document when you run the macro

Let me know how you get on by replying to the group

Cheers

Andy Gallagher

 

[Peter Kinsman]

Am I correct in thinking that to do this you need a reference to Scripting
Runtime, which can upset some anti-virus programs when it runs?

Peter Kinsman

 

[Chris]

Hi! I try this code, and I think it's a nice way to do'it. No problem
with Norton anti-virus. Maybee if you try to run the document on a
second computer. You dont Scripting in Runtime, start the macro from
Word and it works nice.
/ Chris