Trying to access an open document...

[Michael 514]

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]

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]

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

Thank you!

[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

[Michael 514]

Thanks Andrew!

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

So much to do, so little time!

Thanks again!
Mike

[Dave]

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

[Howard Kaikow]

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,