Prevent "XYZ.doc is locked for editing" message
 

The following code is contained in a UserForm code module and is used to open
a file that stores test results for tests listed in a lab management program
I'm working on. "LinkCell" is a public range variable that contains the path
and name of the file containing the results for a particular test. In other
words, the process is similar to opening a file using a Hyperlink. Two file
types are supported: .xls and .doc.

Opening .xls files works fine. The problem is that if the test results are
stored to a Word document (.doc) instead, the below code doesn't run on first
call. On second call I get the "XYZ.doc is locked for editing. Click 'Notify'
to open a read-only copy ...." message. I want the code to work on first run
and I don't want this message to appear.

There is obviously something happening that I don't understand. It's as if
an instance of the .doc file is opened on the first run but is invisible.
There is no indication that a file is open in the Task bar and if I shrink
the lab management program (.xls) there is no open .doc file hiding beneath.
(I know I'm just being stupid but I'm under the gun to get this finished).

Dim FileExt As String

Private Sub CommandButton1_Click()
OpenFileType LinkCell.Value
End Sub

Private Sub OpenFileType(FileName As String)
Dim i As Long
Dim WordApp As Object, myDoc As Object

i = InStrRev(FileName, ".")
FileExt = Right(FileName, Len(FileName) - i)
Select Case FileExt
Case "xls"
Me.Controls(5).Enabled = True
For i = 1 To 4
Me.Controls(i).Enabled = False
Next
Me.Controls(5).SetFocus
DoEvents
Workbooks.Open FileName
Case "doc"
'This part causes the problem
On Error Resume Next
Set WordApp = CreateObject("Word.Application")
Set myDoc = WordApp.Documents.Open(FileName)
If myDoc Is Nothing Then WordApp.Quit
Set WordApp = Nothing
Set myDoc = Nothing
Unload Me
On Error GoTo 0
End Select
End Sub

Much appreciative of some assistance.

Greg Wilson

Prevent "XYZ.doc is locked for editing" message
 

Greg,

Try adding...
WordApp.Visible = True
after...
Set WordApp = CreateObject("Word.Application")

Also, you can check in "Task Manager" to see how instances
of Word are in use...right-click the Taskbar and select Task Manager,
then look at the Processes tab.

Regards,
Jim Cone
San Francisco, USA


"Greg Wilson"
wrote in message

The following code is contained in a UserForm code module and is used to open
a file that stores test results for tests listed in a lab management program
I'm working on. "LinkCell" is a public range variable that contains the path
and name of the file containing the results for a particular test. In other
words, the process is similar to opening a file using a Hyperlink. Two file
types are supported: .xls and .doc.

Opening .xls files works fine. The problem is that if the test results are
stored to a Word document (.doc) instead, the below code doesn't run on first
call. On second call I get the "XYZ.doc is locked for editing. Click 'Notify'
to open a read-only copy ...." message. I want the code to work on first run
and I don't want this message to appear.

There is obviously something happening that I don't understand. It's as if
an instance of the .doc file is opened on the first run but is invisible.
There is no indication that a file is open in the Task bar and if I shrink
the lab management program (.xls) there is no open .doc file hiding beneath.
(I know I'm just being stupid but I'm under the gun to get this finished).

Dim FileExt As String

Private Sub CommandButton1_Click()
OpenFileType LinkCell.Value
End Sub

Private Sub OpenFileType(FileName As String)
Dim i As Long
Dim WordApp As Object, myDoc As Object

i = InStrRev(FileName, ".")
FileExt = Right(FileName, Len(FileName) - i)
Select Case FileExt
Case "xls"
Me.Controls(5).Enabled = True
For i = 1 To 4
Me.Controls(i).Enabled = False
Next
Me.Controls(5).SetFocus
DoEvents
Workbooks.Open FileName
Case "doc"
'This part causes the problem
On Error Resume Next
Set WordApp = CreateObject("Word.Application")
Set myDoc = WordApp.Documents.Open(FileName)
If myDoc Is Nothing Then WordApp.Quit
Set WordApp = Nothing
Set myDoc = Nothing
Unload Me
On Error GoTo 0
End Select
End Sub

Much appreciative of some assistance.

Greg Wilson

Prevent "XYZ.doc is locked for editing" message
 

Jim, you're awesome - that nailed it !!!

I added WordApp.Activate and I'm in business. I'm suffering from burn out at
the moment and never thought of it. That saved me hours.

Best regards,
Greg Wilson

"Jim Cone" wrote:

Greg,

Try adding...
WordApp.Visible = True
after...
Set WordApp = CreateObject("Word.Application")

Also, you can check in "Task Manager" to see how instances
of Word are in use...right-click the Taskbar and select Task Manager,
then look at the Processes tab.

Regards,
Jim Cone
San Francisco, USA


"Greg Wilson"
wrote in message

The following code is contained in a UserForm code module and is used to open
a file that stores test results for tests listed in a lab management program
I'm working on. "LinkCell" is a public range variable that contains the path
and name of the file containing the results for a particular test. In other
words, the process is similar to opening a file using a Hyperlink. Two file
types are supported: .xls and .doc.

Opening .xls files works fine. The problem is that if the test results are
stored to a Word document (.doc) instead, the below code doesn't run on first
call. On second call I get the "XYZ.doc is locked for editing. Click 'Notify'
to open a read-only copy ...." message. I want the code to work on first run
and I don't want this message to appear.

There is obviously something happening that I don't understand. It's as if
an instance of the .doc file is opened on the first run but is invisible.
There is no indication that a file is open in the Task bar and if I shrink
the lab management program (.xls) there is no open .doc file hiding beneath.
(I know I'm just being stupid but I'm under the gun to get this finished).

Dim FileExt As String

Private Sub CommandButton1_Click()
OpenFileType LinkCell.Value
End Sub

Private Sub OpenFileType(FileName As String)
Dim i As Long
Dim WordApp As Object, myDoc As Object

i = InStrRev(FileName, ".")
FileExt = Right(FileName, Len(FileName) - i)
Select Case FileExt
Case "xls"
Me.Controls(5).Enabled = True
For i = 1 To 4
Me.Controls(i).Enabled = False
Next
Me.Controls(5).SetFocus
DoEvents
Workbooks.Open FileName
Case "doc"
'This part causes the problem
On Error Resume Next
Set WordApp = CreateObject("Word.Application")
Set myDoc = WordApp.Documents.Open(FileName)
If myDoc Is Nothing Then WordApp.Quit
Set WordApp = Nothing
Set myDoc = Nothing
Unload Me
On Error GoTo 0
End Select
End Sub

Much appreciative of some assistance.

Greg Wilson