CODEWITHSUNDEEP
vbaexcelms-word
ZHIJIE YING
ZHIJIE YING

Reputation: 55

How to make sure from Excel that a specific Word document is open or not?

I wanted my excel macro to create a report by inserting spreadsheet data after Bookmarks I placed in the template word documents.

But I found out that if the template word document is already open, the macro will crash, and consequently the template document will be locked as Read-only and no longer accessible by the macro.

Is there a way to prevent then macro from crashing even if the template word document is already open?

Below is my code

Set wdApp = CreateObject("Word.Application") 'Create an instance of word

Set wdDoc = wdApp.Documents.Open(ThisWorkbook.Path & "\Templates\Template_Confirmation.docx") 'Create a new confirmation note

Upvotes: 1

Views: 266

Answers (1)

R3uK
R3uK

Reputation: 14547

Here comes an evolution of what was suggested in comments :

A function that test if the file is open and offer you to set it directly while testing.

How to use it :

Sub test()
Dim WdDoc As Word.Document

Set WdDoc = Is_Doc_Open("test.docx", "D:\Test\")
MsgBox WdDoc.Content
WdDoc.Close
Set WdDoc = Nothing
End Sub

And the function :

Public Function Is_Doc_Open(FileToOpen As String, FolderPath As String) As Word.Document
'Will open the doc if it isn't already open and set an object to that doc
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document

On Error Resume Next
'Set wrdApp = GetObject(, "Word.Application")
If wrdApp Is Nothing Then
    Set wrdApp = CreateObject("Word.Application")
    Set wrdDoc = wrdApp.Documents.Open(FolderPath & FileToOpen)
Else
    On Error GoTo NotOpen
    Set wrdDoc = wrdApp.Documents(FileToOpen)
    GoTo OpenAlready
NotOpen:
    Set wrdDoc = wrdApp.Documents.Open(FolderPath & FileToOpen)
End If

OpenAlready:
On Error GoTo 0

Set Is_Doc_Open = wrdDoc

Set wrdApp = Nothing
Set wrdDoc = Nothing

End Function

Only downside of this, you don't have the reference of the Word application...

Any suggestion/evolution are welcome!

Upvotes: 1

Related Questions