Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Close/Release Word object in VBA?

I have the following code to open the manual for an Excel Workbook application I have developed:

Sub OpenManual()

'Word.Application.Documents.Open "\\filePath\FormFlow To MSExcel\FeedSampleReport-Manual.docx"

Dim objWord As Object
Set objWord = CreateObject("Word.Application")
objWord.Visible = True

objWord.Documents.Open "\\filePath\FormFlow To MSExcel\FeedSampleReport-Manual.docx"

End Sub

This gives me 2 issues though:

  1. The document opens, but in the background. The user doesn't know the document has opened unless they know to check Microsoft Word in the Taskbar.
  2. When I try to close the word document I receive: This file is in use by another application or user. (C:\Users\Me\AppData...\Normal.dotm)

When I click ok on that dialogue, I receive a "Save As" screen.

If I cancel out of that and try to close the blank Microsoft Word instance I then get:

Changes have been made that affect the global template, Normal. Do you want to save those changes?

Then if I click No, everything finally closes.

Can anyone help me out with these 2 issues? Do I need to release the object somehow? Have never seen this before.

EDIT:

After trying @Layman-Coders method:

Sub OpenManual()
'Word.Application.Documents.Open "\\filePath\FormFlow To MSExcel\FeedSampleReport-Manual.docx"

'Open an existing Word Document from Excel
Dim objWord As Object
Set objWord = CreateObject("Word.Application")
objWord.Visible = True

' Should open as the forefront
objWord.Activate

'Change the directory path and file name to the location
'of the document you want to open from Excel
objWord.Documents.Open "\\filePath\FormFlow To MSExcel\FeedSampleReport-Manual.docx"

objWord.Quit
Set objWord = Nothing

End Sub

When I have one other word document open and click the button, the following occurs:

  1. Manual opens in the forefront, but I immediately receive This file is in use by another application or user. (C:\Users\Me\AppData\...\Normal.dotm)
  2. I press OK and receive the Save As dialogue.
  3. Cancel out of the Save As dialogue and am presented my Manual document.
  4. When I click the Red X to close the document, I receive Changes have been made that affect the global template, Normal. Do you want to save those change? I click No and the document closes.

If this document is the first instance of word I have opening:

  1. The document opens.
  2. As soon as code hits the objWord.Quit line the document immediately closes.

I am just wanting the document to open to the forefront allowing users to view the Manual for assistance when they need it, and let them close the document at their discretion.

like image 496
Analytic Lunatic Avatar asked Jun 18 '13 19:06

Analytic Lunatic


1 Answers

So the problem you are having with Word asking you to save the global template is because there is already a copy Word open which has rights to the Normal template. When you use CreateObject to set your Word object you are loading up Word a second time which opens Normal template as read only.

What you need to do is check if Word is open or not and if it is grab that copy of Word. If it's not then you can open up Word.

Sub OpenManual()
    Dim objWord As Object

    'We need to continue through errors since if Word isn't
    'open the GetObject line will give an error
    On Error Resume Next
    Set objWord = GetObject(, "Word.Application")

    'We've tried to get Word but if it's nothing then it isn't open
    If objWord Is Nothing Then
        Set objWord = CreateObject("Word.Application")
    End If

    'It's good practice to reset error warnings
    On Error GoTo 0

    'Open your document and ensure its visible and activate after openning
    objWord.Documents.Open "\\filePath\FormFlow To MSExcel\FeedSampleReport-Manual.docx"
    objWord.Visible = True
    objWord.Activate

    Set objWord = Nothing
End Sub

One other nice line of code is to surpress display alerts. This will stop the 'do you want to save' type dialog boxes from appearing.

objWord.DisplayAlerts = 0

like image 76
CuberChase Avatar answered Sep 21 '22 22:09

CuberChase