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
news:[email protected]...
>
> 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.
>
>
> --
> theSquirrel
>
>
> ------------------------------------------------------------------------
> theSquirrel's Profile:
> http://www.excelforum.com/member.php...o&userid=36770
> View this thread: http://www.excelforum.com/showthread...hreadid=564853
>