Getting information from Word to Excel...

[theSquirrel]

I have been trolling these forums and others over the last few weeks
learning the in’s and outs of VBA in Excel. I think I have a good
enough base to tackle a project my boss just gave me.

Basically, he wants to have a folder on a file server hold a bunch of
Word documents and add to an existing local Excel document the ability
to get information from the Word documents to display and update on his
local document. The local document will need to be able to pull
information from multiple documents at a time.

The template is used by multiple projects and the data in the templates
changes daily. The idea for the folder will be to hold ALL projects in
the same folder while not removing the file from the day prior.

The files will use the same naming convention “XXXX – DSR –
MM.DD.YY.Doc”, where XXXX is our 4-digit identifier for the project.

The template contains 1 main table which has many rows and columns that
contain the data I need to get.

I have tried to record macros on the Template to find the code for
selecting a particular cell, but it has not provided any real answers.
I am also stuck as to how to create a link between the local Excel
document and the network based Word document.

Thanks in advance for your help, there is not much information about
this on the web.

 

[Ed]

Basically, you need a program that will:
-- search all Word files in a particular folder
-- for those with names beginning with the same project identifier, choose
the latest date
-- open the Word file and read from specific cells in a specific table
-- close the Word doc and write the data into an Excel file
correct?

I've done a project of this scope - but not without great amounts of help
and many frustrations over a period of time. I'm sure there are those here
who could whip out something like this in their spare time - and I wish I
were one of them! But if you're willing to do some digging and learning,
this is very do-able.

Some suggestions:
(1) Get real familiar with how to Google the groups for answers. Almost
all of this has been answered before (a lot of it as answers to me!). You
can use the Advanced search at groups.google.com. I usually use the search
tool from http://www.rondebruin.nl/Google.htm - there's one for use within
Excel and one to use within Word.

(2) Break it down to one issue at a time, like I did above. It saves the
hair-pulling.

(3) Try everything out on dummy files and folders first. No sense
overwriting good data with garbage.

(4) If you need to go over a network, make sure you can go over the
network. Macros have been used for viruses, and I've had some go-rounds
with my IT people over running code across a network.

On that note, try this:
-- Navigate into a network folder and copy the full path and document name
of a Word document. Paste it into the code below at "XXXXXX".
-- Put the following code into an Excel module and see if it can open the
Word document. Make sure you have a reference (under Tools>>References) to
Microsoft Word [version number] Object Library.
--
Ed
Chief Chef,
Kludge Code Cafe
"Spaghetti Is Our Specialty!"
'
' **** Begin Code ****
Sub Word_File()



Dim appWD As New Word.Application

Dim docDoc As New Word.Document

Dim rngRange As Word.Range



' Create a new instance of Word & make it visible

Set appWD = CreateObject("Word.Application")

appWD.Visible = True



' Tell Word to open a document

Set docDoc = appWD.Documents.Open(XXXXXXXX)



' Get some text from the document

Set rngRange = docDoc.Range.Paragraphs(1)

MsgBox rngRange.Text



' Clean up

docDoc.Close wdDoNotSaveChanges

Set docDoc = Nothing

appWD.Quit

Set appWD = Nothing



End Sub

' **** End Code ****

"theSquirrel" <[email protected]>
wrote in message