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