Not detecting Open Workbook - Why

[Roger]

Hello All,

I've written a application to write data from one workbook to another.
Before the workbook actually writes to the other workbook, I'm looking for
the workbook to alert me if the Second Workbook is already open. In the
event it is, I do not want it to run the rest of the application.

I thought I had set-up the macro to do just that, but it's not detecting the
other open workbook. It still acts like it's writing the data (meaning opens
and closes the other book - of course without saving the like it's requested
to), but I'm not sure why.

If you could look at the below code and give me your thoughts - I'd
appreciate it and else you could suggest to make it react to the request at
hand.

Thank you - Roger


Sub TrackingSALES()
Dim Wb1 As Workbook
Dim Secondwkbk As Workbook
Dim i As Integer
Dim irow As Long
Set Wb1 = ActiveWorkbook



Application.ScreenUpdating = False

On Error Resume Next
Set Secondwkbk = Workbooks("Second Book.xls")
If Not Secondwkbk Is Nothing Then
On Error GoTo 0
strMsg = "Docking Workbook is in use or open. Please Try Again Later"
Set Secondwkbk = Nothing

Exit Sub

Else
On Error GoTo 0
Set Secondwkbk = Workbooks.Open(Filename:= _
"C:\Documents and Settings\Dan03\Desktop\Second Book.xls")
End If

For i = 1 To 10
If Worksheets("T" & i).Range("A1") = "" Then
Worksheets("T" & i).Visible = True
Worksheets("T" & i).Select

