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

[mstav]

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!

 

[Macropod]

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.

 

[mstav]

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.

 

[mstav]

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.

 

[Macropod]

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:

List of field codes in Word - Microsoft Support

An alphabetized list of field codes available for mail merge, forms, and other uses in your documents.

support.microsoft.com

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

Linking from Excel - Help auto-resizing tables

[Solved] Linking from Excel - Help auto-resizing tables Word

www.msofficeforums.com

• 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

Word Fields and Relative Paths to External Files

[Solved] Word Fields and Relative Paths to External Files Word

www.msofficeforums.com