VB.Net using VBA to access Excel Pivot tables

Question

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)?

Answer 1

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