Macro for copy Word into Excel

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.

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

drsmN0SPAMikleAThotmailD0Tcom.INVALID <-- fix this before replying

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?