Follow-up: Merging documents through an inclusion list with VBA

This exercise was originally posted at: http://maritimeorca0.blogspot.com/2012/12/merging-documents-through-inclusion.html

The Task

With Visual Basic for Applications (or a similar language for those who do not use Microsoft Office), merge word processing documents from a spreadsheet-based inclusion list.

Download or copy the contents from an inclusion list and three Word files:

Inclusion List	Link
Sharman, Buck	Link
Curry, Tom	Link
Emery, Marcello	Link

At this time, the inclusion list will include two worksheets. Full shows all entries that are currently being kept track of. Inclusion1 is the worksheet that you will be using for this exercise. Word files are going by a strict Last, First format… at least for now. Use the inclusion list to automatically generate a single document that combines the contents of the Word files for the two people listed into a single word processing document. Feel free to test the third person to ensure proper function.

My Solution

You can download my macro-enabled spreadsheet at:
https://skydrive.live.com/redir?resid=2E6EFB80915ED5BD!4007

The macro code can be seen below with comments (Since this blog wraps with shorter lines than VBA, I have alternated colors to reflect each line.)

Sub Inclusion()
'Declaration for accessing the Word application
Dim wdApp As Word.Application
'Declaration for the Word Document that will be created
Dim wdSrc As Word.Document
'Declaration for this Excel workbook
Dim wbBook As Workbook
'This variable will be used to go through rows one by one
Dim rowCount

'By setting up a variable (declared earlier) to point to this workbook, we can reference it's location. This code requires the path of the source documents to be in the same folder as this Excel spreadsheet.
Set wbBook = ThisWorkbook
'This next line starts up Word.
Set wdApp = CreateObject("Word.Application")

'The following line has been commented out. By making Word visible, we can follow the Macro and debug. Since the formula is working, we can hide the tasks in the background.
'wdApp.Visible = True

'We will create a new document and save it into the same folder as this Excel spreadsheet. We are saving it now in case we need to switch between open Word documents.
wdApp.Documents.Add
wdApp.ActiveDocument.SaveAs Filename:=wbBook.Path & "\Inclusion1.docx"

'The rowCount variable will start with the first row that contains people to be included. Since there are headers in the first row, data starts in the second.
rowCount = 2

'This block of code checks the row currently selected for text. If there are any contents, the code will find the appropriate file and copy it to the end of Inclusion1.
While Worksheets("Inclusion1").Cells(rowCount, 1).Value <> ""
'Open the file indicated by the row. Files must conform to a strict last name followed by a first name with a comma and space in between.
Set wdSrc = wdApp.Documents.Open(wbBook.Path & "\" & Worksheets("Inclusion1").Cells(rowCount, 2).Value & ", " & Worksheets("Inclusion1").Cells(rowCount, 1).Value & ".docx")
'Select all the text for the person's entry and copy it. Since this is all we need from this file, we can also close it.
wdApp.Selection.WholeStory
wdApp.Selection.Copy
wdApp.ActiveDocument.Close
'Switch to Inclusion1, move to the end, and paste the entry.
wdApp.Windows("Inclusion1.docx").Activate
wdApp.Selection.EndKey Unit:=wdStory
wdApp.Selection.PasteAndFormat (wdFormatOriginalFormatting)
'Add to rowCount before going back to the beginning of the While statement
rowCount = rowCount + 1
Wend

'Save and close Inclusion1 and reclaim memory.
wdApp.ActiveDocument.Save
wdApp.ActiveDocument.Close
wdApp.Quit
Set wdApp = Nothing
Set wdSrc = Nothing
End Sub

 

Notes

This is definitely among my more difficult exercises so far. Prior to this exercise, I have never even used macros in Word. I was unsure about my ability to succeed. It took me less than one day to figure it out.

Data from the files for this project will likely change over time as I revisit the project for added ideas and features.

I do not provide space to those who visit my blog. If you would like to share any files, you are going to have to find your own online storage. At that point, you can provide links with your comments. I would like to point out that macros can create security risks. If you are going to download any solutions, make sure that you are not loading macros by default, and check the code before running them.

 

Feel Free to Comment

Now that this exercise has concluded, feel free to share any thoughts that you had regarding results, your approach, and any other ideas that popped in your head regarding this exercise.

About Sharing

Nobody should copy my work and then take credit, but I will not aggressively fight to receive credit. This is because most people who use Office are primarily focused on the results rather than VBA code. I still want my name included in the code (commented out by adding an apostrophe before the line) if you are copying and pasting. If you feel that enough has been changed or added, you can drop the credit. Unless otherwise specified, similar guidelines should apply to solutions from other contributors.

Upon completion of this exercise, please fill out the following form:
https://docs.google.com/spreadsheet/viewform?formkey=dHZNV05tNjB0UndFNzVFWi1xZy1pVmc6MA
Responses to the above form are collected and stored in a spreadsheet that can be viewed at:
https://docs.google.com/spreadsheet/pub?key=0Agcdh--NHOS0dHZNV05tNjB0UndFNzVFWi1xZy1pVmc&output=html