excel - 打开/关闭工作簿并刷新连接

Question

我在共享网络驱动器上有两个工作簿:

工作簿 A(表)

工作簿 B(数据透视表 - 连接到源工作簿 A)

我正在尝试，当打开 Workbook B 时，运行宏并执行以下操作:

取消保护工作簿 B 上的某个工作表

如果工作簿 A 处于打开状态，请刷新工作簿 B 上的数据连接

如果工作簿 A 已关闭，请打开工作簿 A 并刷新工作簿 B 上的数据连接，然后关闭工作簿 A。

保护工作簿 B 上的某个工作表

到目前为止，在大多数情况下测试时，下面的代码都可以按预期工作，但是如果其他人在其他计算机上打开了工作簿 A 时尝试在他们的计算机上打开工作簿 B，它会将工作簿 A 作为只读文件打开并保留它在他们的电脑上打开。我需要它在他们的计算机上关闭，并在另一台计算机上保持最初的打开状态。

Public Sub RefreshPvt()
ThisWorkbook.Worksheets("Sheet1").Unprotect
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wkb As Workbook

If IsFileOpen("S:\\Testing\Job Closeout Status Test.xlsx") Then
    ThisWorkbook.RefreshAll
Else
Set wkb = Workbooks.Open(filename:="S:\\Testing\Job Closeout Status Test.xlsx")
ThisWorkbook.RefreshAll
wkb.Close SaveChanges:=False
End If

ThisWorkbook.Worksheets("Sheet1").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer

    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' Another error occurred.
        Case Else
            Error errnum
    End Select

End Function

Answer 1

如果您在即时窗口中对工作簿 B 运行 ThisWorkbook.RefreshAll 是否有效？

您也可以使 XLApp.Visible = true 来查看它是否正在打开

我认为你应该使用更多类似的东西:

Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables

    pt.RefreshTable

Next pt

要完成整个工作簿，您可以使用:

Sub AllWorkbookPivots()
Dim pt As PivotTable
Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets

        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws

End Sub