VB.Net 使用 VBA 访问 Excel 数据透视表 [英] VB.Net using VBA to access Excel Pivot tables

问题描述

我创建了一个包含多张工作表的 xlsx 文件，每张工作表至少有一个数据透视表，同一工作表上的某种图表使用该数据透视表.所有这些数据透视表都使用来自另一个名为数据"的工作表中的相同数据.在我完成所有工作后，我去记录了一些宏，然后创建了一些 VBA 代码以在运行代码时更新数据透视表.所有这些都很好用，我制作了一个 xlsm 文件.

我决定是时候将所有内容合并到 VB.Net 中了.我的代码执行一个存储过程并带回我成功放入数据"表中所有其他表可以访问它的正确位置的数据.然后我从第一张带有数据透视表的工作表开始，这就是我的问题开始的地方.以下是 Excel 中有效的代码:

Sub UpdatePreviousMonth(intRecordCount As Integer)表格(上个月").选择范围(A6").选择ActiveSheet.PivotTables("ptPreviousMonth").ChangePivotCache _ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _"C:\Users\me\Documents\[My Charts.xlsm]Data!R1C4:R" &CStr(intRecordCount) &C4"，版本:=xlPivotTableVersion15)ActiveSheet.PivotTables("ptPreviousMonth").PivotFields("Names").AutoSort xlAscending, "Names"结束子

这是我在 VB.Net 中无法工作的代码:

Private Sub UpdatePreviousMonth(intRecordCount As Integer, xlWorkbook As Excel.Workbook, xlWorkSheets As Excel.Sheets, xlWorkSheetData As Excel.Worksheet)尝试Dim xlWorkSheet As Excel.Worksheet = CType(xlWorkSheets("Prev Month"), Excel.Worksheet) ' 此行有效...xlWorkSheet.PivotTables("ptPreviousMonth").ChangePivotCache _(xlWorkbook.PivotCaches.Create(SourceType:=Excel.XlPivotTableSourceType.xlDatabase, SourceData:=xlWorkSheetData.Range("R1C4:R" & CStr(intRecordCount) & "C4")，版本:=xlPivotTableVersion15))xlWorkSheet.PivotTables("ptPreviousMonth").PivotFields("Names").AutoSort(Excel.XlSortOrder.xlAscending, "Names")Catch ex 作为例外结束尝试结束子

问题从整个 xlWorkSheet.PivotTables("ptPreviousMonth").ChangePivotCache 行开始.xlWorkSheet.PivotTables("ptPreviousMonth").ChangePivotCache 警告Late bound resolution;可能会发生运行时错误". 我在 xlWorkSheet.PivotTables("ptPreviousMonth").PivotFields("Names").AutoSort 行收到了同样的警告.

并且 Version:=xlPivotTableVersion15 是一个例外，'xlPivotTableVersion15' 未声明.由于其保护级别，它可能无法访问.

如您所见，我声明了 xlWorkSheet，所以我不知道为什么会收到警告，除非它与我不认识的其余行有关.

从我读过的内容来看，xlPivotTableVersion15 是可选的，所以我尝试在没有 Version:=xlPivotTableVersion15 的情况下运行代码并得到 <块引用>

来自 HRESULT 的异常:0x800A03EC

这似乎意味着我缺少一个参数?

xlWorkSheetData 是数据所在的数据"表.

有人可以帮忙吗?我觉得我很接近但找不到解决方案?

解决方案

Edit - 我应该早点意识到这一点，但是你不能在 Range 功能.这会导致 xlWorkbook.PivotCaches.Create 语句的参数无效.下面的代码已更正以使用有效的语法.

<小时>

我真的建议您启用 Option Strict.您已经在使用一些强类型互操作变量，没有理由不完成这项工作并避免后期绑定的不确定性.

来自 Worksheet.PivotTables 方法的文档:

<块引用>

返回一个对象，该对象表示单个数据透视表(一个数据透视表对象)或所有数据透视表报告的集合(一个数据透视表对象)在工作表上.

由于这个函数可以返回多种类型，所以使用VB的TryCast Operator 将返回的 Object 转换为 PivotTable.

关于版本问题，学习使用对象浏览器的(键盘:F2或查看菜单->对象浏览器)搜索功能.它有助于找到定义此类项目的位置和方式.

Private Sub UpdatePreviousMonth(intRecordCount As Integer, xlWorkbook As Excel.Workbook, xlWorkSheets As Excel.Sheets, xlWorkSheetData As Excel.Worksheet)尝试Dim xlWorkSheet As Excel.Worksheet = CType(xlWorkSheets("Prev Month"), Excel.Worksheet)Dim pt As Excel.PivotTable = TryCast(xlWorkSheet.PivotTables("ptPreviousMonth"), Excel.PivotTable)如果 pt IsNot Nothing 那么Dim ver As Excel.XlPivotTableVersionList = Excel.XlPivotTableVersionList.xlPivotTableVersion15Dim sourceDataRange As Excel.Range = xlWorkSheetData.Range("A1:A1").Resize(intRecordCount)Dim 缓存 As Excel.PivotCache = xlWorkbook.PivotCaches.Create(SourceType:=Excel.XlPivotTableSourceType.xlDatabase, SourceData:=sourceDataRange, Version:=ver)pt.ChangePivotCache(缓存)万一pt = CType(xlWorkSheet.PivotTables("ptPreviousMonth"), Excel.PivotTable)如果 pt IsNot Nothing 那么Dim field As Excel.PivotField = CType(pt.PivotFields("Names"), Excel.PivotField)field.AutoSort(Excel.XlSortOrder.xlAscending，名称")万一Catch ex 作为例外结束尝试结束子

I created an xlsx file with several sheets, each having at least one pivot table which is used by some kind of chart on the same sheet. All these pivot tables use the same data from another sheet called "Data". After I got everything to work I then went and recorded some macros and then created some VBA code to update the pivot tables whenever I ran the code. All this works beautifully and I made an xlsm file.

I decided it was time to incorporate everything to VB.Net. My code executes a stored procedure and brings back data which I successfully put into the "Data" sheet in the correct location where all the other sheets can access it. I then started with the first sheet with a pivot table and this is where my problems start. Below is the code from Excel that works:

Sub UpdatePreviousMonth(intRecordCount As Integer)

    Sheets("Prev Month").Select

    Range("A6").Select

    ActiveSheet.PivotTables("ptPreviousMonth").ChangePivotCache _
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "C:\Users\me\Documents\[My Charts.xlsm]Data!R1C4:R" & CStr(intRecordCount) & "C4", Version:=xlPivotTableVersion15)

    ActiveSheet.PivotTables("ptPreviousMonth").PivotFields("Names").AutoSort xlAscending, "Names"

