Trying to access an open document...

Hello all,

As many of you have seen from the my other threads here, I am dealing with some VBA code in Excel that opens up a bunch of Word documents and populates various cells with the bookmarks from the Word document.

Things are going nicely, but there is one small issue I would love to avoid:

If the user selects a Word Document that is open (either on her PC or on another PC), the Excel VBA macro appears to hang.

Believe it or not, a trip to the Task Manager is required!

Upon closer examination, I noticed that the macro isn't handing. Rather, the user is simply presented with a dialog box telling the User the document is open, and to "open a read only copy, etc. etc."

The problem is that this dialog box is hidden under the Excel window! It doesn't come into focus.

I am not 100% sure if this is due to behavior on all machines, so just the fact that the user may have used Microsoft's Power Tools' TweakUI to "Prevent Applications from Stealing Focus."

In any case, I was wondering if it's possible to FORCE a dialog box to come into control, or alternatively, for an Excel VBA Macro that is accessing a Word document to 'know' if the Doc is open or not, and if so, to either prompt the user to close it, or, alternatively, to automatically open a "read-only" copy.

This isn't a tremendously mission-critical request, but it would just be a nice feature to add to this cool macro we all created before we stick it in the KB.

Ideas are welcome!

Thanks!

Mike

Code:

---

Option Explicit

Private Sub TestIsFileOpen()
        Dim strFile As String
       
        strFile = "This file does not exist"
        Debug.Print Chr$(34) & strFile & Chr$(34); IIf(IsFileOpen(strFile), " is open", " is not open")
        strFile = "C:\boot.ini"
        Debug.Print Chr$(34) & strFile & Chr$(34); IIf(IsFileOpen(strFile), " is open", " is not open")
        strFile = "J:\Documents and Settings\Howard Kaikow\ntuser.dat.LOG"
        Debug.Print Chr$(34) & strFile & Chr$(34); IIf(IsFileOpen(strFile), " is open", " is not open")
End Sub

Private Function IsFileOpen(strFullyQualifiedFileName As String) As Boolean
        Dim intFreeFile As Integer
       
        intFreeFile = FreeFile
       
        On Error Resume Next
        Open strFullyQualifiedFileName For Binary Access Read Lock Read As #intFreeFile
        Close #intFreeFile
       
        If Err.Number = 0 Then
                IsFileOpen = False
        Else
                IsFileOpen = True
                Err.Clear
        End If
End Function


---

This looks cool!
Thanks, I will try to integrate it into my Macro!

Thank you!

Hi Michael,

I would just do a check to see if the document is open or not. Here is a little function (written for Excel) that should do the trick:

[VBA]
Function CheckDocOpen(wdApp As Word.Application, sFileName As String) As Boolean
Dim wdDoc As Word.Document

CheckDocOpen = False
For Each wdDoc In wdApp.Documents
If wdDoc.Name = sFileName Then
CheckDocOpen = True
Exit For
End If
Next wdDoc

End Function

[/VBA]

You can also supress any Word dialogs with: [VBA] wdApp.DisplayAlerts = wdAlertsNone [/VBA]

This will just reopen the document I assume.

Hope this helps
Andrew

Thanks Andrew!

I haven't even added either one of these goodies, yet!

So much to do, so little time!

Thanks again!
Mike

Mike...you asked for ideas. To avoid open Word document errors it is possible to copy the file in question (even if it is open) to another location. Do your stuff on the copied file, then quit the Word application and copy it back to the original location. I'm not sure if this helps your application but it's not that hard to do with a couple of UDF's. Dave
edit: Here's a link to an example
http://www.mrexcel.com/board2/viewto...36269&start=10

Quote:

---

Originally Posted by hairywhiterabbit

Hi Michael,

I would just do a check to see if the document is open or not. Here is a little function (written for Excel) that should do the trick:

[VBA]
Function CheckDocOpen(wdApp As Word.Application, sFileName As String) As Boolean
Dim wdDoc As Word.Document

CheckDocOpen = False
For Each wdDoc In wdApp.Documents
If wdDoc.Name = sFileName Then
CheckDocOpen = True
Exit For
End If
Next wdDoc

End Function

[/VBA]

You can also supress any Word dialogs with: [VBA] wdApp.DisplayAlerts = wdAlertsNone [/VBA]

This will just reopen the document I assume.

Hope this helps
Andrew

---

that code will check only whether a particular instance of word has a doc open.

one needs code like i posted to check whether a file is open anywhere,