VBA Question - Is the file open?

[tinderbox22]

Hello,

I'm running a macro that first prompts the user to open a file. I would like to know if there's a way to detect if a certain file is already open and skip that step. The below is partial code, and I'm hoping to add a piece that recognizes an open file called "October Summary.xlsm" and skips the entire Application.GetOpenFilename piece:

Sub Copy_To_Summary()

Dim my_FileName As Variant
Dim wb_Current As Workbook
Dim ws_Current As Worksheet
Dim wb_New As Workbook
Dim ws_New As Worksheet
Dim rg As Range
Dim Region As String

    Set wb_Current = ThisWorkbook
    Region = Sheets("MEDIA").Range("D1").Value
   
    my_FileName = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*") 'Open File dialog box
   
    If my_FileName <> False Then
        Workbooks.Open Filename:=my_FileName
    End If
       
    Set wb_New = ActiveWorkbook
    
    wb_Current.Activate
    
    With Sheets("MEDIA")
        Set rg = .Range("C2") 'first cell to copy
        Set rg = Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp)) 'All data in that column
        Set rg = rg.Resize(, 11) 'All data in columns C:M
    End With
    
    rg.Copy 'Copy selected table range

...

Any help would be appreciated. Thank you.

 

[JoeMo]

Here's a UDF you can call from your macro to see if a specific workbook is already open after you install the UDF in a standard module:

Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

As an example:
Dim Fname as string
Fname = InputBox("Enter file name") ' assume user responded with "October Summary.xlsm"
If Fname = "" then exit sub 'In case user clicked Cancel on the input box
If workbookopen( "October Summary.xlsm" ) then
'do something with the workbook
Else
'do something else
End If

 

[tinderbox22]

Thank you! I will try this out and let you know. I appreciate the quick help.

 

[JoeMo]

Thank you! I will try this out and let you know. I appreciate the quick help.

You are welcome - thanks for the reply.