PDA

View Full Version : Trying to access an open document...



Michael 514
03-05-2005, 10:42 AM
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

Howard Kaikow
03-06-2005, 06:31 AM
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

Michael 514
03-07-2005, 09:24 AM
This looks cool!
Thanks, I will try to integrate it into my Macro!

Thank you!

hairywhiterabbit
03-21-2005, 06:52 AM
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:


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



You can also supress any Word dialogs with: wdApp.DisplayAlerts = wdAlertsNone

This will just reopen the document I assume.

Hope this helps
Andrew

Michael 514
03-22-2005, 02:54 PM
Thanks Andrew!

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

So much to do, so little time!

Thanks again!
Mike

Dave
04-03-2005, 08:29 AM
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/viewtopic.php?t=136269&start=10

Howard Kaikow
04-17-2005, 02:28 PM
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:


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



You can also supress any Word dialogs with: wdApp.DisplayAlerts = wdAlertsNone

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,