One of the biggest limitations of the previous letter is that specific cells in the Excel spreadsheet have to be named and copied to the form letter. What if the content in the spreadsheet could be any size and in any location? The second letter attacks that problem. The first thing that is done to work on the second letter is performed by a button that was added to the previous UserForm that simply dismisses that one and opens another one:
Private Sub btnClosePhrases_Click()
frmDocPhrases.Hide
frmGetExcelData.Show
End Sub
One way to solve the problem of selecting a random area from the spreadsheet would be to identify exactly what cells have been selected and then place them in some sort of "container" to be picked up by the code in the Word VBA. Another way would be to pass back the location of the cells. Unfortunately, according to Microsoft, there is no "Active Range" property. But Excel does support the Clipboard! This reduces our effort to just two lines of code inside a subroutine:
Private Sub btnReturn_Click()
Selection.Copy
SendKeys "%{F4}", True
End Sub
Ordinarily, the statement Application.Quit would be used to close Excel and return to Word. But this doesn't work in this case. Actually, Application.Quit doesn't work for a lot of people and the Web is full of complaints about how to get around the problem. Sending the keystroke shortcut that simply closes down any application (Alt-Function4) does the trick, however. The True parameter of SendKeys allows the Alt-F4 to complete before returning control to the macro and can potentially avoid timing problems.
The button that is used to trigger the event code shown above is placed directly onto the worksheet. This is an example of what is called an ActiveX control and it's a legacy component of VBA that is still completely supported. The illustration below shows the ActiveX controls in the Excel Toolbox.
--------
Click Here to display the illustration
--------
When the Word document (and the UserForm with buttons) is redisplayed, the content of the clipboard is simply pasted at a bookmark in a way very similar to the first letter and the Excel application is closed. At this point, the Word document can be further customized. (But it should never be saved since the original document is the "template" and shouldn't be changed. Doing something about this problem with a "real" Word template is another way the application could be improved.)
Private Sub btnPasteExcelData_Click()
Selection.GoTo What:=wdGoToBookmark, Name:="ExcelContent"
Selection.Paste
myExcelApp.Quit
Set myExcelApp = Nothing
Me.Hide
End Sub
Two other very minor notes. The Caption property doesn't provide a way for embedding line breaks, so the Caption is initialized in the Workbook_Open event where vbNewline characters can be embedded using code. Also, if you're interested in random number programming, I also decided to simply pick a "Salutation" field for the second letter at random.
Download the application and give it a try! Click Here to download. Remember to change the {path} to match your own directory structure, and remember that this is just a "starter" application. Use the ideas here to build a real one of your own!
