Embedded links to Excel data

[silverback]

I produce an audit document for a local charity. It comprises a Word document and a companion Excel spreadsheet.
For each audit question the spreadsheet contains a table of results and a chart built from that data. This is shown in the attached picture. The word document contains, for each question, a two cell, one row table which contains links to the Excel spreadsheet. The left hand cell contains the data table, the right hand one contains the chart. The Excel file currently has its data and charts organised in the same order as the Word document.

The data table in the left hand cell of the Word document is produced by a link which looks like :

{ LINK Excel.Sheet.8 "C:\\Filename\\Excelfile.xls" Carer!R17C2:R21C3 \a \f 4 \r \*MERGEFORMAT }

All works fine. However, the users have amended the audit questionnaire and inserted some questions in various sections.

If I insert rows in the spreadsheet to make space for the data and charts from the new questions, the existing links in the right hand cells of the Word doc continue to work OK since they use the chart name, and this doesn't change. But the links in the left hand cell now point to an area of the spreadsheet which no longer contains the data (because it's been pushed down by inserting the new stuff).

I realise that I could simply add the new question data and charts at the end of the worksheet but I'd like to keep the it organised in the same order as the Word doc.

Is there a simple way to update links of the above type to point to the new location of the data? At the moment, the only way I can think of to do it is to delete each link and make a new one - which will be very tedious since the new questions have been inserted near the beginning, meaning that most of the links need updating.

Thanks
Silverback

[HansV]

I'd name the ranges that you want linked:
- In Excel, select one of the ranges.
- Click in the address box on the left hand side of the formula bar.
- Type a name (no spaces, not resembling a cell address).
- Press Enter.
- Repeat for the other ranges.

In Word, make the links refer to the names instead of to cell addresses, e.g.

{ LINK Excel.Sheet.8 "C:\\Filename\\Excelfile.xls" "Carer!MyRange" \a \f 4 \r \*MERGEFORMAT }

The links should now work the way you want.

[silverback]

Hi, Hans.
Yes - I can see that naming a range makes the link the same type as the chart references - so as the rows get pushed down, the range name stays the same. However, there really are a lot of links so please can you assist with code (that I can put in a macro) to replace all instances of !R2C2:R6C3, !R17C2:R21C3 etc with a specified string? Any chance that the code could add a numeric to the string? So, My replacement string might be "ICTable" and the link replacement macro would generate "ICTable1", "ICTable2" and so on.
Thanks
Silverback

[HansV]

Do all the links in the Word document refer to ranges in the same sheet in the same Excel workbook?

[silverback]

No - there are six sections running from 2 - 7 in the Word doc (0 and 1 are Table Of Contents and Intro respectively) and the links in each section map on to a separate worksheet, all of which are in the same Excel file. So an actual link looks like :
LINK Excel.Sheet.8 "C:\\Manuals\\Audit Docs 2010\\ECH ReportData.xls" DayCarePat!R194C1:R198C2 \a \f 4 \r \* MERGEFORMAT
where "DayCarePat" is the name of the worksheet in Excel file "ECH ReportData.xls"
Silverback

[HansV]

See the attached text file. It contains a longish macro that hopefully does what you want.

Code.txt

Notes:
1) Before running the macro, edit the path and filename in the line

Const strWorkbook = "C:\Filename\Excelfile.xls"

to match your situation.

2) Before running the macro, make a backup copy of both the document and the workbook. Both will be modified!

[silverback]

Hans
The text is installed as a macro, thanks.
Unfortunately, it doesn't run through the entire document - it creates the first link, passes over the chart link associated with that first link, changes the spreadsheet range and then displays the error shown in the attachment.
I can't find a way to establish what it is that's being objected to.
Silverback

[HansV]

Oops, sorry about that. I forgot to take the charts into account. Try the modified version. You'll have to go back to the versions of the document and workbook before you tried the incorrect version of the macro (please make copies again before trying the new version!)

Code.txt

[silverback]

To Hans and this wonderful community of loungers.
All working now. So many thanks. You really are so generous.
In all, there turned out to be 138 existing links, not including the new questions that have yet to be inserted. It's probably going to end up with about 150 links in the revised document and spreadsheet.
Having to delete all these, then remake them - and that only after the task of naming each of the spreadsheet ranges - would have involved a lot of (error prone) work. You have saved me literally hours of work.

I now have a mechanism to update my skeleton document, because I actually produce an audit document for three separate hospices. Each hospice has its own folder containing a copy of the skeleton document together with a copy of the skeleton data spreadsheet to hold that hospice's data. Using your code, I can update the skeleton doc and the skeleton spreadsheet so that the insertion and deletion of questions becomes an error free process which doesn't take enormous amounts of time. Your code has enabled me to 'remake' the skeleton files, which are my starting point.
I also use Macropod's routines obtained from here and these reset the links from an absolute filename to the filename appropriate to whatever folders the two documents reside in. I now have a rigorous basic set up which has been future proofed.

It's difficult to put into words how grateful myself and my hospice clients are to all you loungers who so willingly give your time and expertise. I will try and learn and improve my own skills using these two pieces of VB code, but there is no doubt that I will progress so much faster thanks to everyone's generosity of spirit.

On behalf of my customers, I thank you all again.
Silverback

[HansV]

Thank you very much for your kind words, Silverback. It's really nice to hear that the help given here makes a difference, I appreciate it!