Excel VBA 在另一个工作簿中使用数据透视表数据

Excel VBA Using pivot table data in another workbook

提问by qu33nb33

The following Sub asks the user to select a generic template to open, then asks the user to select a source file (to populate the template). The source file is contains a number of worksheets and pivot tables. The sub then selects data from a pivot table and copies it into the template. I need the source file to be a variable vs. a hard coded pivot table source bc this title changes based on the users selection.

以下 Sub 要求用户选择要打开的通用模板，然后要求用户选择源文件（以填充模板）。源文件包含许多工作表和数据透视表。然后 sub 从数据透视表中选择数据并将其复制到模板中。我需要源文件是一个变量，而不是一个硬编码的数据透视表源 bc 这个标题会根据用户的选择而改变。

Issue 1: when the data is copied it only shows REF! instead of the actual data (even when the data is present).

问题 1：复制数据时只显示 REF！而不是实际数据（即使数据存在）。

'Open Generic Report to populate with data
Dim GenericFolderLocation As String
MsgBox "Please select the generic porfolio template..."
GenericFolderLocation = "C:\Users\user.name\Desktop"
   ChDrive GenericFolderLocation

  SelectedFile = Application.GetOpenFilename( _
        fileFilter:="Excel Files (*.xls*), *.xls*", MultiSelect:=False)

        Workbooks.Open (SelectedFile)
        Set test = ActiveWorkbook

Dim SourceFolderLocation As String
Dim FileName As String
Dim SourceFile As String
MsgBox "Please select the data source file..."
SourceFolderLocation = "C\Users\user.name\Desktop"
ChDrive SourceFolderLocation
SourceFile = Application.GetOpenFilename( _
    fileFilter:="Excel Files (*.xls*), *.xls*", MultiSelect:=False)

    Workbooks.Open (SourceFile)
    Set wkbk = ActiveWorkbook

    test.Activate

    'Test1
    'Select empty cell in Chart template 
    Range("C28").Select
    'Populate with pivot table data from sourceFile
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""  Value"",'[wkbk]ActCost_PIVOT'!R3C1,""Team"",""Field1"",""Row Descrption"",""Row1"",""Type"",""DataPoint1"")"

    'Repeat for next cell
    Range("C27").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""  Value"",'[wkbk]CRActCost_PIVOT'!R3C1,""Team"",""Field1"",""Row Descrption"",""Row1"",""Type"",""DataPoint2"")"

[Resolved] Issue 2. Similar to issue 1 I would like to make things like "TypeName" or "TeamName" variables. Can I just declare them outside the Sub like this-

[已解决] 问题 2。与问题 1 类似，我想创建诸如“TypeName”或“TeamName”变量之类的东西。我可以像这样在 Sub 之外声明它们吗？

    Dim TeamName As String

    Sub()

    TeamName = "Tigers"

    End Sub

Thanks for your help!

谢谢你的帮助！

回答by varocarbas

There is no problem to define global variables (outside any sub/function) in VBA. What you propose for TeamNamewould be fine (write the variable define at the top of the given module). You can use this variable afterwards inside this module without any problem. For example by doing:

在 VBA 中定义全局变量（在任何子/函数之外）没有问题。您建议的内容TeamName很好（在给定模块的顶部编写变量定义）。之后您可以在此模块中使用此变量而不会出现任何问题。例如通过做：

ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""  Sum"",'[SourceFile]WorkbookA_PIVOT1'!R3C1,""Team/Branch""," & """" & TeamName & """" & ",""Row Descrption"",""RowName"",""Type"",""TypeName"")"

As you can see, you have to take care of the quotes in case of being required (to escape them). A trickto avoid confusions is putting them completely appart (""""), as shown above.

如您所见，您必须处理引号以防被需要（以逃避它们）。避免混淆的一个技巧是将它们完全分开 ("""")，如上所示。

Other issue I wanted to highlight is that you can access all the contents of any PivotTabledirectly via VBA (you don't need the formula in your question):

我想强调的另一个问题是，您可以PivotTable通过 VBA 直接访问 any 的所有内容（您不需要问题中的公式）：

 Dim pivotTable As pivotTable
 Set pivotTable = ActiveSheet.PivotTables("PivotTable Name")

From the pivotTablevariable you have full access to all the contents of the given PivotTable. This represents the kind of advantages of VBA over Excel, which you should maximise (if you continue relying on formulae, why using VBA at all?).

从pivotTable变量中，您可以完全访问给定数据透视表的所有内容。这代表了 VBA 相对于 Excel 的优势，您应该最大限度地发挥它的优势（如果您继续依赖公式，为什么要使用 VBA？）。

I guess that they were implicit in my aforementioned answer, but just to make sure, here you have my answers to your specific questions:

我想它们隐含在我前面提到的答案中，但只是为了确保，这里有我对您的具体问题的答案：

1. Making the name of workbook A a variant: I want the file the user selects to be called "SourceFile". This isn't working... not sure why... -> if you have a code working as you want, you can replace a string on it with a variable (for example: "workbookA"), just by insert the variable in the right place and concatenating it with the remaining bits via &s. In case of having quotes you have to escape them as suggested above.
2. Similar to issue 1 I would like to make things like "TypeName" or "TeamName" variables. Can I just declare them outside the Sub like this -> the code you propose for TeamNameis right and you should emulate it for any other (string) variable you want to use globally. Nevertheless, bear in mind that having too many global variables is not a good coding practice; intend to rely on local variables as much as possible.

1. 使工作簿 A 的名称变体：我希望用户选择的文件称为“SourceFile”。这不起作用......不知道为什么...... - >如果你有一个你想要的代码，你可以用一个变量替换它上面的字符串（例如：“workbookA”），只需插入变量在正确的位置并通过&s将其与其余位连接起来。如果有引号，您必须按照上面的建议将它们转义。
2. 与问题 1 类似，我想创建诸如“TypeName”或“TeamName”变量之类的东西。我可以像这样在 Sub 之外声明它们吗 -> 您建议的代码TeamName是正确的，您应该为要全局使用的任何其他（字符串）变量模拟它。然而，请记住，拥有过多的全局变量并不是一个好的编码习惯；打算尽可能地依赖局部变量。