none
In excel use VBA to check if a word document is open RRS feed

  • Question

  • I wish to open a MS Word document by clicking on a button in an excel worksheet, but I need to check first to see if the document is already open.  The following code works correctly if the document is NOT open, but I need a way to deal with the "already open" situation. Currently the "Already Open" dialogue box appears, and when I click Cancel, the Word background is displayed with no document Showing.  Ideally the code would simply bring the requested already open document to the front, but an alternative would be to generate a message box telling me it was already open.

    Sub HelpAndInfo()

    'Open Help and Information Document
    Dim Wd As Object
    Dim HelpDoc As Object

        On Error GoTo Unset
        
        Set Wd = CreateObject("Word.Application")

        'If the doc is already open the next line pulls up the "Already Open" dialogue box .

        Set HelpDoc = Wd.Documents.Open("C:\Users\NAME\Documents\Computer\VBA\SpdSheetNotes.doc")
        Wd.Visible = True
        
        On Error GoTo 0

    Unset:
        Set Wd = Nothing
        Set HelpDoc = Nothing

    End Sub

    Any help gratefully received.

    Friday, December 28, 2012 4:50 PM

Answers

  • Try this version:

    Sub HelpAndInfo()
        'Open Help and Information Document
        Const strpath = "C:\Users\NAME\Documents\Computer\VBA\SpdSheetNotes.doc"
        Dim Wd As Object
        Dim HelpDoc As Object
        Dim f As Boolean
        On Error Resume Next
        Set HelpDoc = GetObject(strpath)
        If HelpDoc Is Nothing Then
            Set Wd = GetObject(, "Word.Application")
            If Wd Is Nothing Then
                Set Wd = CreateObject("Word.Application")
                If Wd Is Nothing Then
                    MsgBox "Failed to start Word!", vbCritical
                    Exit Sub
                End If
                f = True
            End If
            Set HelpDoc = Wd.Documents.Open(strpath)
            If HelpDoc Is Nothing Then
                MsgBox "Failed to open help document!", vbCritical
                If f Then
                    Wd.Quit
                End If
                Exit Sub
            End If
            Wd.Visible = True
        Else
            With HelpDoc.Parent
                .Visible = True
                .Activate
            End With
        End If
    End Sub
    


    Regards, Hans Vogelaar

    • Marked as answer by AndyColRomsey Friday, December 28, 2012 6:30 PM
    Friday, December 28, 2012 5:05 PM

All replies

  • Try this version:

    Sub HelpAndInfo()
        'Open Help and Information Document
        Const strpath = "C:\Users\NAME\Documents\Computer\VBA\SpdSheetNotes.doc"
        Dim Wd As Object
        Dim HelpDoc As Object
        Dim f As Boolean
        On Error Resume Next
        Set HelpDoc = GetObject(strpath)
        If HelpDoc Is Nothing Then
            Set Wd = GetObject(, "Word.Application")
            If Wd Is Nothing Then
                Set Wd = CreateObject("Word.Application")
                If Wd Is Nothing Then
                    MsgBox "Failed to start Word!", vbCritical
                    Exit Sub
                End If
                f = True
            End If
            Set HelpDoc = Wd.Documents.Open(strpath)
            If HelpDoc Is Nothing Then
                MsgBox "Failed to open help document!", vbCritical
                If f Then
                    Wd.Quit
                End If
                Exit Sub
            End If
            Wd.Visible = True
        Else
            With HelpDoc.Parent
                .Visible = True
                .Activate
            End With
        End If
    End Sub
    


    Regards, Hans Vogelaar

    • Marked as answer by AndyColRomsey Friday, December 28, 2012 6:30 PM
    Friday, December 28, 2012 5:05 PM
  • Hans

    Many thanks;  that works exactly as I want it.

    However, there is one interesting point.  (I am using MS Office 2007.)  When I open the document using the macro (F5 or stepping through with F8 or from the macro selector on the ribbon) it opens correctly except that the zoom slider bar at the bottom right has no pointer on it and the + and - controls don't work.  When I go to View on the ribbon, the zoom dialogue box operates correctly and allows me to change the soom setting.  Opening the document normally by double clicking on the file name in its folder opens the document with the zoom slider working normally.  If I open it with the macro and then double click on the file name in its folder, the zoom control still does not work.  I have tried closing the VBA window to see if that had any effect; it didn't.  Everything else seems normal - I can change the view mode, edit, spell check etc.

    Now that's a weird one!

    With Thanks

    Andy C

    Friday, December 28, 2012 6:46 PM
  • Weird indeed! It looks like this has been corrected in Office 2010, which I'm using. The zoom slider works correctly when I open a document using the macro.

    Regards, Hans Vogelaar

    Friday, December 28, 2012 6:55 PM
  • Dear gent,

    Thanks for the code.

    It works perfectly as expected.

    However, the path and file name in this code is constant

    What should I do to make the file and path to be variable.

    Best regards,

    Gholam Soori

    gholam.soori@gmail.com

    Const strpath = "C:\Users\NAME\Documents\Computer\VBA\SpdSheetNotes.doc"

    Friday, October 6, 2017 9:55 AM
  • You can replace

    Const strpath = "C:\Users\NAME\Documents\Computer\VBA\SpdSheetNotes.doc"

    with

    Dim strPath As String
    strPath = ... ' your variable expression


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, October 6, 2017 10:56 PM