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.

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

<tbody>
</tbody>

 

[martindwilson]

you would have to control it from word you cant "push" from excel into word you can only "pull" info from excel into word. what you are doing is basically mail merge
the merge fields will pull the excel data into the relevant places. you don't specify which version of office you have google mail merge and look for your version

 

[Xceller]

you would have to control it from word you cant "push" from excel into word you can only "pull" info from excel into word. what you are doing is basically mail merge
the merge fields will pull the excel data into the relevant places. you don't specify which version of office you have google mail merge and look for your version

Thanks for for your reply. There is not a Word message board on Mrexcel, do you know where I can get help?

 

[Cindy Ellis]

Even though it's not a Word forum, we're willing to help
The layout of your Excel worksheet is not amenable to Word Merge, which is looking for a single row of data (with labeled fields) for each merge field.
In theory, you should be able to link specified cells in Excel to Word document using Insert Object, then select the workbook and range, but I have not been able to make that work...it might be my version of Excel/Word, or it might be some security setting installed by my company's IT department.
You can create a word document from Excel using VBA, but it still wouldn't be quite what you've asked for. It would essentially re-create the word document from the current Excel data whenever the macro is triggered. If that's close enough I can probably walk you through the steps of setting it up, depending on your version of Excel and Word. Just reply back and let me know that you'd like to give it a try.
Hope that helps,

 

[Cindy Ellis]

After some thought and experimentation, there are 3 reasonable solutions, depending on what works best for you:

• Create a link to the cells in Excel (I think this is what you wanted all along) as follows:
  • Create the document in Word
  • Open the data file in Excel
  • Wherever data should be linked in from Excel, select and copy the cell in Excel.
  • 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.
  • Save the document.
  • To test it out, change some cells in the Excel file, save and close, then open the Word document (with Excel closed or open). You will get a prompt to update linked fields...select OK or Yes. Once it's opened, the word document should contain the new data from Excel.
• Transpose your input data file so that the field names are in the first row, and all of the associated data is in the 2nd row, with the applicable data item just below the field name, and use Word Merge. If you want to use this approach, I can walk you through the steps.
• Use an expanded version of the following macro to create a new version of the Word file whenever the data changes (the code below only handles the first paragraph):

Sub MakeDoc()    Dim myWD As Word.Application
    Dim CurrentPath As String
    Dim DataSheet As String
    Dim SaveFileName As String
    Dim temp As Boolean
    SaveFileName = "Business Results.docx"  'modify as needed
    
    CurrentPath = ActiveWorkbook.Path
    DataSheet = ActiveSheet.Name
    Application.ScreenUpdating = False   'Comment out for troubleshootin...uncomment for normal use
    
    Set myWD = CreateObject("Word.Application")
    myWD.documents.Add
    myWD.Visible = True   'un-comment this line for troubleshooting
    'get the current state of the createbackup property
    temp = myWD.Options.CreateBackup
    myWD.Options.CreateBackup = False
    With myWD
        .Selection.TypeText Text:="OVERVIEW AND BUSINESS TRENDS" + Chr(13)
        .Selection.TypeText Text:="Results for the " & Range("C3").Value & _
                   " Months Ended " & Range("C1").Value & Chr(13)
        .Selection.TypeText Text:="Consolidated revenues for the " & Range("C4").Value & _
                   " quarter of " & Format(Range("C1").Value, "YYYY") & " were $" & _
                   Range("C7").Value & " billion, an " & Range("C8").Value & " of $" & _
                   Range("C9").Value & " million, or " & Format(Range("C10").Value, "##.0%") & ", compared to" & _
                   " the same period in " & Range("C11").Value & ".  For the " & _
                   Range("C3").Value & " months ended " & Format(Range("C1").Value, "Mmmm dd, yyyy") & ", DEG, which we acquired in " & _
                   "September 2010, generated $" & Range("C12").Value & " million in revenues, and ABC " & _
                   "Holdings, Inc. (""ABC""), which we acquired in June 2011, generated $" & _
                   Range("C13").Value & " million in revenues. By comparison, neither of the acquired companies " & _
                   "contributed to our consolidated revenues during the " & Range("C4").Value & " quarter of fiscal " & _
                   "year " & Range("C2").Value & ". During the quarter, revenues " & Range("C19").Value & " 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 " & Range("C4").Value & _
                   " quarter of " & Format(Range("C1").Value, "YYYY") & " was $" & Range("C14").Value & "million compared with $" & Range("C15").Value & " million during the " & _
                   "second quarter of " & Range("C2").Value & ", an increase of " & Format(Range("C17").Value, "##.#0") & ". " & _
                    Chr(13)
        .Selection.TypeParagraph
    End With
    With myWD.ActiveDocument
        .SaveAs Filename:=CurrentPath & "\" & SaveFileName, AddToRecentFiles:=True
    End With
    myWD.Options.CreateBackup = temp
    
    myWD.Quit
    Set myWD = Nothing
    
    AppActivate "Microsoft Excel"


End Sub

Hope that helps,

 

[Xceller]

Hi Cindy,

Thank you so much for your help! I am running both Excel version 2007 and 2010. I am a newbie at vba. When I try to step through your code, it stops at the first line "Dim myWD As Word.Application" and I get this message: " Compile error: User-defined type not defined". Is it something I am not doing right. Would you kindly walk me through the steps I need to take to run the code.

Again, I appreciate your help very much!

 

[Cindy Ellis]

In the VBA editor, choose "Tools", then select "References". In the list of references check the box for Microsoft Word 12.0 Object Library. (Or whatever Word version shows up in the list.)
There may be other problems after that, although it worked for me to create at the first paragraph of your report.
I'm on my way out the door right now but will check back in a couple of hours.

 

[Xceller]

The code is running now. It is not picking up the Excel data. The Excel data workbook("BusRslt") is saved on my desktop. Below is the resulting Word report, Business Results. The Excel data worksheet("BusRslt") is selected when I run the code. I am puzzled.

OVERVIEW AND BUSINESS TRENDS
Results for the Months Ended
Consolidated revenues for the quarter of were $ billion, an of $ million, or , compared to the same period in . For the months ended , DEG, which we acquired in September 2010, generated $ million in revenues, and ABC Holdings, Inc. ("ABC"), which we acquired in June 2011, generated $ million in revenues. By comparison, neither of the acquired companies contributed to our consolidated revenues during the quarter of fiscal year . During the quarter, revenues 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 quarter of was $million compared with $ million during the second quarter of , an increase of .

 

[Cindy Ellis]

In the Excel Code, it's looking for specific cells, so if the data isn't in Column C(or is in different rows), you will need to change the cell references. The data you posted showed all the info in Column C...so that's what I used to determine what goes where in the file. If there's any reasonable chance that the data will be in different locations from time to time, there is another approach, but it would add another chunk of code to what's already there.

 

[Xceller]

In the Excel Code, it's looking for specific cells, so if the data isn't in Column C(or is in different rows), you will need to change the cell references. The data you posted showed all the info in Column C...so that's what I used to determine what goes where in the file. If there's any reasonable chance that the data will be in different locations from time to time, there is another approach, but it would add another chunk of code to what's already there.

My mistake. I corrected the Excel cell reference. It's Working now. Thank you very much for your help! Here is the resulting word report:

OVERVIEW AND BUSINESS TRENDS
Results for the Three Months Ended 7/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 01, 2011, DEG, which we acquired in September 2010, generated $115 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.8million compared with $61.9 million during the second quarter of 2010, an increase of .08.