asked on

Problem when closing MS Word after populating Word template with Excel macro

I am working on an Excel macro to automatically populate a Word template using information from a row in an Excel spreadsheet. The macro code is attached.

The macro works correctly as far as I can tell. But, when I am done with the resulting document and close it, I get a message saying that "This file is in use by another application or user. (C:\Documents and Settings\...\Normal.dot)". I can only click OK or Show Help. Once I click OK, I am taken to a Save As screen. I click Cancel here and am returned to Word. When I try to close Word again, I get a message that says "Changes have been made that affect the global template, Normal.dot. Do you want to save those changes?" Once I click No here, Word closes.

I have a feeling that this is a result of the way that my Excel macro is opening the Word template. I am very new to this, so I am not sure what to try next.

Thanks for any help!

Sub GenerateQuote()
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim r As Integer
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = True
    r = Selection.Row
    
    Set wdDoc = wdApp.Documents.Add("C:\Documents and Settings\...\template.DOC")
    
    wdDoc.FormFields("H_Contact_Name").Result = Sheets(1).Cells(r, 1).Value
    wdDoc.FormFields("H_Bill_To_Customer").Result = Sheets(1).Cells(r, 2).Value
    wdDoc.FormFields("H_Location").Result = Sheets(1).Cells(r, 3).Value
    wdDoc.FormFields("H_Quote_Date").Result = Sheets(1).Cells(r, 4).Value
    wdDoc.FormFields("H_Product_Interest").Result = Sheets(1).Cells(r, 5).Value
    wdDoc.FormFields("H_Bill_To_Customer1").Result = Sheets(1).Cells(r, 6).Value
    wdDoc.FormFields("H_Project_Name").Result = Sheets(1).Cells(r, 7).Value
    wdDoc.FormFields("H_Retail_Customer").Result = Sheets(1).Cells(r, 8).Value
    wdDoc.FormFields("H_Project_City").Result = Sheets(1).Cells(r, 9).Value

End Sub

Open in new window

Tommy Kinard

This may be the problem

Set wdDoc = wdApp.Documents.Open("C:\Documents and Settings\...\template.DOC")

Open in new window

kwarshaw

ASKER

I replaced the full path to the template file with the ellipsis to make the code a little more concise. Sorry for the confusion. The entire macro seems to work correctly (with the full file path included). The problem comes up when I try to close Word.

Thanks.

Tommy Kinard

I am suggesting that you open the document instead of adding another one to the collection of documents. This will cause more than 1 document to be open. So to test you would need to itterate thought the documents collection and close them all.

kwarshaw

ASKER

Sorry, I missed the change from 'Add' to 'Open' in your first comment.

I made the change to my code, and Word is now opening the template (rather than a copy of it as 'Document 1'). However, when I close Word, I still get the same messages about 'file in use'.

I also get these messages regardless of whether I save the filled-in template.

Tommy Kinard

Well I don't think you want to work on the templete so that isn't it.

Tha only other thing I can think of is the templete is making some kind of change to the normal.doc maybe firing a macro in word like on document open? Also a template is normally a .dot file. Rename the normal.doc file (if there is nothing in there you need for now) If I could see all of the code and the template I might be able to help more, please remove any confidental information if you can post it.

Otherwise I am starting to grab at straws here so maybe somebody else can step in and help. :(

RapidDelp

dragontooth,
I think you got it.
kwarshaw, you need to save your template as a .dot file. then use add like you were. Then the new document will be associated with the template, rather than with Normal.dot
WdApp.documents.Add(Template, NewTemplate, DocumentType, Visible)

Returns a Document object that represents a new, empty document added to the collection of open documents.
Template Optional Variant. The name of the template to be used for the new document. If this argument is omitted, the Normal template is used.NewTemplate Optional Variant. True to open the document as a template. The default value is False. You are OK with the default

DocumentType Optional Variant. The default constant is wdNewBlankDocument.
Visible Optional Variant. True to open the document in a visible window. If this value is False, Microsoft Word opens the document but sets the Visible property of the document window to False. The default value is True. you could set this false.
when you save the file, it will have the same formats as th template, so it will not need to update the template.
best of luck!

kwarshaw

ASKER

Thank you both for your help. I am still having this issue, though. I have renamed my template as a .dot file. I have also played with the parameters for WdApp.documents.Add that RapidDelp provided.

Dragontooth, my normal.dot file is already named with .dot and is stored in "\Documents and Settings\...\Application Data\Microsoft\Templates\Normal.dot". I'm not sure if I should make any changes to it.

If I simply open the Template.dot file by double-clicking it in Windows Explorer, the template opens fine (as it does with the Excel macro), but I am also able to close Word as usual by clicking the red X. In this case, I don't get any of the annoying messages about the normal.dot file.

I wonder if there is something else that I need to add to the Excel macro code to "release ownership" of the Word application. I have tried closing the Excel application before closing Word (thinking that Excel was the "other application" referenced in the message), but I still get the same messages when closing Word.

Thanks again for all the help.

GrahamSkan

There are some undocumented actions (bugs) that can make Word think that the template has to be saved. If it is the Normal template, you won't usually notice it because it is saved automatically. However, if you have another instance of Word running, you will get that message. Check for Winword.exe in the Procesess tab of Task Manager.

kwarshaw

ASKER

Yes, I have 2 instances of Winword.exe showing in Task Manager. Are there any patches/settings to fix this, or is there anything that I need to change in my macro code?

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kwarshaw

ASKER

GrahamSkan, that did it!!

I no longer get the messages about Normal.dot when closing Word.

Many thanks!!