How to: Have two templates cross-reference each other independent of filename path

HighSierra · #1 05-01-2015, 08:52 PM

Hi all,

I have two documents: A and B. I'd like info that is inputted into A to match certain fields in B. I've done this by adding bookmarks to A and then in B doing this: insert > object> text from file > specifying a range > inserting as link. And this works great except I am concerned my users may move some of the documents, and more likely, that the file name could be changed. Actually, since these are just template files, the names would certainly be changed.

Is there a better way to do this? Mail Merge? Can I use some VBA coding to do a doc search? Any help would be so, so appreciated!

Thanks!

gmayor · #2 05-01-2015, 09:45 PM

What kind of information are you inserting from the other document?
Are these items words or phrases, or long lengths of text? And what is the process involved that relates to the input of information in one of the documents and its translation in the other? Without more information or the documents themselves, finding a better solution would be difficult.

HighSierra · #3 05-01-2015, 10:52 PM

The information is all text based, and not more than one to two short sentences. As far as the process, a user would manually enter the info in the doc and then we want the other one to auto-populate with this info.

I have tried recreating a dummy doc to post here, and I think it should work as long as you have the two documents on your desktop...

gmayor · #4 05-02-2015, 01:09 AM

Actually it wouldn't work for the very problem that you have highlighted. The paths don't match and so the fields cannot find the data. It will work if you change the paths in the fields, but that will be beyond many users.

I wouldn't do it that way. I would write the values from the source directly to the target document, but this means the use of macros.

Based on your example, I would a couple of standard functions to your document to run the update when the document is opened.

In the ThisDocument module of Doc B add the following code:

Code:

Option Explicit

Private Sub Document_Open()
    ModMain.UpDateDocument
lbl_Exit:
    Exit Sub
End Sub

Insert a new standard module in the document project. Name it ModMain and insert into that module:

Code:

Option Explicit

Sub UpDateDocument()
Dim strFile As String
Dim oSource As Document
Dim oTarget As Document
Dim oSourceTable As Table
Dim oTargetTable As Table
Dim oSourceCell As Range
Dim oTargetCell As Range
    strFile = "C:\Path\Doc A.doc"
    If Not FileExists(strFile) Then
        strFile = BrowseForFile("Select the file containing the linked texts")
    End If
    If strFile = vbNullString Then
        MsgBox "No file selected"
        GoTo lbl_Exit
    End If
    Set oTarget = ActiveDocument
    Set oSource = Documents.Open(FileName:=strFile, AddToRecentFiles:=False, Visible:=False)
    Set oSourceTable = oSource.Tables(1)
    Set oTargetTable = oTarget.Tables(1)

AccCrit_Spec:
    Set oSourceCell = oSourceTable.Cell(2, 2).Range
    oSourceCell.End = oSourceCell.End - 1
    Set oTargetCell = oTargetTable.Cell(2, 2).Range
    oTargetCell.End = oTargetCell.End - 1
    oTargetCell.FormattedText = oSourceCell.FormattedText

Results_Spec:
    Set oSourceCell = oSourceTable.Cell(2, 3).Range
    oSourceCell.End = oSourceCell.End - 1
    Set oTargetCell = oTargetTable.Cell(2, 3).Range
    oTargetCell.End = oTargetCell.End - 1
    oTargetCell.FormattedText = oSourceCell.FormattedText

AccCrit_Precision:
    Set oSourceCell = oSourceTable.Cell(3, 2).Range
    oSourceCell.End = oSourceCell.End - 1
    Set oTargetCell = oTargetTable.Cell(3, 2).Range
    oTargetCell.End = oTargetCell.End - 1
    oTargetCell.FormattedText = oSourceCell.FormattedText

Results_Precision:
    Set oSourceCell = oSourceTable.Cell(3, 3).Range
    oSourceCell.End = oSourceCell.End - 1
    Set oTargetCell = oTargetTable.Cell(3, 3).Range
    oTargetCell.End = oTargetCell.End - 1
    oTargetCell.FormattedText = oSourceCell.FormattedText
    
    oSource.Close SaveChanges:=wdDoNotSaveChanges
    MsgBox "Document updated"

lbl_Exit:
    Set oSource = Nothing
    Set oSourceTable = Nothing
    Set oSourceCell = Nothing
    Set oTarget = Nothing
    Set oTargetTable = Nothing
    Set oTargetCell = Nothing
    Exit Sub
End Sub

Public Function FileExists(strFullName As String) As Boolean
Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists(strFullName) Then
        FileExists = True
    Else
        FileExists = False
    End If
lbl_Exit:
    Exit Function
End Function

Function BrowseForFile(Optional strTitle As String) As String
Dim fDialog As FileDialog
    On Error GoTo err_Handler
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
        .Title = strTitle
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Word documents", "*.doc,*.docx,*.docm"
        .InitialView = msoFileDialogViewList
        If .Show <> -1 Then GoTo err_Handler:
        BrowseForFile = fDialog.SelectedItems.Item(1)
    End With
lbl_Exit:
    Exit Function
err_Handler:
    BrowseForFile = vbNullString
    Resume lbl_Exit
End Function

The default path is defined at the start of the macro. If the macro cannot find that file it will prompt for it. It then updates the cells that had contained fields with the formatted contents of the appropriate cells from the source document. The source document is then closed without saving.

See http://www.gmayor.com/installing_macro.htm

HighSierra · #5 05-02-2015, 07:34 PM

AWESOME! yeah, I was thinking of something like this too, but wasn't sure of how to go with it. I am wondering if it would be possible to just grab whatever the bookmark contains, rather than defining a range in the table. This way, if in some weird future state they change the table up, the bookmarks would still be the same. I am going to play with this to see how that would work...

gmayor · #6 05-02-2015, 11:29 PM

You could use the bookmarks by setting the range to the bookmark rather than the table cell content, but bookmarks have an unfortunate way of being over-written, so the table cell is the better approach. Table cell (1,1) will always be table cell (1,1) even if the bookmark it contains is missing. It is easy enough to change the cell addresses is the layout chyanges.

HighSierra · #7 05-06-2015, 07:04 PM

Ah, yes that makes sense.

So, funny enough, I presented the idea and we decided to go with a drop-down of quick parts for the table since the inputs will not vary.

Now we have a single variable that we want to manually define so I am working on a way to get that to auto-input in a bunch of places in the doc. I'll be putting that up as a separate question. Thanks so much for the help!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Reference number and cross reference for equation numbers to match the thesis format	wmac	Word	1	05-14-2013 08:54 PM
Need help with using bookmark and cross-reference	mpdsal	Word	1	07-26-2012 01:05 PM
Cross-reference in two lines!!!!!	Jamal NUMAN	Word	3	04-12-2011 06:47 AM
Reference to an external filename which changes frequently	roooberta	Word	0	12-11-2008 07:33 AM