Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.tables
|
|||
|
|||
Multiple Links from Word 2003 table to Excel 2003
Stating the version of my office package for the previous post:
I have a Word template with dozens of links to an Excel spreadsheet. Currently when I change the source data in Excel, I have to got to the Word table, I click Edit, Link and then Change Source for every entry in the Word Table. Is there a way to change the source file once and have all the items in the Excel source file be automatically updated to the links in my Word table? The format of the Excel sheet does not change, the links always point to the same place, it is only the name of the spreadsheet which changes, and the content obviously. |
#2
Posted to microsoft.public.word.tables
|
|||
|
|||
Multiple Links from Word 2003 table to Excel 2003
I have had a maro written to "redirect" the links to the new Excel file.
Here it is: Sub RedirectLinks() ' ' RedirectLinks Macro ' Macro created 1/31/2007 by Craig ' Dim alink As Field, linktype As Range, linkfile As Range Dim linklocation As Range, i As Integer, j As Integer, linkcode As Range Dim Message, Title, Default, Newfile Dim counter As Integer counter = 0 For Each alink In ActiveDocument.Fields If alink.Type = wdFieldLink Then Set linkcode = alink.Code i = InStr(linkcode, Chr(34)) Set linktype = alink.Code linktype.End = linktype.Start + i j = InStr(Mid(linkcode, i + 1), Chr(34)) Set linklocation = alink.Code linklocation.Start = linklocation.Start + i + j - 1 If counter = 0 Then Set linkfile = alink.Code linkfile.End = linkfile.Start + i + j - 1 linkfile.Start = linkfile.Start + i Message = "Enter the modified path and filename following this " Format " & linkfile " Title = "Update Link" Default = linkfile Newfile = InputBox(Message, Title, Default) End If linkcode.Text = linktype & Newfile & linklocation counter = counter + 1 End If Next alink End Sub When you run it, it prompts you for the new full path and file name to link to. You will notice the prompt is seeded with the current full path and file name with some oddities: the backslash directory delimiter is always input twice ("//" instead of "/"). I dont know why that is but you have to always use the double backslash. I hope this helps, Craig "Cyberjeff" wrote: Stating the version of my office package for the previous post: I have a Word template with dozens of links to an Excel spreadsheet. Currently when I change the source data in Excel, I have to got to the Word table, I click Edit, Link and then Change Source for every entry in the Word Table. Is there a way to change the source file once and have all the items in the Excel source file be automatically updated to the links in my Word table? The format of the Excel sheet does not change, the links always point to the same place, it is only the name of the spreadsheet which changes, and the content obviously. |
#3
Posted to microsoft.public.word.tables
|
|||
|
|||
Multiple Links from Word 2003 table to Excel 2003
' Macro created 26/10/01 by Doug Robbins to update links in a document
' Dim alink As Field, linktype As Range, linkfile As Range Dim linklocation As Range, i As Integer, j As Integer, linkcode As Range Dim Message, Title, Default, Newfile Dim counter As Integer counter = 0 For Each alink In ActiveDocument.Fields If alink.Type = wdFieldLink Then Set linkcode = alink.Code i = InStr(linkcode, Chr(34)) Set linktype = alink.Code linktype.End = linktype.Start + i j = InStr(Mid(linkcode, i + 1), Chr(34)) Set linklocation = alink.Code linklocation.Start = linklocation.Start + i + j - 1 If counter = 0 Then Set linkfile = alink.Code linkfile.End = linkfile.Start + i + j - 1 linkfile.Start = linkfile.Start + i Message = "Enter the modified path and filename following this Format " & linkfile Title = "Update Link" Default = linkfile Newfile = InputBox(Message, Title, Default) End If linkcode.Text = linktype & Newfile & linklocation counter = counter + 1 End If Next alink -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Cyberjeff" wrote in message ... Stating the version of my office package for the previous post: I have a Word template with dozens of links to an Excel spreadsheet. Currently when I change the source data in Excel, I have to got to the Word table, I click Edit, Link and then Change Source for every entry in the Word Table. Is there a way to change the source file once and have all the items in the Excel source file be automatically updated to the links in my Word table? The format of the Excel sheet does not change, the links always point to the same place, it is only the name of the spreadsheet which changes, and the content obviously. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Links from Word table to Excel | Tables | |||
Email a Word 2003 file maintaining links to Excel file | Microsoft Word Help | |||
Outlook 2003 Cant send e-mail from Excel 2003 or word 2003 | Microsoft Word Help | |||
How do I convert a Excel 2003 file into a Word 2003 file (table)? | Microsoft Word Help | |||
I am having trouble printing multiple copies in Word 2003. Excel. | Microsoft Word Help |