Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Create Word Doc from Template in Excel macro (Excel/Word 2000 sp3)

    I want to create a word document based on a template. I want to do this in a macro in an Excel workbook. I found code to create a new document but I can't figure out how to make that new document based on an existing template (preferable one that is not stored in the Template folder. Can I supply the path?).

    To back up a bit, I want to jump out of Excel to Word so the user can create some text with all the Word bells and whistles and then copy that text back into the Excel cell. If there's a better way to do this, please let me know. My approach at this point is to have a command button in the title row of a spreadsheet with text telling the user to select the cell to edit, then click the button. The code behind the button should copy the text from the selected cell, if there is any there, create a Word window, paste in the text, allow the user to change what they want, then copy the text back into the cell. I want the template so I can give the user more instructions and provide a button in the document to initiate the code to copy the new text back and close the document. Or am I nuts??
    Any help you can give would be most appreciated!
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Create Word Doc from Template in Excel macro (Excel/Word 2000 sp3)

    Your first question is simple: say that you have created a Word application object objWordApp. To create a now document based on a specific template, use

    Dim objWordDoc As Word.Document
    Set objWordDoc = objWordApp.Documents.Add Template:="C:MyTemplatesTest.dot"

    But I have serious doubts about what you ask in the second part. Once you hand over control the document to the user, your macro loses control over it. Moreover, the user can do lots of things in Word that don't make sense in an Excel cell, so I don't think it is a good idea to use Word as a kind of cell editor.

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Create Word Doc from Template in Excel macro (Excel/Word 2000 sp3)

    Thanks HansV, for always responding! I am getting a compile error with this statement, "Expected: end of statement" and the word "Template" is highlighted. Here's my statement pasted in and I don't see my mistake:

    Set oDoc = oWord.Documents.Add Template:="c:WordTemplateFromExcel.dot"

    I'm real confused on the syntax rules (hard to believe I got this far without understanding some of the basics). When I type an open paren after the word "Add" in the statement above I get a helpful syntax guide in a yellow box that looks like this (sorry, the square brackets aren't working. I used B to indicate them:

    Add (BTemplateB, BNewTemplateB, BDocumentTypeB, BVisibleB) As Document

    Why is this syntax different than the one you showed with the keyword followed by the colon? How did you figure out it meant that, anyway?

    I agree with your concerns about the wisdom of this approach. I need to demo this so I can show our mutual point that formatting, styles, etc. will be lost. We may move to store the edited text elsewhere (maybe as a Word doc with a generated name we store in a hidden cell in the spreadsheet) so it can retain the attributes and just paste it in the cell so they can see a hint of what they have. The reason for this whole exercise is that the user is not happy with the limited ability of Excel to handle text in the cell. We have a rather complicated Excel application with tons of dialog boxes and entering this text (comments) often exceeds the limits that cause problems with spell checking, auto row height and other problems. Word has been suggested as a solution but integrating it with Excel this way is new to me. I'd love to hear any suggestions you may have, it is always informative to hear from you!
    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Create Word Doc from Template in Excel macro (Excel/Word 2000 sp3)

    I'm sorry, the error is mine, it was air code I didn't actually test. We are calling Add as a function here, so we must enclose the argument in parentheses:

    Set oDoc = oWord.Documents.Add(Template:="c:WordTemplateFromE xcel.dot")

    If advanced text editing is so important to the user, Excel might not be the most appropriate program. I don't know anything about your application, so I can't give specific advice.

  5. #5
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Create Word Doc from Template in Excel macro (Excel/Word 2000 sp3)

    Thanks, that makes it more clear. My code has an error routine that pops up a msgbox with "Word caused a problem. " & Err.Description, vbCritical, "Error: " & Err.Number. Sorry to be a pest, considering that you have definitely steered me in the right direction, but the statement now causes this error when I invoke the macro:

    Error: 2147417851 Word caused a problem. Method 'Add' of object 'Documents' failed.

    When I step through the de###### I get this error:

    Error: 2147417851 Word caused a problem. Automation error The server threw an exception.

    Perhaps I messed up the opening of word in the first place. Here's that code:

    Dim oWord As Word.Application
    Dim WordWasNotRunning As Boolean
    Dim oDoc As Word.Document
    Dim oPathDoc As String

    'Get existing instance of Word if it's open; otherwise create a new one
    On Error Resume Next
    Set oWord = GetObject(, "Word.Application")
    If Err Then
    Set oWord = New Word.Application
    WordWasNotRunning = True
    End If

    On Error GoTo Err_Handler

    oPathDoc = ActiveWorkbook.Path & "WordTemplateFromExcel.dot"
    'Set oDoc = oWord.Documents.Add(Template:=oPathDoc)
    Set oDoc = oWord.Documents.Add(Template:="C:WordTemplateFromE xcel.dot")
    'Set oDoc = GetObject(oPathDoc)
    With oDoc
    .Parent.Visible = True
    .Activate
    End With

    Any ideas?
    I was hoping to use the path but that generates another error so I moved the template to the root for now. I left in some of the old code but it is commented out.
    Thanks
    Phil

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Create Word Doc from Template in Excel macro (Excel/Word 2000 sp3)

    If I substitute the path and filename of a template existing on my PC into your code, it runs ok: it creates a new document based on the template, and brings the Word window with the document to the foreground. So make absolutely sure that you have used the correct path and filename. Even the smallest typo will cause the code to fail.

  7. #7
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Create Word Doc from Template in Excel macro (Excel/Word 2000 sp3)

    Right you are Hans! Somehow I corrupted the template I was building and I can't even open that by itself at all. When I used another template it worked like a charm. Thanks very much for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •