VBA Help: 1) Check if workbook is already open. 2) If it IS, activate it. 3) If it is NOT, open it.

[bhurst]

I have a project where I download and format a data file, which includes as part of the process copying information from a master placement workbook and pasting it into the data file. Because the master placement workbook gets used throughout the day for a number of different uses, it may already be open, or it may be closed. I'm trying to create a VBA code that can:
- Check if the master placement workbook is already open
- If it is, activate the master placement workbook
- If it is not, open the master placement workbook

Dim temp As String

temp = "C:\File pathway\Master Placement Workbook.xlsx"

Dim Ret

Ret = IsWorkBookOpen(temp)

If Ret = True Then
    Workbooks("Z:\Coordinator of Institutional Assessment's Folder\SURVEY DATA DOWNLOADER.xlsx").Activate
Else
    Workbooks.Open ("Z:\Coordinator of Institutional Assessment's Folder\SURVEY DATA DOWNLOADER.xlsx")
End If
---------------------------------------------
' Function code obtained from http://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long


    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0


    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

When I run this code and the Master Placement Workbook is not already open, it works fine. If I run it while the Master Placement Workbook IS already open, though, it doesn't activate it, and it throws an error saying that the call to activate it needs debugging. I've been programming macros for about a month now, so I'm willing to admit I may be doing this the wrong way. Any ideas?

 

[Russell Hauf]

Here's how I might do it. I think you don't need a separate function. I find having Workbook objects to work with can make things quite flexible:

    Dim strPath As String
    Dim strTemp As String
    Dim wbk As Workbook
    Dim fOpen As Boolean
    
    strPath = "Z:\Coordinator of Institutional Assessment's Folder\"
    strTemp = "SURVEY DATA DOWNLOADER.xlsx"
    
    On Error Resume Next
    Set wbk = Workbooks(strTemp)
    fOpen = Not (wbk Is Nothing)
    On Error GoTo 0     ' or point to your error handler
    
    If Not fOpen Then
        Set wbk = Workbooks(strPath & strTemp).Open
    End If
    
    wbk.Activate
    
    ' other code...
    
    ' if the workbook wasn't open earlier, close it - otherwise do nothing (leave open)
    If Not fOpen Then
        wbk.Close False
    End If

 

[bhurst]

That worked like a charm--thanks very much!