[Use your browser's BACK button to return to the PRIME FAQ Page or click here if you came directly to this page.]

(Code) Determine If a Specific Office Document Is Open Using Excel/VBA and WordBasic

This was taken from the PRIME for Excel and PRIME for Word APIs. These APIs are available in our Word and Excel shareware packages which can be downloaded from our Products page.

When developing Office applications, you often find yourself inside an instance of Excel or Word needing to know if a specific document is open. So we wrote a general-purpose function to take one argument — a document’s fully qualified workbook filename (after all, the same root filename can exist in multiple folders) — and check to see if it’s open. Usually the sequence of events is to then open it if it isn’t already open.

Our function is called fblnIsWorkbookOpen in Excel and fIsDocumentOpen in Word.

Shown below is an Excel/VBA example that calls the function, followed by the Excel VBA source code. In these procedures there are, by design, some distinct differences in the code between VBA and WordBasic.

' Excel/VBA calling example
Sub Test()
    Dim strSourceFilename As String
    strSourceFilename = "D:\Data\Excel\Delete1.xls"
    If Not fblnIsWorkbookOpen(strSourceFilename) Then
        ' make sure target file really exists on disk
        If Dir(strSourceFilename) = "" Then
            MsgBox "Target file doesn't exist. We're outta here."
            Exit Sub
        End If
        Workbooks.Open Filename:=strSourceFilename
    End If
End Sub

' Excel/VBA...
' --------------------------------------------------------------------
' Purpose:  Indicates whether a specific fully-qualified workbook
'           is open right now. The caller is responsible for passing
'           a valid fully-qualified name.
'           NOTE: Providing only an 8.3 filename will return False as
'           this function is looking at the FullName property!!!
'
' Inputs:   strFullName - the fully-qualified name of the workbook
'
' Returns:  Boolean
'
' Updated:  2/21/95
' --------------------------------------------------------------------
' Copyright © 1994-96 PRIME Consulting Group, Inc.
' --------------------------------------------------------------------
Public Function fblnIsWorkbookOpen(strFullName As String) As Boolean
    Dim wrk As Workbook
    fblnIsWorkbookOpen = False
    For Each wrk In Workbooks
        If UCase(wrk.FullName) = UCase(strFullName) Then
            fblnIsWorkbookOpen = True
            Exit Function
        End If
    Next wrk
End Function

' WordBasic...
' --------------------------------------------------------------------
' Purpose:  Indicates whether a specific fully-qualified document
'           is open right now. The caller is responsible for passing
'           a valid fully-qualified name.
'           NOTE: Providing only an 8.3 filename will return False as
'           this function is looking at the FullName property!!!
'
' Inputs:   FullName$ - the fully-qualified name of the document
'
' Returns:  0 if FullName$ is not currently open, else return
'           matching window number.
'           NOTE: This is different from the Excel function which
'           is strictly boolean.
'
' Updated:  9/28/95 - ported in part from PRIME 95/Excel's API (see 
'                     above note re modifications)
' --------------------------------------------------------------------
' Copyright © 1994-96 PRIME Consulting Group, Inc.
' --------------------------------------------------------------------
Function fIsDocumentOpen(FullName$)
    ' ----- Declarations/initializations
    fIsDocumentOpen = 0
    i = 1
    Found = 0
    Search$ = UCase$(FullName$)
    ' ----- Main body
    n = CountWindows()
    While (Not Found) And (i <= n)
        lFileNameFromWindow$ = UCase$(FileNameFromWindow$(i))
        If Search$ = lFileNameFromWindow$ Then
            Found = - 1
            fIsDocumentOpen = i
        Else
            i = i + 1
        End If
    Wend
End Function

The Naked PC Subscribe to our free electronic newsletter. Get the latest on all things PC, updates to PRIME Freeware page, and more. Type your email name and click Subscribe.

Return to Top