Transfer from Excel to Word
Sending data between Excel and Word is possible using a number of different methods. The method I prefer is to set up a link between Excel and Word. I have found this method produces more consistent results than sending the Excel data via vba to Word. If you use VBA in my experience Excel tended to upload data from Excel into Word a different size and shape each time the code runs.
A way of locking the data down is to create a template in word which is linked to a template in Excel. It is important that the data in Word remains in the same cells each month hence the term Template. In a simple example, I have set up an Excel template with 4 sheets, England, Wales, Scotland and United Kingdom. I have used the Sheet United Kingdom as the Template in word for the procedure.
The following image is the final output where you can click on any of the series to have it either included or excluded.
The idea is to set up a word Template that has a link to table and chart data in the United Kingdom tab of the Excel file.
In the Excel file there are 4 sheets set up identically. These are England, Scotland Wales and a summary sheet for United Kingdom. It is very important that each sheet has been set up in the same manner with every chart and table in the same position.
The procedure will;
- Open the Word document in Field code view (this happens automatically).
- Find all instance of the word United Kingdom and replace with the next Country eg England.
- Break the links to the original Excel file leaving only the data related to the next Country.
- Save the file as eg England.doc
- Reopen the United Kingdom Template and do the same thing with the next country until all the countries have been updated and the respective templates saved.
- There will be 4 files in the folder one for each of the 3 countries and a summary file for Great Britain.
Each file will be different and have data on it relating to the specific Country in the Excel file. It sounds simple however there is a lot which needs to occur before the procedure runs smoothly. Firstly the building of the template needs to occur in Word.
Setting up a Link from Word to Excel
In the Excel workbook copy the table you want updated in the word document.
The following image is the final output where you can click on any of the series to have it either included or excluded.
In the Excel file above the highlighted area is copied.
Now open a blank Word document.
Select Paste Special.
You will know this has been done correctly if you press Alt F9 and have something similar to the following.
This is the coding which sits behind the purple table above. This says that the Link is held in
C:\Test\XlTemplate.xlsx
The data is on the in the range B5:G10 which is represented by R5C2:R10C7(R5 is Row 5, C2 is Column B, R10 is Row 10 and C7 is Column G.
Follow the same procedure with the charts with one minor adjustment when you paste Special the following Dialog will appear.
Once again Paste Link but this time choose Microsoft Excel Chart Object. Do the same until all of the tables and charts have been pasted as links into the word document.
Congratulations you have set up a linked word document to an Excel file.
As the data in your Excel file changes your word document will change with it. This might be as much as many people require. The following The VBA Component of the Project
Secondly in the word document press Alt F11. This takes you into the visual basic editor. In ThisDocument module place the following This takes you into the visual basic editor.
In ThisDocument module place the following lines of code.
Application.ActiveWindow.View.ShowFieldCodes=True
End Sub
This ensures that the word document will open up in Field Code view.
In the Excel file the procedure will be run from Find the Reference to Microsoft Scripting Runtime and ensure the item is ticked.
In the Excel file the procedure will be run from Select Tools - References.
Find the Reference to Microsoft Scripting Runtime and ensure the item is ticked.
The following will link the two documents. In the procedure I have set up the following will create 4 Word Documents. One of each region and a consolidation file.
Sub CreateMultiDocs()
'To ensure this file runs in the VB Editor click Tools, References. Then search for'
'Microsoft Word n.n Object Library (already done on this file though)
Dim wrdApp As Object
Dim wrdDoc As Object
Dim sPath As String
Dim wPath As String
sPath="C:\Test\" 'XL Path Change Path Here
wPath="C:\Test\" 'Word Doc Change Path here.
Set wrdApp=Createobject("Word.Application")
For i=2 To Range("B" & Rows.Count).End(xlUp).Row
wrdDoc.Activate wrdApp.Visible=True
str=Range("B" & i).Value
wrdApp.Selection.Find.Text="United Kingdom"
wrdApp.Selection.Find.Replacement.Text=str
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
wrdDoc.Fields.Update
wrdDoc.Fields.Unlink
wrdDoc.SaveAs sPath & Range("B" & i).Value & ".doc"
wrdDoc.Close False
Next i
