Hi Dave,
I guess Im just a bit confused what you mean by €śwhich€ť session of Excel.
As to which workbook Im trying to apply the error message towards, it would
be the Second Book that Im 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 its 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" wrote:
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 wrote:
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" wrote:
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 he
http://support.microsoft.com?kbid=138621
Microsoft's IsFileOpen function.
Roger wrote:
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
--
Dave Peterson