CODEWITHSUNDEEP
excelvba
Subhaac
Subhaac

Reputation: 437

Check whether a specific workbook is open

I have this code so far to transfer between two files. How do I check whether the destination workbook is open? I've tried IsWorkbookOpen(), but it says "function not defined".

Sub Test2()

Dim rowCount As Long
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim a As Integer
a = 1
z = 5
x = 2
y = 16

ActiveWorkbook.Sheets("Results").Activate
rowCount = Cells(Rows.Count, "B").End(xlUp).Row

MsgBox "There are " & Workbooks.Count & " open workbooks!"
For Counter1 = 1 To 8
a = 1
z = 5
MsgBox "From :(" & a & "," & x & ") To:(" & z & "," & y & ")"


    For Counter = 1 To rowCount
       If IsWorkbookOpen("CMK & CPK Sheet (Rev2)") = True Then
            MsgBox "Workbook is Open!"
        Else
            MsgBox "Workboook is Not Open!"

        Workbooks("CMK & CPK Sheet (Rev2)").Sheets(3).Cells(z, y).Value = ActiveWorkbook.ActiveSheet.Cells(a, x).Value
        z = z + 1
        a = a + 1
        Next Counter
        y = y + 1
        x = x + 1

Next Counter1

End Sub

Upvotes: 0

Views: 193

Answers (2)

L42
L42

Reputation: 19727

Try this:

Dim targetWB as Workbook

On Error Resume Next
Set targetWB = Workbooks("CMK & CPK Sheet (Rev2)")
On Error Goto 0

If targetWb Is Nothing Then
     MsgBox "Workbook not yet open"
     Exit Sub 'you can terminate procedure or you can use Workbooks.Open
Else
     MsgBox "Workbook open"
     '~~> rest of your code here
End If

Hope this helps.

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Here is one way to see if a specific workbook is open:

Sub hfskadjrufc()
    Dim bk As Workbook
    Dim s As String, ItIsOpen As Boolean
    s = "todo.xls"
    ItIsOpen = False
    For Each bk In Workbooks
        If bk.Name = s Then
            ItIsOpen = True
        End If
    Next bk
    MsgBox ItIsOpen
End Sub

Upvotes: 1

Related Questions