excel - 打开文件和刷新数据连接的 VBA 语法错误

Question

不知道这里发生了什么...在 Excel VBA 中创建一个子例程，通过传递给它的值打开并激活文件。显然我做错了什么......但不确定是什么。

Sub openBook(ByVal fName As String, ByVal activate As Boolean)
    Application.Workbooks.Open(fName, 0, False) '= Required here?
End Sub

编辑成功了，只是想检查一下以确保这是下面正确的语法查看更新后的代码:

Sub openBook(ByVal fileName As String, ByVal refresh As Boolean)
    Dim wb As Workbook

    Set wb = Workbooks.Open(fileName, 0, False)

    If refresh = True Then
        wb.RefreshAll
    End If

End Sub

Answer 1

Workbooks.Open 的语法是

表达式.Open(文件名、UpdateLinks、ReadOnly、格式、密码、WriteResPassword、IgnoreReadOnlyRecommended、起源、分隔符、可编辑、通知、转换器、AddToMru、本地、CorruptLoad)

这就是你正在尝试的吗？

Sub Sample()
    openBook "C:\MyFile.xlsx", False, True
End Sub

Sub openBook(fileName As String, UpdtLink As Boolean, RdOnly As Boolean)
    Application.Workbooks.Open fileName, UpdtLink, RdOnly
End Sub

编辑

如果您想传递 0/False 和 1/True 那么您必须更改

Sub openBook(fileName As String, UpdtLink As Boolean, RdOnly As Boolean)

至

Sub openBook(fileName As String, UpdtLink As Variant, RdOnly As Variant)

评论跟进

is there anyway to also activate that workbook in the same line or would another line of code be required? – metsales 1 min ago

为什么要激活它？应尽可能避免 .Activate。您可能想查看THIS

话虽如此，如果你想激活它，那么你必须使用这样的代码

Sub Sample()
    openBook "C:\MyFile.xlsx", False, True
End Sub

Sub openBook(fileName As String, UpdtLink As Boolean, RdOnly As Boolean)
    Dim wb As Workbook
    Set wb = Application.Workbooks.Open(fileName, UpdtLink, RdOnly)
    wb.Activate
End Sub

但是，以下是我根据我之前关于不使用 .Activate 的建议提出的建议

Dim wb As Workbook

Sub Sample()
    openBook "C:\MyFile.xlsx", False, True

    DoEvents

    With wb
        '
        '~~> Do something with the workbook here
        '
    End With
End Sub

Sub openBook(fileName As String, UpdtLink As Boolean, RdOnly As Boolean)
    Set wb = Application.Workbooks.Open(fileName, UpdtLink, RdOnly)
End Sub