VBA Question - Is the file open?

tinderbox22

New Member
Joined
Mar 9, 2010
Messages
43
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:

VBA Code:
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.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,231
Messages
5,600,427
Members
414,384
Latest member
joehalks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top