Archive for February, 2013

Link Dynamic Range Excel Reports to Word

February 24, 2013 2 comments

We can link any kind of Excel Reports to Word for making of report journals. However most of the times Excel pivot table reports that can expand over the selection so integration of dynamic range pivot table to word will be some tricky.
We will use simple Excel functions COUNTA and INDIRECT to make reference of Excel dynamic ranges and intern link this Excel dynamic range to word.
The original idea is for this task is from my team lead Peter Schmidt during PoC to one of my client
Step 1: Create a Pivot table report from PowerPivot model as
Excel Pivot Table
Step 2: Calculate dynamic cell range of pivot table using COUNTA function as
Dynamic cell range
Step 3: Reference the dynamic range cell to a name using Excel Formula – Name Manager. Use the INDIRECT function to refer the cell range as
Name range
Step 4: Select the range of Pivot table and paste into word using paste spatial as
Paste special option
Step 5: Select the pasted pivot table and update link then click on Alt+F9 to check the connection string including the cell range
Excel link
Step 6: Update the cell range R1C1:R5C2 using name range SalesPivotRange created from step 3
Name range
Step 7: Expand the details on Excel Pivot table and update the link on word as
Excel range updates
Word links to dynamic range

Categories: Misc Tags: