If workbook is open then close

[ummjay]

Hi! I have the below, which allows the user to open the designated file and then run my macro accordingly. I noticed that if that file is already open it doesnt run the rest of the macro properly, so it there a way to say if that file is already open, close the already open file, and then open the file in question to perform the macro?

Set macroWB = ActiveWorkbook

    NewFFN = Application.GetOpenFilename(Title:="Please Select File")
    If NewFFN = False Then
        MsgBox "Macro Terminated Due to No File Selected"
        Exit Sub
    Else
        Workbooks.Open fileName:=NewFFN
        Set fileWB = ActiveWorkbook
    End If
    Application.Calculation = xlCalculationAutomatic   '<- may not be necessary

 

[BigBeachBananas]

Try this.

Dim macroWB As Workbook
Dim fileWB As Workbook
Dim NewFFN As Variant
Dim isOpen As Boolean

Set macroWB = ThisWorkbook 

' Check if the file is already open
For Each fileWB In Workbooks
    If fileWB.FullName = NewFFN Then
        isOpen = True
        Exit For
    End If
Next fileWB

' If the file is open, close it
If isOpen Then
    Workbooks(NewFFN).Close SaveChanges:=False
End If

NewFFN = Application.GetOpenFilename(Title:="Please Select File")
If NewFFN = "False" Then
    MsgBox "Macro Terminated Due to No File Selected"
    Exit Sub
Else
    Workbooks.Open FileName:=NewFFN
    Set fileWB = ActiveWorkbook
End If

Application.Calculation = xlCalculationAutomatic

 

[ummjay]

thanks, macro still ran as expected, but running into the same issue when the file is already open, and i run it again, then the rest of my macro doesnt work properly.

here is a small snipit, of what it does next. so it has issues with my formulas, but if i close the file manually before I run the entire thing, it works fine.

'insert columns
fileWB.Activate

Dim Column As Range
Set Column = Application.Range("M:M")
Column.Insert Shift:=xlShiftToRight, CopyOrigin:=xlFormatFromRightOrBelow

Dim Column2 As Range
Set Column2 = Application.Range("Q:Q")
Column2.Insert Shift:=xlShiftToRight, CopyOrigin:=xlFormatFromRightOrBelow

Dim Column3 As Range
Set Column3 = Application.Range("R:R")
Column3.Insert Shift:=xlShiftToRight, CopyOrigin:=xlFormatFromRightOrBelow

Dim Column4 As Range
Set Column4 = Application.Range("R:R")
Column4.Insert Shift:=xlShiftToRight, CopyOrigin:=xlFormatFromRightOrBelow

'add name to new columns
Range("M8").Value = "Formula"""
Range("Q8").Value = "Grouped by Q"""
Range("R8").Value = "Grouped by P"""
Range("S8").Value = "Formula"""

'1st formula
 With ActiveSheet.Activate
        'LastRow = Cells(Rows.Count, 1).End(xlUp).Row
         lastrow = ActiveSheet.Range("A9:A" & Rows.Count).Find("").Row - 1
         
        'LastRow = Intersect(ActiveSheet.Range("A9").CurrentRegion, Range("A:A"))
        ActiveSheet.Range("M9:M" & lastrow).Formula = "=P9*Y9"

        ActiveSheet.Range("R9").Formula2 = "=UNIQUE(P9:P" & lastrow & ")"

 

[BigBeachBananas]

How about this?

Dim macroWB As Workbook
Dim fileWB As Workbook
Dim NewFFN As Variant
Dim isOpen As Boolean

Set macroWB = ThisWorkbook

NewFFN = Application.GetOpenFilename(Title:="Please Select File")

If NewFFN = "False" Then
    MsgBox "Macro Terminated Due to No File Selected"
    Exit Sub
Else
    ' Check if the file is already open
    For Each fileWB In Workbooks
        If fileWB.FullName = NewFFN Then
            isOpen = True
            Exit For
        End If
    Next fileWB

    ' If the file is open, close it
    If isOpen Then
        Workbooks(NewFFN).Close SaveChanges:=False
    End If

    ' Open the selected file
    Workbooks.Open FileName:=NewFFN
    Set fileWB = ActiveWorkbook
End If

Application.Calculation = xlCalculationAutomatic

 

[ummjay]

works on the intial run (with no prev file open). When I try a 2nd time, and this time keeping the prev file open (to see if it closes it and opens the new file), it fails and highlights this row:
Workbooks(NewFFN).Close SaveChanges:=False

subscript out of range, when i hover over NewFFN, it does show the path/file.