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)"
You can do it like this: