旨在实现： 我想改变透视表的SourceData。

Aim to achieve : I want to change the SourceData of the Pivot table.

我想从 C：\ [file.xlsx] SheetName改变！RangeName 到SheetNameRangeName

我有以下的实现：

For Each table In sheet.PivotTables
    Dim str = "'" + Split(table.SourceData, "]")(1)
    table.SourceData = str   // Gives Exception here
Next table

// Exception: Exception from HRESULT: 0x800A03EC

在误差 STR 的时间都有价值SheetName！RangeName （这是很好..）

At the time of error str has value 'SheetName'!RangeName (This is fine.. )

我也越来越异常之前一个消息框： 无法打开源文件C：\ file.xlsx （是不是很明显，因为它在使用中）

I am also getting a message box before the exception : Can not open the source file C:\file.xlsx (Isn't that obvious because its in use)

请帮助..这部作品在VB作为宏。

Please help.. this works in VB as a Macro.

推荐答案

这是VB.Net版本，如果你会使用它在vb.net-EXE。它不会在EXCEL.VBA工作，但据我所知，你想要一个VB.Net版本？

This is the VB.Net version as if you would use it in an vb.net-exe. It will not work in EXCEL.VBA, but as I understand it you wanted a VB.Net version?

Imports Microsoft.Office.Interop

Sub ChangePivotTableSourceData()
    Dim e As New Excel.Application
    e.Workbooks.Open("c:\testdoc.xlsx", , True) 'Open the doc readonly
    Dim sheet As Excel.Worksheet = e.ActiveSheet

    For Each table As Excel.PivotTable In sheet.PivotTables
        table.SourceData = "'" + Split(table.SourceData, "]")(1)
    Next

    e.ActiveWorkbook.SaveAs("c:\testdoc_changed.xlsx") 'Save under another name
    e.ActiveWorkbook.Close()
    e.Quit()
    sheet = Nothing
    e = Nothing
End Sub