Opening a Word doc in Excel VBA - how do I allow for it to be already open?

[Bill Hamilton]

Hi,

I have a macro that opens a Word doc to paste some data into it from the current Excel sheet. I have the following pretty standard code to set up the Word environment and open the document.

Sub UpdateWordDoc()
Dim wdDoc As Object, wdApp As Object

Set wdApp = CreateObject("word.application")
Set wdDoc = wdApp.Documents.Open(ThisWorkbook.Path & Application.PathSeparator & "info12.docx")
With wdDoc

'  processing

End With
wdDoc.Close savechanges:=true
wdApp.Quit
Set wdDoc = Nothing
Set wdApp = Nothing
End Sub

This works fine if the doc file exists and is not currently open, but just seems to hang if it is currently open. How can I allow for that, and use that instance of it anyway (and also not close it at the end)?

Also, what's the handling process for when/if the file doesn't exits (i.e. not found)? Is there a specific Err.Number value I should check for?

Any help would be appreciated.

Bill

 

[shg]

Maybe something like this (untested):

Sub UpdateWordDoc()
    Const sDoc      As String = "info12.docx"
    Dim wdDoc       As Object
    Dim wdApp       As Object
    Dim bOpenedApp  As Boolean
    Dim bOpenedDoc  As Boolean

    On Error GoTo GetApp
    Set wdApp = GetObject("Word.Application")

    On Error GoTo GetDoc
    Set wdDoc = wdApp.Documents(sDoc)

    On Error GoTo 0

    With wdDoc
        '  processing
    End With

    wdDoc.Save
    If bOpenedDoc Then wdDoc.Close
    If bOpenedApp Then wdApp.Quit
    Exit Sub

GetApp:
    bOpenedApp = True
    Set wdApp = CreateObject("Word.Application")
    Resume Next

GetDoc:
    bOpenedDoc = True
    Set wdDoc = wdApp.Documents.Open(ThisWorkbook.Path & "\" & sDoc)
    Resume Next
End Sub

 

[BengalMagic]

Also, what's the handling process for when/if the file doesn't exits (i.e. not found)? Is there a specific Err.Number value I should check for?

Any help would be appreciated.

Bill

I use this function:

Option Explicit
 
Function FileOrDirExists(PathName As String) As Boolean
      
Dim itemp As Integer
    
On Error Resume Next
itemp = GetAttr(PathName)
Select Case Err.Number
    Case 0
        FileOrDirExists = True
    Case Else
        FileOrDirExists = False
End Select
On Error GoTo 0
End Function

and then a simple

If FileOrDirExists(filehere) Then
GoTo continue
Else
MsgBox "The source spreadsheet " & filehere & " is not available"
Exit Sub
End If

to call it (where "filehere" is a string relating to the full path of the file in question).

 

[patel45]

Sub UpdateWordDoc1() 
Dim wdDoc As Object, wdApp As Object
On Error Resume Next
Set wdDoc = CreateObject("C:\test.doc") 
Set wdApp = wdDoc.Application
wdApp.Visible = True
With wdDoc

'  processing

End With
wdDoc.Close savechanges:=True
wdApp.Quit
Set wdDoc = Nothing
Set wdApp = Nothing
End Sub

 

[Bill Hamilton]

Thank you shg and patel45. Both these work (although shg gave me a bit of a wobbly by omitting the comma as the first parameter in GetObject!). I might go for that one as it allows for Word to be active but not with the particular document I'm concerned with here - it doesn't shut down Word regardless of what's active. I'm sure patel45's solution could be tweaked to allow for that, and I had a go, but it got too messy (for me - I'm sure there's an elegant resolution for that).

BengalMagic: That's perfect. Now I just have to integrate these solutions into one piece of code. I can at least manage that much.

Thank you all for your help.

Bill