关于vba：运行时错误1004“无法打开数据透视表源文件”

2024-07-03 09:17| 来源: 网络整理| 查看: 265

我想基于同一工作簿中的数据集(包含在工作表中)创建数据透视表。

当我运行宏时，工作簿是打开的。数据集来自在Access中运行查询，然后将其导出到excel。我还尝试在运行宏之前保存工作簿。我正在使用Excel 2016。

这是我的代码：

123456789101112131415161718192021Sub BusinessInteligenceCreatePivotTable() Dim PivotSheet As Worksheet Dim pvtCache As PivotCache Dim pvt As PivotTable 'Determine the data range you want to pivot Set pvtCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=wsPartsMachines.Name &"'!" & wsPartsMachines.Range("A1").CurrentRegion.Address, Version:=xlPivotTableVersion15) 'Create a new worksheet With ThisWorkbook Set PivotSheet = .Sheets.Add(After:=.Sheets(.Sheets.Count)) PivotSheet.Name ="Production Schedule" End With PivotSheet.Activate Range("A1").Select 'Create Pivot table from Pivot Cache 'Set pvt = pvtCache.CreatePivotTable(TableDestination:=ActiveCell, TableName:="ProductionSchedule") Set pvt = PivotSheet.PivotTables.Add(PivotCache:=pvtCache, TableDestination:=ActiveCell, TableName:="ProdSched") End Sub

最后两行生成相同的错误消息。"运行时错误1004。无法打开数据透视表源文件'C：\ Users ...'"。

有人知道如何解决这个问题吗？谢谢。

编辑当我记录一个宏时，VBA会给我这个代码(它可以工作)。

123456789101112131415Sub BusinessInteligenceCreatePivotTable() Dim PivotSheet As Worksheet With ThisWorkbook Set PivotSheet = .Sheets.Add(After:=.Sheets(.Sheets.Count)) PivotSheet.Name ="Production Schedule" End With PivotSheet.Activate Range("A1").Select ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Parts & Machines2!R1C1:R1328C14", Version:=6).CreatePivotTable _ TableDestination:=ActiveCell, TableName:="PivotTable1", DefaultVersion:=6 End Sub

我希望动态设置SourceData的范围。我为此所做的努力(使用debug.print)生成：'Parts＆Machines2'！R1C1：R1328C14 它似乎与宏录制的" Parts＆Machines2！R1C1：R1328C14"不同。

正是这种差异产生了我找不到源数据的错误？

数据的屏幕截图。

此代码有效。仍然不确定SourceData为什么不起作用。在Shai Rado建议的代码中进行了少量更改。下面是代码。

1234567891011121314151617181920212223242526272829Sub BusinessInteligenceCreatePivotTable() Dim PivotSheet As Worksheet Dim pvtCache As PivotCache Dim pvtTable As PivotTable Dim ws1PartMachines As Worksheet Dim pvtDataRng As Range 'Determine the data range you want to pivot Set ws1PartsMachines = Worksheets("Parts & Machines2") ' set the Pivot Table Data Source Range Set pvtDataRng = ws1PartsMachines.Range("A1").CurrentRegion Set pvtCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=pvtDataRng) Debug.Print pvtCache.SourceData 'Create a new worksheet With ThisWorkbook Set PivotSheet = .Sheets.Add(After:=.Sheets(.Sheets.Count)) PivotSheet.Name ="Production Schedule2" End With PivotSheet.Activate Range("A1").Select 'Create Pivot table from Pivot Cache Set pvtTable = PivotSheet.PivotTables.Add(PivotCache:=pvtCache, TableDestination:=ActiveCell, TableName:="ProdSched") End Sub

我不确定在哪里定义wsPartsMachines as Worksheet以及在哪里设置。

但是，该错误在该行中：

1Set pvtCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=wsPartsMachines.Name &"'!" & wsPartsMachines.Range("A1").CurrentRegion.Address, Version:=xlPivotTableVersion15)

如果在以下位置添加一行：

1Debug.Print pvtCache.SourceData

您将在立即窗口中看到'Sheet3'''!R1C1:R6C3-您有太多'。 (我将" Sheet3"用作我的SourceData) 尝试将此行修改为：

1Set pvtCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=wsPartsMachines.Name &"!" & wsPartsMachines.Range("A1").CurrentRegion.Address)

编辑1：尝试另一种方法，将数据源透明度设为Range：

12345678910Dim pvtDataRng As Range Set wsPartsMachines = Sheets("Parts & Machines2") ' set the Pivot Table Data Source Range Set pvtDataRng = wsPartsMachines.Range("A1").CurrentRegion Set pvtCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=pvtDataRng) 'Create Pivot table from Pivot Cache Set pvt = PivotSheet.PivotTables.Add(PivotCache:=pvtCache, TableDestination:=ActiveCell, TableName:="ProdSched")

相关讨论零件和机器2！R1C1：R1328C14零件和机器2！R1C1：R1328C14 wsPartsMachines是工作表名称，即在" VBA编辑器"中将名称改为" wsetsMachines"，而不是" Sheet3"。从原始代码中删除""时，Debug.Print pvtCache.SourceData会在不进行任何原始代码编辑的情况下生成此文件：Debug.Print pvtCache.SourceData在从原始代码中删除""时会生成此文件：Parts＆Machines2！R1C1：R1328C14运行此代码时(我删除了此错误消息，我在"设置pvt ="行插入："运行时错误1004。 @ Energizer1意味着您的数据没有按照构建数据透视表的方式进行组织。您可以在上面的帖子中添加数据的屏幕截图吗？您看到我的最新问题了吗？我认为问题可能出在" SourceData"上。由于宏记录的SourceData的地址与我自己动态设置的SourceData不同。我还将发布带有数据透视表数据的工作表的屏幕截图。感谢您的所有帮助。

【本文地址】

公司简介

联系我们