Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Not detecting Open Workbook - Why
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Not detecting Open Workbook - Why
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Not detecting Open Workbook - Why
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Not detecting Open Workbook - Why
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Not detecting Open Workbook - Why
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Not detecting Open Workbook - Why
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 Roger wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Not detecting Open Workbook - Why
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" wrote: 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 Roger wrote: 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" 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Not detecting Open Workbook - Why
Glad you got it working!
Roger wrote: 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" wrote: 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 Roger wrote: 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" 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Not Detecting Open Workbook Status - Odd?? | Excel Discussion (Misc queries) | |||
Detecting if a Window/Workbook is Open | Excel Discussion (Misc queries) | |||
excel 2003 saved file will not open without a blank workbook open | Excel Discussion (Misc queries) | |||
Search open sheets in workbook and insert into open sheet | Excel Discussion (Misc queries) | |||
Excel workbook does not open in open window on desktop | Excel Discussion (Misc queries) |