If Wb1.Sheets("Checklist").Range("a4") Then Secondwkbk.ActiveSheet.Cells(4,
4) = "true"
If Wb1.Sheets("Checklist").Range("a3") Then Secondwkbk.ActiveSheet.Cells(4,
5) = "true"
If Wb1.Sheets("Checklist").Range("a10") Then Secondwkbk.ActiveSheet.Cells(8,
5) = "true"
If Wb1.Sheets("Checklist").Range("b10") Then Secondwkbk.ActiveSheet.Cells(8,
1) = "true"
If Wb1.Sheets("Checklist").Range("t9") Then Secondwkbk.ActiveSheet.Cells(8,
7) = "true"
If Wb1.Sheets("Checklist").Range("u9") Then Secondwkbk.ActiveSheet.Cells(8,
9) = "true"
If Wb1.Sheets("Checklist").Range("a8") Then Secondwkbk.ActiveSheet.Cells(7,
4) = "Yes"
If Wb1.Sheets("Checklist").Range("B8") Then Secondwkbk.ActiveSheet.Cells(7,
4) = "No"
If Wb1.Sheets("Checklist").Range("a7") Then Secondwkbk.ActiveSheet.Cells(7,
7) = "Yes"
If Wb1.Sheets("Checklist").Range("b7") Then Secondwkbk.ActiveSheet.Cells(7,
7) = "No"
Secondwkbk.ActiveSheet.Range("H7").Value = Wb1.Sheets("Checklist").Range("Q7")
If Wb1.Sheets("Checklist").Range("e6") = 1 Then
Secondwkbk.ActiveSheet.Cells(19, 6) = "DRS"
If Wb1.Sheets("Checklist").Range("e6") = 2 Then
Secondwkbk.ActiveSheet.Cells(19, 6) = "Book Entry"
If Wb1.Sheets("Checklist").Range("e6") = 3 Then
Secondwkbk.ActiveSheet.Cells(19, 6) = "Restricted"
If Wb1.Sheets("Checklist").Range("e6") = 4 Then
Secondwkbk.ActiveSheet.Cells(19, 6) = "ESPP"
If Wb1.Sheets("Checklist").Range("e6") = 5 Then
Secondwkbk.ActiveSheet.Cells(19, 6) = "See other Comments"
Secondwkbk.ActiveSheet.Range("f21").Value =
Wb1.Sheets("Checklist").Range("P5")
If Wb1.Sheets("Checklist").Range("t5") = 1 Then
Secondwkbk.ActiveSheet.Cells(21, 6) = "Common Stock"
If Wb1.Sheets("Checklist").Range("t5") = 2 Then
Secondwkbk.ActiveSheet.Cells(21, 6) = "Preferred Stock"
Secondwkbk.ActiveSheet.Range("d5").Value = Wb1.Sheets("Checklist").Range("l4")
Secondwkbk.ActiveSheet.Range("h5").Value = Wb1.Sheets("Checklist").Range("f5")
Secondwkbk.ActiveSheet.Range("P7").Value = Wb1.Sheets("Checklist").Range("H6")
Secondwkbk.ActiveSheet.Range("K7").Value = Wb1.Sheets("Checklist").Range("L9")
Secondwkbk.ActiveSheet.Range("P5").Value = Wb1.Sheets("Checklist").Range("L6")
Secondwkbk.ActiveSheet.Range("E6").Value = Wb1.Sheets("Checklist").Range("P6")
Secondwkbk.ActiveSheet.Range("F52").Value =
Wb1.Sheets("Checklist").Range("J8")
Secondwkbk.ActiveSheet.Range("F36").Value =
Wb1.Sheets("Checklist").Range("A24")
Secondwkbk.ActiveSheet.Range("F37").Value =
Wb1.Sheets("Checklist").Range("A25")
If Wb1.Sheets("Checklist").Range("B9") Then
Secondwkbk.ActiveSheet.Range("F52") = "No"
Secondwkbk.ActiveSheet.Range("f23").Value =
Wb1.Sheets("Checklist").Range("G16")
If Wb1.Sheets("Checklist").Range("T17") Then
Secondwkbk.ActiveSheet.Range("f23") = "800-468-9716"
Secondwkbk.ActiveSheet.Range("f24").Value =
Wb1.Sheets("Checklist").Range("L16")
If Wb1.Sheets("Checklist").Range("T18") Then
Secondwkbk.ActiveSheet.Range("F24") = "800-450-4064"
Secondwkbk.ActiveSheet.Range("f31").Value =
Wb1.Sheets("Checklist").Range("G22")
If Wb1.Sheets("Checklist").Range("s21") = 2 Then
Secondwkbk.ActiveSheet.Cells(31, 6) = "Yes - Dividend Reinvestment"
If Wb1.Sheets("Checklist").Range("s21") = 3 Then
Secondwkbk.ActiveSheet.Cells(31, 6) = "Yes - Direct Purchase"
If Wb1.Sheets("Checklist").Range("s21") = 4 Then
Secondwkbk.ActiveSheet.Cells(31, 6) = "Yes - ESPP"
If Wb1.Sheets("Checklist").Range("s21") = 5 Then
Secondwkbk.ActiveSheet.Cells(31, 6) = "Yes - Other see Comments"
If Wb1.Sheets("Checklist").Range("b21") Then
Secondwkbk.ActiveSheet.Cells(31, 6) = "No"
If Wb1.Sheets("Checklist").Range("d9") Then Secondwkbk.ActiveSheet.Cells(7,
4) = "Yes"
If Wb1.Sheets("Checklist").Range("e8") Then Secondwkbk.ActiveSheet.Cells(7,
4) = "No"
If Wb1.Sheets("Checklist").Range("a14") Then
Secondwkbk.ActiveSheet.Cells(30, 6) = "Yes"
If Wb1.Sheets("Checklist").Range("b14") Then
Secondwkbk.ActiveSheet.Cells(30, 6) = "No"
If Wb1.Sheets("Checklist").Range("s14") Then
Secondwkbk.ActiveSheet.Cells(29, 6) = "Yes"
If Wb1.Sheets("Checklist").Range("t14") Then
Secondwkbk.ActiveSheet.Cells(29, 6) = "No"
If Wb1.Sheets("Checklist").Range("a11") Then
Secondwkbk.ActiveSheet.Cells(28, 6) = "Yes - Color N/A"
If Wb1.Sheets("Checklist").Range("b11") Then
Secondwkbk.ActiveSheet.Cells(28, 6) = "No"
If Wb1.Sheets("Checklist").Range("t11") Then
Secondwkbk.ActiveSheet.Cells(28, 6) = "Yes - Color"
If Wb1.Sheets("Checklist").Range("u11") Then
Secondwkbk.ActiveSheet.Cells(28, 6) = "Yes - Black and White"
If Wb1.Sheets("Checklist").Range("a14") Then
Secondwkbk.ActiveSheet.Cells(30, 6) = "Yes"
If Wb1.Sheets("Checklist").Range("b14") Then
Secondwkbk.ActiveSheet.Cells(30, 6) = "No"
If Wb1.Sheets("Checklist").Range("u17") = 25 Then
Secondwkbk.ActiveSheet.Cells(23, 6) = "Other see Comments"
If Wb1.Sheets("Checklist").Range("a20") Then
Secondwkbk.ActiveSheet.Cells(22, 6) = "www.shareowneronline.com"
If Wb1.Sheets("Checklist").Range("b20") Then
Secondwkbk.ActiveSheet.Cells(22, 6) = "www.wellsfargo.com"
If Wb1.Sheets("Checklist").Range("s23") Then
Secondwkbk.ActiveSheet.Cells(35, 6) = "Yes"
If Wb1.Sheets("Checklist").Range("t23") Then
Secondwkbk.ActiveSheet.Cells(35, 6) = "No"
If Wb1.Sheets("Checklist").Range("t24") Then
Secondwkbk.ActiveSheet.Cells(38, 6) = "No"
If Wb1.Sheets("Checklist").Range("a28") Then
Secondwkbk.ActiveSheet.Cells(38, 6) = "$5.00"
If Wb1.Sheets("Checklist").Range("a23") Then
Secondwkbk.ActiveSheet.Cells(46, 6) = "Yes"
If Wb1.Sheets("Checklist").Range("t25") Then
Secondwkbk.ActiveSheet.Cells(46, 6) = "No"
If Wb1.Sheets("Checklist").Range("A32") Then
Secondwkbk.ActiveSheet.Cells(49, 6) = "Generic"
If Wb1.Sheets("Checklist").Range("B32") Then
Secondwkbk.ActiveSheet.Cells(49, 6) = "Custom"
If Wb1.Sheets("Checklist").Range("A32") Then
Secondwkbk.ActiveSheet.Cells(50, 6) = "Generic"
If Wb1.Sheets("Checklist").Range("B32") Then
Secondwkbk.ActiveSheet.Cells(50, 6) = "Custom"
If Wb1.Sheets("Checklist").Range("A32") Then
Secondwkbk.ActiveSheet.Cells(51, 6) = "Generic"
If Wb1.Sheets("Checklist").Range("B32") Then
Secondwkbk.ActiveSheet.Cells(51, 6) = "Custom"
Secondwkbk.ActiveSheet.Range("F52").Value =
Wb1.Sheets("Checklist").Range("J8")
If Wb1.Sheets("Checklist").Range("B9") Then Secondwkbk.ActiveSheet.Cells(52,
6) = "No"
Secondwkbk.ActiveSheet.Range("G19").Value =
Wb1.Sheets("Checklist").Range("B19")
Secondwkbk.ActiveSheet.Range("F33").Value =
Wb1.Sheets("Checklist").Range("G19")
If Wb1.Sheets("Checklist").Range("b19") Then
Secondwkbk.ActiveSheet.Cells(33, 6) = "No"
Secondwkbk.ActiveSheet.Range("F54").Value =
Wb1.Sheets("Checklist").Range("F34")
If Wb1.Sheets("Checklist").Range("B32") Then
Secondwkbk.ActiveSheet.Cells(54, 6) = "No"
Secondwkbk.ActiveSheet.Range("F56").Value =
Wb1.Sheets("Checklist").Range("F36")

