Create VBA Tools - Linked Word & Excel Files / MailMerge?

mstav

New Member
Joined
Jan 7, 2025
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Web
In my line of work, virtually every company uses some combination and variation of VBA tools that essentially links Excel & Word documents to produce ±100 page report deliverables. Most of this is done through VBA tools either made by 3rd party technology solution providers, and some are replicated internally. While these VBA tools are more broad in scope and functionality, I'm primarily interested in replicating a small portion of it, the "linking". I believe the VBA tools are really just UI shortcuts of the existing linking/embedding infrastructure of Word & Excel.

Here's what I generally understand of the process:

  1. The Excel file is a template with a vast majority of the tables, text cells, etc. all having named ranges such as "inpAddress" for a text cell with a property address, or "TblExpenses" for a table summarizing expenses.
  2. The Word file template then allows the user to "update" all the pre-linked tables and ranges through either a shortcut in a custom toolbar (i.e. "Update All" button), or via a right-click option on the linked objects in the Word document.
  3. I believe the VBA tool itself is in Word, as all the functions are typically done while in the Word document (i.e. the Toolbar is in the Word, all "updating" or pasting new links takes place in Word, etc.
  4. If the Excel file is moved, renamed, etc. then you typically have to "re-link" the Word document to the new file or location via a pop-up window to navigate.
I'd compare this functionality to Mail Merge, but my experience with Mail Merge is limited and does not seem to accomplish the same functionality.

If any of this is unclear, I can try to expand or re-phrase the descriptions. Any guidance, direction, or other feedback on how to achieve something like this would be greatly appreciated!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
What you describe is a set of Word documents using LINK fields and/or DATABASE fields - neither being a mailmerge process - to connect them to various ranges in one or more Excel workbooks.

What isn't clear is what the problem is that you're trying to solve.
 
Upvote 0
What you describe is a set of Word documents using LINK fields and/or DATABASE fields - neither being a mailmerge process - to connect them to various ranges in one or more Excel workbooks.

What isn't clear is what the problem is that you're trying to solve.
Okay, that makes more sense based on what I’ve seen over the years of the various coding and add-ins. I was pointed to Mail Merge on a different forum.

I’m essentially looking to replicate or produce something similar to what I described above. More specially, I’m really just trying to figure out to create this VBA tool to use with my own custom word and excel documents.

For the purpose of the forum, I’d say I’m trying to learn how to produce this for say a single table or field and teach myself from there.
 
Upvote 0
Maybe another way to phrase what I’m looking to accomplish:

1. I am writing reports for 10 seperate properties. I have created a word and excel template to use for each property. I then update each Excel with each property’s data.
2. Each report will have the same 10 tables for each property. In the Excel’s for each property, I have utilized the same naming convention for each table (Table1, Table 2,…).
3. I want to be able to be able to update all the tables in the respective Word docs at once, instead of manually re-pasting every table in each respective Word document.

My guess is creating a database or list of the named ranges and fields in Excel, then writing the VBA code to allow me to link to the different excels, and run a macro to delete and paste all the named ranges in the Word document with the now updated tables from the respective excels.
 
Upvote 0
Without knowing more about what your source data look like and how they're meant to be represented in the Word document, it's impossible to give any specific guidance.

To link a Word document to an Excel range is as simple as copying the Excel range, then pasting it into Word using Paste Special with the 'paste link' option and choosing the desired paste format. Similarly, inserting an Excel range into a Word document via a DATABASE field - which gives many more layout options than simply linking to an Excel range, is as simple as using Insert|Quick Parts|Field>Database>Insert Database>Get Data, then choosing the appropriate Query options and Table format. More advice on all Word field parameters can be found via:

An example of:
• LINK field usage that enables the linked range to automatically adjust to match a named range in Excel can be found at
• DATABASE field usage can be found at
Mail merge into different coloumns
• Field coding to implement relative field paths, plus a macro for updating LINK field paths can be found at
 
Upvote 0
Thank you for the guidance thus far!

After a couple days of browsing the provided links, I've learned a little more and think I can better explain what I am looking to accomplish. The closest "alternative" I was able to achieve was:
  1. Creating a blank Word and Excel file (TESTING.docm & TESTING.xlsm).
  2. In the Excel on 'Sheet1', I created a sample table and named the range as 'Table1'.
  3. In the Word, I used Insert|Quick Parts|Field>Links and References>Link>LINK Excel.Sheet.8 "C:\\Users\\first.last\\Documents\\TESTING.xlsm" "Sheet1!Table1"
  4. This inserted the 'Table1' from 'Sheet1' as intended, I was then able to replicate this for 'Table2' from 'Sheet2' as as intended, and so on.
This essentially achieves the underlying goal of linking tables into the word. My end goal is to create the following:
  1. I would like to create a macro to speed up this process for pasting in new links. Ideally something relatively simple as:
    1. In an Excel workbook, highlight or select the named range (i.e. 'Table1'), and "Copy" it to the clipboard.
    2. In the Word document, "Paste" the selection as is done via the process noted above.
  2. I would like to create a macro to update all the linked fields at once. This can currently be done by going to File|Info|Edit Links to Files>select all links>Update Now.
  3. I would like to create a macro to allow a User to change the Source File workbook of all links without changing the links themselves (i.e. Change from TESTING.xlsm to TESTINGv2.xlsm, but still using the same "Item in File" like 'Sheet1!Table1'.
If you or anyone has any guidance, feedback, or a direction I can research for myself for any of those items, it would be greatly appreciated.
 
Upvote 0
In regards to #2 for "Update All", my current solution is:
  1. I was able to "Record Macro" for the following 2 macros which appears to work (see below).
  2. Added a Custom Tab to the Ribbon in Word.
  3. Created a New Group under the Custom Tab.
  4. Added 2 separate buttons to the 2 macros in the Custom Tab.
2 macros coding:

Sub UpdateAllLinks()
'
' UpdateAllLinks Macro
'
'
Selection.WholeStory
Selection.Fields.Update
End Sub

Sub UpdateSelectLink()
'
' UpdateSelectLink Macro
'
'
Selection.Fields.Update
End Sub
 
Upvote 0
In regards to #3 for "Change Source File", I was able to find the following solution (then added to my Custom Tab in the Toolbar):

VBA Code:
Sub ChangeSourceFile()
Dim dlgSelectFile As FileDialog 'FileDialog object '
Dim thisField As Field
Dim selectedFile As Variant
'must be Variant to contain filepath of selected item
Dim newFile As Variant
Dim fieldCount As Integer '
Dim x As Long
On Error GoTo LinkError
'create FileDialog object as File Picker dialog box
Set dlgSelectFile = Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
With dlgSelectFile
   .Filters.Clear 'clear filters
  .Filters.Add "Microsoft Excel Files", "*.xls, *.xlsb, *.xlsm, *.xlsx" 'filter for o    nly Excel files
  'use Show method to display File Picker dialog box and return user's action
  If .Show = -1 Then
 'step through each string in the FileDialogSelectedItems collection
    For Each selectedFile In .SelectedItems
         newFile = selectedFile 'gets new filepath
       Next selectedFile
     Else 'user clicked cancel
   Exit Sub
  End If
End With
Set dlgSelectFile = Nothing
'update fields
With ActiveDocument
  fieldCount = .Fields.Count
  For x = 1 To fieldCount
    With .Fields(x)
     'Debug.Print x '
     Debug.Print .Type
      If .Type = 56 Then
        'only update Excel links. Type 56 is an excel link
        .LinkFormat.SourceFullName = newFile '
        .Update
        .LinkFormat.AutoUpdate = False
        DoEvents
      End If
    End With
  Next x
End With
MsgBox "Source data has been successfully imported."
Exit Sub
LinkError:
Select Case Err.Number
  Case 5391 'could not find associated Range Name
    MsgBox "Could not find the associated Excel Range Name " & _
      "for one or more links in this document. " & _
      "Please be sure that you have selected a valid " & _
      "Quote Submission input file.", vbCritical
      Case Else
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
    End Select
End Sub
 
Upvote 0
If you create a Word template with all your boilerplate content and basic LINK fields in place, then all you'd need to do after creating and saving a new client workbook, then creating a new document from that template, is tell the LINK field where to point to to get the table data. That could be automated by inserting your code into the Word template's 'This Document' code module and renaming it:
Private Sub Document_New()

Then, to ensure the fields update when the macro is run, insert:
VBA Code:
  .Fields.Update
  .PrintPreview
  .ClosePrintPreview
between:
Excel Formula:
  Next x
End With

To avoid unwanted screen flickering, you should also insert:
VBA Code:
Application.ScreenUpdating = False
before:
VBA Code:
With ActiveDocument
and insert:
VBA Code:
Application.ScreenUpdating = True
before:
VBA Code:
MsgBox "Source data has been successfully imported."

The other thing you should do is ensure Word's options for:
• Update fields before printing;
• Update linked linked data before printing; and
• Update automatic links at open,
are all checked. Simply doing a Print Preview should then be sufficient to update all the fields, without the need for any code.
 
Upvote 0

Forum statistics

Threads
1,226,287
Messages
6,190,059
Members
453,592
Latest member
bcexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top