End Sub

Here is the code I have in VB.Net which does not work:

Private Sub UpdatePreviousMonth(intRecordCount As Integer, xlWorkbook As Excel.Workbook, xlWorkSheets As Excel.Sheets, xlWorkSheetData As Excel.Worksheet)
        Try
            Dim xlWorkSheet As Excel.Worksheet = CType(xlWorkSheets("Prev Month"), Excel.Worksheet) ' This line works...

            xlWorkSheet.PivotTables("ptPreviousMonth").ChangePivotCache _
                (xlWorkbook.PivotCaches.Create(SourceType:=Excel.XlPivotTableSourceType.xlDatabase, SourceData:=xlWorkSheetData.Range("R1C4:R" & CStr(intRecordCount) & "C4"), Version:=xlPivotTableVersion15))    

xlWorkSheet.PivotTables("ptPreviousMonth").PivotFields("Names").AutoSort(Excel.XlSortOrder.xlAscending, "Names")

            Catch ex As Exception
                End Try
  End Sub

The issue starts on the entire xlWorkSheet.PivotTables("ptPreviousMonth").ChangePivotCache line. xlWorkSheet.PivotTables("ptPreviousMonth").ChangePivotCache warns that "Late bound resolution; runtime errors could occur". I get the same warning at the xlWorkSheet.PivotTables("ptPreviousMonth").PivotFields("Names").AutoSort line.

And Version:=xlPivotTableVersion15 is an exception of 'xlPivotTableVersion15' is not declared. It may be inaccessible due to its protection level.

As you can see I declared xlWorkSheet so I don't know why I'm getting the warning, unless it has something to do with the rest of the line that I don't recognize.

From what I have read xlPivotTableVersion15 is optional so I tried running the code without the Version:=xlPivotTableVersion15 and got

Exception from HRESULT: 0x800A03EC

which appears to mean I'm missing a parameter?

xlWorkSheetData is the "Data" sheet where the data resides.

Can anyone help? I feel I'm close but can't find the solution(s)?

解决方案

Edit - I should have realized this sooner, but you can not use the RC style in the Range function. This is causing an invalid argument to the xlWorkbook.PivotCaches.Create statement. The code below has been corrected to use a valid syntax.

---

I would really recommend that you enable Option Strict. You are already using some strongly typed interop variables and there is no reason to not finish the job and avoid the uncertainty of late-binding.

From the documentation for Worksheet.PivotTables Method:

Returns an object that represents either a single PivotTable report (a PivotTable object) or a collection of all the PivotTable reports (a PivotTables object) on a worksheet.

Since this function can return multiple types, use VB's TryCast Operator to convert the returned Object to a PivotTable.

With the version issue, learn to use the Object Browser's (Keyboard: F2 or View Menu->Object Browser) search function. It helps to find where and how such items are defined.

Private Sub UpdatePreviousMonth(intRecordCount As Integer, xlWorkbook As Excel.Workbook, xlWorkSheets As Excel.Sheets, xlWorkSheetData As Excel.Worksheet)
    Try
        Dim xlWorkSheet As Excel.Worksheet = CType(xlWorkSheets("Prev Month"), Excel.Worksheet)

        Dim pt As Excel.PivotTable = TryCast(xlWorkSheet.PivotTables("ptPreviousMonth"), Excel.PivotTable)
        If pt IsNot Nothing Then
            Dim ver As Excel.XlPivotTableVersionList = Excel.XlPivotTableVersionList.xlPivotTableVersion15
            Dim sourceDataRange As Excel.Range = xlWorkSheetData.Range("A1:A1").Resize(intRecordCount)
            Dim cache As Excel.PivotCache = xlWorkbook.PivotCaches.Create(SourceType:=Excel.XlPivotTableSourceType.xlDatabase, SourceData:=sourceDataRange, Version:=ver)
            pt.ChangePivotCache(cache)
        End If

        pt = CType(xlWorkSheet.PivotTables("ptPreviousMonth"), Excel.PivotTable)
        If pt IsNot Nothing Then
            Dim field As Excel.PivotField = CType(pt.PivotFields("Names"), Excel.PivotField)
            field.AutoSort(Excel.XlSortOrder.xlAscending, "Names")
        End If

    Catch ex As Exception
    End Try
End Sub

这篇关于VB.Net 使用 VBA 访问 Excel 数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！