Xlookup 宏重新打开已打开的文件

发布于 01-21 02:56 字数 634 浏览 2 评论 0原文

我需要自动化我不在办公室时运行的报告，但是这个部分使我失败了。如何阻止它要求我打开一个已经打开的文件？我不知道我错过了什么。稍后，在其他地方的代码中再次重复这一点，并引起与我引用同一文件相同的问题。

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)"

原文

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)"

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

就此别过2025-01-28 02:56:50

您可以这样做：

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)

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)

回复收藏 0 原文

~没有更多了~