vba Help! using Excel worksheet as source to update Word document.

[Xceller]

Hi vba gurus:

I have a Word document that I would like to link to an Excel worksheet (Below). I want to be able to edit/change the data in the Excel table and have the variables (highlighted in red) update automatically in Word.

The variables in the Word document highlighted in red below correspond to various cells in the Excel table below.

Thank you for looking into this!


OVERVIEW AND BUSINESS TRENDS
Results for the Three Months Ended July 1, 2011
Consolidated revenues for the second quarter of 2011 were $2.4 billion, an increase of $110.9 million, or 4.9%, compared to the same period in 2010. For the three months ended July 1, 2011, DEG, which we acquired in September 2010, generated $115.0 million in revenues, and ABC Holdings, Inc. (“ABC”), which we acquired in June 2011, generated $27.6 million in revenues. By comparison, neither of the acquired companies contributed to our consolidated revenues during the second quarter of fiscal year 2010. During the quarter, revenues increased from our work in the federal and industrial and commercial market sectors, while we experienced a decline in revenues from our work in the power and infrastructure market sectors. Net income attributable to Parent Corporation for the second quarter of 2011 was $66.8 million compared with $61.9 million during the second quarter of 2010, an increase of 7.9%.
Cash Flows
During the six months ended July 1, 2011, we generated $262.9 million in cash from operations. Cash flows from operations increased by $142.8 million for the six months ended July 1, 2011 compared with the same period in 2010. This increase was primarily due to the timing of payments from clients on accounts receivable, project performance payments and vendor and subcontractor payments. The increase was partially offset by higher income tax payments and higher defined benefit plan contributions.
On June 1, 2011, we acquired ABC for a purchase price of approximately $259 million, net of cash acquired.
In addition, we had cash outflows of $136.7 million related to repurchases of common stock for the six months ended July 1, 2011. During the first six months of fiscal year 2011, we had a net borrowing of $50.0 million under our revolving line of credit.
Acquisition
On June 1, 2011, we completed the acquisition of ABC. The operating results of ABC after the acquisition date are included in our condensed consolidated financial statements under the Northwest business. The operating results generated from this newly acquired business during this period were not material to our consolidated results for the three- and six-month periods ended July 1, 2011.
Book of Business
As of July 1, 2011 and December 31, 2010, our total book of business was $28.7 billion and $29.1 billion, respectively. The decrease in our book of business in the first six months of fiscal year 2011 occurred primarily in our Northeast Region. This decrease was partially offset by an addition of $0.8 billion to our book of business resulting from our acquisition of ABC. Please see Book of Business on page 36 for more information.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Current Date[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]July 1,2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Last Year[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: center"]2010[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]This QTR[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]Three[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Last QTR[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]Secod[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]First QTR[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]First[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]YTD Months[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]Six[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Consolidated revenues[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]2.4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"] 8[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Incr / Decr[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]increase[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Incr / Decr in millions[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: center"]110.9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Percent increased[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]4.9%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Same Pd Last Yr[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: center"]2010[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]DEG revenue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]115.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]ABC revenue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]27.6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]This Yr Net Income Parent[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]66.8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]last Yr Net Income Parent[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]61.9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Parent Income Increase / decrease[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: center"]increase[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Parent Income Increase / decrease percent[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]7.9%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Cash from operations[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]262.9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Cash from operations increase/decrease[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]increased[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Increase in cash from operations[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]142.8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Purchase price of acquisition[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]259[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Cash outflows for repurchases of common stock[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="align: center"] 136.7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Net borrowing under revolving line of credit[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]50.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Book of business as of July 1, 2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]28.7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Book of business as of December 31, 2010[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]29.1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Book of business increase/decrease[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]decrease[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]27[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Book of business $ decrease[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: center"]0.8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Page number[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]36[/TD]
[/TR]
</tbody>[/TABLE]

 

[Cindy Ellis]

Based on what's already there, are you comfortable extending it to the rest of the report text?

 

[Xceller]

Yes, I will try to extend it to the rest of the report. If I run into issues and need help, do you mind if I ask you again?

I tried your other suggested approach of copying and pasting a link in Word. "In Word, choose Paste Special. Select the "Past Link" check box on the left side of the dialog, Select Unformatted text, and click OK. (If you choose Formatted text, the formatting applied in Excel will transfer over to your Word doc, probably not what you want). Format the text the way you want it in Word...it should "stick" even when the data is updated." I prefer this approach. Is there a way I can easily see the cross-reference of the numbers from Excel to Word? I can click the numbers in Word to find the link in Excel and tried viewing all the links, but there isn't a report or list I can print to see all the link references.

 

[Cindy Ellis]

I'm not sure if you can print a list of just the links (I'll see what's available through VBA), but you can at least look at the list.
From the "Office" button (assuming Excel 2007 or later), choose Prepare, then scroll to the bottom of the dialog and select "Edit Links to Files".
Another approach is to print the report with the field codes instead of the values. To do that, from the Office button choose "Word Options" (button near the bottom of the dialog), then "Advanced". Scroll down to the Print section, and choose "Print Field Codes instead of their values". When you print the report, you will get the full field code, which will reference the Excel cell that's referenced.
Hope that helps,

 

[Xceller]

Another trick I learned today. You are awesome!