Macro for copy Word into Excel

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.

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?