Secondwkbk.ActiveSheet.Range("d2").Value = Now()


Call MainPage

Application.Goto Wb1.Sheets("Checklist").Range("d3")

Call Email

Call Clear

 

[Dave Peterson]

This kind of test:

set Secondwkbk = nothing
On Error Resume Next
Set Secondwkbk = Workbooks("Second Book.xls")
On error goto 0
if secondwkbk is nothing then ...

checks to see if that workbook is open in the same instance of excel.

If you have the workbook open in another instance of excel, this test won't do
what you want.

Microsoft shares a way to check to see if the file is open here:
http://support.microsoft.com?kbid=138621
Microsoft's IsFileOpen function.

 

[Roger]

Both methods failed. The macro detailed in the link kept getting hung up on
the first line "Is file open".

Can you think of any other way to write it so I can get it to work?

Thanks - R

 

[Dave Peterson]

It's time to describe what you want--do you want to test to see if the workbook
is already open in the same excel session or do you want to test to see if the
workbook is open in a different excel session?

And then it's time to share the code you tried. I've used both versions of that
code and they both have worked ok for me.

 

[Roger]

Hi Dave,

I guess I’m just a bit confused what you mean by “which†session of Excel.
As to which workbook I’m trying to apply the error message towards, it would
be the Second Book that I’m concerned about seeing the error for. The
Secondbook would be the warehouse/end place for all the data that users would
be reviewing. This data is transferred there via macro by WB1. My end goal is
to prevent Secondbook from opening and closing in the event other users are
currently in it. With all my previous code attempts, WB1 carries out the
application whether SecondBook is open or closed and ignores my requests to
alert me in the event the other is open. The code I used is exactly what was
written on the link you gave me. I set it up in the WB1 and asked it to
check the Second Book. It got hung up immediately and highlighted
ISFileOpen.

