Xlookup Macro re-opening already open file

Question

I need to automate a report I run for when I'm out of office, but this one section keeps failing me. How do I stop it from asking me to open an already open file? I cannot figure out what I missed. This is later repeated again in code elsewhere and causes the same issue as I'm referencing the same file.

ChDir "H:\SCOSBaker\Backlog Reports\Oracle Backlog"
Workbooks.Open Filename:= _
("H:\SCOSBaker\Backlog Reports\Oracle Backlog\Backlog_" & Format(Now(), "MMDDYY") & ".xlsx")

Dim FilenameSufffix As String
    FilenameSuffix = Format(Now(), "MMDDYY")
    
Dim XLSXFilename As String
    XLSXFilename = "Backlog_" & FilenameSuffix & ".xlsx"

ActiveCell.FormulaR1C1 = _
        "=XLOOKUP(RC[3],[XLSXFilename]Copy_Of_Query_SO_Config_Lines!C19,[XLSXFilename]Copy_Of_Query_SO_Config_Lines!C7)"

Answer 1

You can do it like this:

Const FPATH As String = "H:\SCOSBaker\Backlog Reports\Oracle Backlog\"

Dim wb As Workbook, fName As String

fName = "Backlog_" & Format(Now(), "MMDDYY") & ".xlsx"

On Error Resume Next
Set wb = Workbooks(fName) 'try to get a reference: ignore error if not open
On Error GoTo 0           'stop ignoring errors

'if not open then open it...
If wb Is Nothing Then Set wb = Workbooks.Open(FPATH & fName)