Hi Emile,

I think I pasted fcnGetFileList for you at the end of my post -- I
wouldn't be so mean not to!

I had a recursive one - that returned full paths for all files in
subfolders etc. I can't seem to put my hands on it and I don't have time
to rewrite it just now.

Here's one I found on google (from this NG) -- see bottom of email. Note
this isn't recursive but it should give the same results.

HTH,
Gareth

Bob Phillips Jul 25, 4:27 am show options

Newsgroups: microsoft.public.excel.programming
From: "Bob Phillips" <bob.phill...@notheretiscali.co.uk> - Find messages
by this author
Date: Mon, 25 Jul 2005 10:27:46 +0100
Local: Mon, Jul 25 2005 4:27 am
Subject: Re: Recursive Function + File searching to return path
Reply to Author | Forward | Print | Individual Message | Show original |
Report Abuse

Straight-forward enough


Sub ph8()
Const sStartFolder As String = "c:\myTest"
Dim iCtr As Long
Dim iLevel As Long
Dim iBaseLevel As Long
Dim sh As Worksheet


iBaseLevel = Len(sStartFolder) - Len(Replace(sStartFolder, "\", ""))
With Application.FileSearch
.NewSearch
.LookIn = sStartFolder
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
On Error Resume Next
Set sh = Worksheets("Files")
On Error GoTo 0
If Not sh Is Nothing Then
sh.Cells.ClearContents
Else
Worksheets.Add.Name = "Files"
Set sh = ActiveSheet
End If
sh.Cells(1, 1) = sStartFolder
sh.Cells(1, 2) = 1
For iCtr = 1 To .FoundFiles.Count
iLevel = Len(.FoundFiles(iCtr)) - _
Len(Replace(.FoundFiles(iCtr), "\", ""))
sh.Cells(iCtr + 1, 1) = _
.FoundFiles(iCtr)
sh.Cells(iCtr + 1, 2).Value = iLevel
Next iCtr
End If
End With
End Sub





I used to have one somewhere....

Russs wrote:
> On 9/10/05 3:37 PM, in article OyYHo8jtFHA.2792@tk2msftngp13.phx.gbl,
> "Gareth" <msng@garhooREMOVEME.com> wrote:
>
>
>>You need to open each word document, take the data and then close it.
>>Try something like:
>>
>>Sub GoThroughWordDocs()
>>
>>Dim myFiles As Variant
>>Dim oW As Object
>>Dim doc As Object
>>
>> 'Get list of document files from my path
>> myFiles = fcnGetFileList("c:\temp", "*.doc")
>>
>> Set oW = GetObject(, "Word.Application")
>>
>> If myFiles(LBound(myFiles)) = "" Then
>> MsgBox "no files found"
>> Exit Sub
>> End If
>>
>> For i = LBound(myFiles) To UBound(myFiles)
>>
>> 'open the word document
>> Set doc = oW.Documents.Open(Filename:=myFiles(i))
>>
>> 'use whatever code you already have to prcoess
>> 'this document
>>
>> 'close it
>> doc.Close
>>
>> Next i
>>
>> Set doc = Nothing
>> Set oW = Nothing
>>
>>End Sub
>>
>>
>>HTH,
>>Gareth
>>
>>emile wrote:
>>
>>>Hey guys,
>>>
>>>I have been looking through past posts but can't really find what I am
>>>looking for.
>>>
>>>What I need is a macro that will take information within a certain table in
>>>Word and transfer it into Excel. I have hundreds of Word documents on hand
>>>and all are in a same format layuot. I will extract about 6 counts of
>>>information and paste them into 6 columns of Excel, and the next Word
>>>information will be pasted in the next row of Excel. Since the format of Word
>>>will be the same, I was hoping that there can be a macro solution to this
>>>problem.
>>>
>>>I have already tried Macro recorder, but the recorded code will only target
>>>Open documents and spreadsheets at a specific file name. Is it possible to do
>>>all this without the hundreds of Word documetns opened?
>>>
>>>I am new to macros so I really appreciate your help. Plus, if you have any
>>>info about on-line tutorials of Word or Excel macros, please let me know.
>>>That will help as well.
>>>
>>>Thanks all.
>
> Hi Gareth,
> I did a google search to find 'fcnGetFileList' and found it OK. Is there a
> function using VBA to recursively process all files in a given directory and
> all the directory's subdirectories?