Though with my old code WB1 might not actually write data when the
Secondbook is already open, it still does not show an error message, so the
user would assume the application worked.

Below is what I came up with from the website you gave me the link for. It
hangs up on IsFileOpen and goes not further.

Thanks for our continued review and thoughts - Roger


<<<<< The below macro would be set in WB1 and looking in Second Book to see
if it’s already in use >>>>>

Sub TestFileOpened()

If IsFileOpen("C:\Documents and Settings\Dan03\Desktop\Second
Book.xls") Then
MsgBox "File already in use!"
End Sub
Else
MsgBox "File not in use!"
Workbooks.Open "C:\Documents and Settings\Dan03\Desktop\Second
Book.xls"
Call TrackingSALES

End If
End Sub

 

[Dave Peterson]

You can have multiple workbooks open in a single excel session (look under
Window in the menubar to see the list of workbooks open).

You can also have multiple instances of excel open at the same time.

Start excel any way you want.

Then start a second instance of excel by using:
Windows start button|run
type:
Excel
and hit enter
Then use file|open to open an existing workbook.

If you look under Window in each of these instances, you won't see the name of
the workbook open in the other instance.

It's just like opening MSWord and NotePad at the same time--two applications
that don't know that the other is running.

By you opening the file in a second instance of excel, you can simulate the test
where some other user has the workbook open.

So...

Did you add the IsFileOpen function to your code?

This skinnied down code worked ok for me:

Option Explicit
Sub TestFileOpened()

Dim TestStr As String
Dim myFileName As String

myFileName = "C:\my documents\excel\book2.xls"
TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName)
On Error GoTo 0

If TestStr = "" Then
MsgBox "File: " & myFileName & " doesn't exist!"
Exit Sub
End If

If IsFileOpen(myFileName) Then
MsgBox "File already in use!"
Exit Sub '???
Else
MsgBox "File not in use!"
Workbooks.Open myFileName
End If
End Sub
Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer

On Error Resume Next
filenum = FreeFile()

Open filename For Input Lock Read As #filenum
Close filenum
errnum = Err
On Error GoTo 0

Select Case errnum
Case 0
IsFileOpen = False
Case 70
IsFileOpen = True
Case Else
Error errnum
End Select
End Function

Hi Dave,

I guess I’m just a bit confused what you mean by “which†session of Excel.
As to which workbook I’m trying to apply the error message towards, it would
be the Second Book that I’m concerned about seeing the error for. The
Secondbook would be the warehouse/end place for all the data that users would
be reviewing. This data is transferred there via macro by WB1. My end goal is
to prevent Secondbook from opening and closing in the event other users are
currently in it. With all my previous code attempts, WB1 carries out the
application whether SecondBook is open or closed and ignores my requests to
alert me in the event the other is open. The code I used is exactly what was
written on the link you gave me. I set it up in the WB1 and asked it to
check the Second Book. It got hung up immediately and highlighted
ISFileOpen.

Though with my old code WB1 might not actually write data when the
Secondbook is already open, it still does not show an error message, so the
user would assume the application worked.

Below is what I came up with from the website you gave me the link for. It
hangs up on IsFileOpen and goes not further.

Thanks for our continued review and thoughts - Roger

<<<<< The below macro would be set in WB1 and looking in Second Book to see
if it’s already in use >>>>>

Sub TestFileOpened()

If IsFileOpen("C:\Documents and Settings\Dan03\Desktop\Second
Book.xls") Then
MsgBox "File already in use!"
End Sub
Else
MsgBox "File not in use!"
Workbooks.Open "C:\Documents and Settings\Dan03\Desktop\Second
Book.xls"
Call TrackingSALES

End If
End Sub

 

[Roger]

Thanks Dave that worked just great.

I know if must be a pain to explain and re-explain things to some of us that
know less, but I appreciate you taking the time to review this and also the
helpful information that will make this task easier to break down the next
time it comes up .

Thanks again and have a great weekend - Roger

 

[Dave Peterson]

Glad you got it working!

Thanks Dave that worked just great.

I know if must be a pain to explain and re-explain things to some of us that
know less, but I appreciate you taking the time to review this and also the
helpful information that will make this task easier to break down the next
time it comes up .

Thanks again and have a great weekend - Roger