Word macro to extract data from excel

[Kenneth]

I have an excel spreadsheet with a lot of data across mutiple
worksheets. Once I have all the data in excel, I use this data to
create a report in Word. Normally, I will type the Word report by hand,
but it's so time consuming. The reports are always the same text, but
with different numbers from the excel spreadsheet. Does anyone know of
a way to automate this process and generate a Word report based on this
data in excel? Ideally, I'd like to open a Word template, run a macro
that will reference the excel spreadsheet and generate the report text
using the data from the spreadsheet.

Thanks!

 

[Carol]

Why don't you just embed the excel sheet into a Word document? Or
better yet, take a picture of the Excel document and paste it into
Word? It's kind of difficult to tell you how to create a template for
this when I dont know what all it entails.

 

[Michael Bednarek]

I have an excel spreadsheet with a lot of data across mutiple
worksheets. Once I have all the data in excel, I use this data to
create a report in Word. Normally, I will type the Word report by hand,
but it's so time consuming. The reports are always the same text, but
with different numbers from the excel spreadsheet. Does anyone know of
a way to automate this process and generate a Word report based on this
data in excel? Ideally, I'd like to open a Word template, run a macro
that will reference the excel spreadsheet and generate the report text
using the data from the spreadsheet.

Insert a LINK field to the data in the Excel workbook into your Word
document. The easiest way to do this is described in Word Help:
Insert information by creating a linked object or embedded object
(Copy/Paste Special/Paste link)

 

[Kenneth]

Carol and Michael,
Thank you for your response. Maybe I didn't describe my situation
clearly. I'm not wanting the excel spreadsheet to appear in word, in a
table format anyway. I want the excel data to show up within a
paragraph form, written report. For example, let's say my excel
worksheet was a list of addresses and phone numbers listed by name. So,
in Word I could write: "(Name) currently lives at (address). They can
also be reached by phone at (phone number)." (The excel data being in
place of the items in parenthesis) It's a simple example, but maybe
that explains things better.

The spreadsheet I have has a lot more data and the paragraphs are much
longer in the report. I need to duplicate the paragraphs in the report,
but with different data each time. I could link to the data in excel
from Word, but I have to update the link fields individually each time.
I tried this once and after updating a ton of links, I decided that it
was too time consuming. I don't know of an easier way to update the
links in the whole document at once. I thought it would be great to
create a macro that asks for a value (the Name in our example) and
based on that value, imports the data into the paragraph text. I could
then do this over and over for each value in the excel spreadsheet.
Does that help explain my situation better?

 

[Michael Bednarek]

Carol and Michael,
Thank you for your response. Maybe I didn't describe my situation
clearly. I'm not wanting the excel spreadsheet to appear in word, in a
table format anyway. I want the excel data to show up within a
paragraph form, written report. For example, let's say my excel
worksheet was a list of addresses and phone numbers listed by name. So,
in Word I could write: "(Name) currently lives at (address). They can
also be reached by phone at (phone number)." (The excel data being in
place of the items in parenthesis) It's a simple example, but maybe
that explains things better.

The spreadsheet I have has a lot more data and the paragraphs are much
longer in the report. I need to duplicate the paragraphs in the report,
but with different data each time. I could link to the data in excel
from Word, but I have to update the link fields individually each time.
I tried this once and after updating a ton of links, I decided that it
was too time consuming. I don't know of an easier way to update the
links in the whole document at once. I thought it would be great to
create a macro that asks for a value (the Name in our example) and
based on that value, imports the data into the paragraph text. I could
then do this over and over for each value in the excel spreadsheet.
Does that help explain my situation better?

I created an Excel workbook with these two cells:
B2: Test 11
B3: Test 12
and saved it.

Then I created a Word document with these two lines:
Field 1: { LINK Excel.Sheet.8 C:\\Temp\\testlink.xls Sheet1!R2C2 \a \t }
Field 2: { LINK Excel.Sheet.8 C:\\Temp\\testlink.xls Sheet1!R3C2 \a \t }
where the field codes ({...}) were created via Copy/Paste Special/Paste link
as I described in my previous post.

These lines then showed in the document as
Field 1: Test 11
Field 2: Test 12
I saved and closed the document.

Then I opened the workbook and changed the values to
B2: Test 21
B3: Test 22
and saved and closed it.

Then I opened the Word document; it prompted me whether I wanted to update
the links; I answered Yes. It took several seconds to do that and then showed
Field 1: Test 21
Field 2: Test 22
There is no delay if the workbook is already open.

This is exactly what the Help section suggests which I mentioned in my
previous post. Does that not work for you?

 

[Kenneth]

On 27 Mar 2006 07:19:42 -0800, Kenneth wrote in microsoft.public.word:

I created an Excel workbook with these two cells:
B2: Test 11
B3: Test 12
and saved it.

Then I created a Word document with these two lines:
Field 1: { LINK Excel.Sheet.8 C:\\Temp\\testlink.xls Sheet1!R2C2 \a \t }
Field 2: { LINK Excel.Sheet.8 C:\\Temp\\testlink.xls Sheet1!R3C2 \a \t }
where the field codes ({...}) were created via Copy/Paste Special/Paste link
as I described in my previous post.

These lines then showed in the document as
Field 1: Test 11
Field 2: Test 12
I saved and closed the document.

Then I opened the workbook and changed the values to
B2: Test 21
B3: Test 22
and saved and closed it.

Then I opened the Word document; it prompted me whether I wanted to update
the links; I answered Yes. It took several seconds to do that and then showed
Field 1: Test 21
Field 2: Test 22
There is no delay if the workbook is already open.

This is exactly what the Help section suggests which I mentioned in my
previous post. Does that not work for you?

Michael,
Thanks again for your help. I've used those links before in Word and
sometimes it works well. However, let's say that each paragraph in Word
that I'm using has 15 links to the excel spreadsheet. If you add
another paragraph with the same links, do you have to update each link
manually or can you do it globally so that it references new data?
Normally I'm adding 30-70 of these paragraphs in each report so editing
all those links can take a while. I am hoping for a way to automate the
process so that I don't have to manually update the links, at least not
all of them.

Thanks,
Kenneth

 

[Michael Bednarek]

On 28 Mar 2006 07:51:42 -0800, Kenneth wrote in microsoft.public.word:

[snip]

Michael,
Thanks again for your help. I've used those links before in Word and
sometimes it works well. However, let's say that each paragraph in Word
that I'm using has 15 links to the excel spreadsheet. If you add
another paragraph with the same links, do you have to update each link
manually or can you do it globally so that it references new data?
Normally I'm adding 30-70 of these paragraphs in each report so editing
all those links can take a while. I am hoping for a way to automate the
process so that I don't have to manually update the links, at least not
all of them.

I don't understand what you mean by "update each link". The *data* in
the links of the Word document will be updated from their Excel sources
when the Word document is opened (or via F9 or the Edit/Links... menu).

Modifying the link *specification* is usually a manual affair, but if
you can formulate rules for that, it can certainly be automated with VBA
code. LINKs are members of ActiveDocument.Fields of Type=wdFieldLink
(56). E.g.:

Dim fldField As Field

For Each fldField In ActiveDocument.Fields
With fldField
If .Type = wdFieldLink Then
Debug.Print "Was: " & .Code
.Code.Text = Replace(.Code, "March", "April")
Debug.Print "Now: " & .Code
End If
End With
Next fldField

This works whether the Field Codes are visible or not.

You can also apply Search/Replace on the link specifications if the
Field Codes are visible (Alt+F9).