从另一个工作簿更新数据透视表工作簿中的数据

debugcn 发表于 Dev

雅各布·特拉兹（JakubTracz）

我遇到了一件奇怪的事情：我加入了三个工作簿：Personal Data Tracker，Global Tracker以及带有数据透视图和图表的工作簿。逻辑如下：工作完成后，用户单击按钮，因此数据被复制到GL Tracker。在GL跟踪器表中触发更改事件后，将打开最后一个工作簿，在打开通风口后刷新枢轴，并关闭wb。

一切似乎都工作正常，但是当我实时运行宏时，最后我收到一条错误消息，内容是

“应用程序定义或对象定义的错误”。

仅显示“确定”和“帮助”按钮，它没有使VBE打开，因此我可以对其进行调试。任何人都不会知道即使整个链条都正常工作会发生什么情况？谢谢。

来自个人跟踪器的代码：

Sub test()

Dim path As String
Dim wb As Workbook

path = ThisWorkbook.path & "\Dest.xlsm"

Application.ScreenUpdating = False

ThisWorkbook.Sheets("Total").Range("R1").Value = Date
Range("R1").Font.Color = VBA.ColorConstants.vbWhite
Worksheets("TOTAL").Range("B2:B13").Copy

On Error GoTo Handler
Workbooks.Open (path)
On Error GoTo 0

Set wb = Workbooks("Dest")
Worksheets("Sheet1").Range("B2").PasteSpecial Paste:=xlPasteValues
Exit Sub

Handler:
 MsgBox "Someone else is saving their data at the moment." & vbNewLine & _
    "Please try in a few seconds"
End Sub

来自GL跟踪器的代码：

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MRange As Range
Dim wbPivot As Workbook
Dim pt As PivotTable
Dim ws As Worksheet
Dim Name As String
Dim answer As VbMsgBoxResult

Set MRange = ThisWorkbook.Sheets(1).Range("Table1")
Name = Application.UserName

Application.ScreenUpdating = False

If Not Intersect(Target, MRange) Is Nothing Then
    Application.EnableEvents = True
Set wbPivot = Workbooks.Open("C:\Users\jakub\Desktop\Excel - various\Pivot.xlsm")
End If

'refresh

For Each ws In wbPivot.Worksheets
    For Each pt In ws.PivotTables
        pt.PivotCache.Refresh
        pt.Update
        pt.RefreshTable
    Next
Next

'saving

Application.ScreenUpdating = True

If Application.UserName <> "Jakub Tracz" Then
    MsgBox "User not authorised. Workbook will be closed."
            wbPivot.Close True
            ThisWorkbook.Close True
    Else
        answer = MsgBox(Prompt:="Do you want to save and close the workbook?", _
            Buttons:=vbYesNo + vbQuestion)

    Select Case answer
        Case vbYes
            wbPivot.Close True
            ThisWorkbook.Close True
        Case vbNo
            MsgBox "Welcome, " & Application.UserName
    End Select
End If
End Sub

布拉克斯

我将为您提供概念证明代码作为示例供您使用。这并不能完全用您可以复制/粘贴的代码来回答您的问题，但是您可以使用它按照您希望的方式将其组合在一起，而无需我对很多事情进行假设并自行进行重组。

这只是演示了如何在一个可以引用另一个工作簿的例程中使用工作簿对象变量，以及如何对第二个工作簿进行更改并保存/关闭它。

Sub Tracker_Update()
  Dim wbPivot as Workbook

  ' open the workbook
  Set wbPivot = Workbooks.Open("C:\Users\jakub\Desktop\Excel - various\Test.xlsx")

  ' optionally make it hidden
  wbPivot.Visible = False

  With wbPivot

    ' pretend this code updates the pivot table
    .Worksheets(1).Range("A1") = "hello world"

    ' Close and save it
    .Close True

  End With

  ' optionally clear the variable 
  ' this is not really needed in VBA, but if you eventually
  ' start using VB.NET with Excel as a COM object,
  ' you will want to know how to do this part when you are done
  Set wbPivot = Nothing

End Sub

我认为您最终还是会更喜欢这种方法，因为代码不会在不同的地方分散很多。以后更容易调试，并且其他人在离开公司后也更容易了解您在做什么。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。