Can Excel be used to generate a seperate Word document?

gmayor · #9 07-12-2016, 09:11 PM

You can do this either from Excel or from Word. Which is better rather depends on what you propose the users are going to do with the worksheet. If they are not making changes to the worksheet but merely making selections in a userform from data on the worksheet, then I would do it all in Word. If they are making changes to the worksheet and creating a document based on those changes, then I would do it in Excel. Either way you create a document from your template and write values to it from the worksheet (or based on the worksheet).

There are two functions available from my web site that will help. The xlFillList function at http://www.gmayor.com/Userform_ComboBox.html will read the worksheet into a userform list box or combo box from where a user can make suggestions. The box in question will be multi column so all the data required from each record is in the box, so you can process the selected record from the data in the columns of the box.

The other function is the FillBM function at http://www.gmayor.com/useful_vba_functions.htm which can be used to write a value from the userform to a bookmark in the document.

Working from Excel, you can read the values directly from the worksheet, but the fillBM function will need modifying to work with the specific document you are creating and not ActiveDocument.

The code to create a document from Excel would be

Code:

Dim wdApp As Object
Dim wdDoc As Object
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err Then
        Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    Set wdDoc = wdApp.Documents.Add(Template:="C:\Path\TemplateName.dotx")
    'Do stuff with wdDoc

The bigger problem is how the users are going to have access to the worksheet, and the template in order to create the document.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Importing Data from seperate closed excel file	KingoftheKassel	Excel Programming	1	06-10-2016 08:34 AM
generate and save document versions with different print areas	schroedercn	Word	1	05-20-2014 06:01 AM
Generate Excel Speadsheet from Access, Web, or???	chrisalter	Excel	0	07-12-2011 01:04 PM
generate unique number on same document when printed	benjii19	Word	1	03-08-2011 12:38 AM
Cannot generate 2003 Excel reports in IIS7	sword.fish	Excel	0	02-22-2010 01:43 PM