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

kwarshawAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tommy KinardCommented:
This may be the problem
Set wdDoc = wdApp.Documents.Open("C:\Documents and Settings\...\template.DOC")

Open in new window

0
kwarshawAuthor Commented:
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.
0
Tommy KinardCommented:
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.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

kwarshawAuthor Commented:
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.
0
Tommy KinardCommented:
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. :(
0
RapidDelpCommented:
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!

0
kwarshawAuthor Commented:
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.
0
GrahamSkanRetiredCommented:
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.
0
kwarshawAuthor Commented:
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?
0
GrahamSkanRetiredCommented:
Macros that don't quit  the application (wdApp.Quit) can do that, though I see that you make the application visible, so the user would know about it and be able to close it manually.

You could try to use an existing instance and only open a new one if necessary.






    On Error Resume Next 'supress error checking
    Set wdApp = GetObject(, "Word.Application")
    On Error GoTo 0 'restart error checking
    
    If wdApp Is Nothing Then
        Set wdApp = CreateObject("Word.Application")
    End If
    wdApp.Visible = True
    r = Selection.Row

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kwarshawAuthor Commented:
GrahamSkan, that did it!!

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

Many thanks!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.