1

I have a snippet of code that is run from MS project to extract data and then creates a pivot table in excel. This is working fine. When I tried to add a slicer to that pivot table, the icon appears to be greyed out. I googled over the internet and found out that it is related to older versions of excel and it can be fixed when file is saved in compatibility mode. My problem is unique in that sense because I have Microsoft365 subscription so I am already using the latest version of excel....

To narrow down my problem, my pivot tables are getting created as an older version and I don't have a clue. I have shared my code to have some feedback please.

I have added following reference to excel

Sub AddReferenceToExcel()
'this sub adds the correct reference to excel to ensure that the export works
Dim Major As Long
Dim Minor As Long
On Error Resume Next
    Major = 1
    With ActiveProject.VBProject.References
        .Remove .Item("Excel")
        Major = 1
        For Minor = 9 To 5 Step -1
            Err.Clear
            .AddFromGuid "{00020813-0000-0000-C000-000000000046}", Major, Minor
            If Err.Number = 0 Then
                Exit For    'no error, so reference set
            End If
        Next Minor
    End With
End Sub

'Creating Pivot table
'====================
Dim pt As PivotTable
Dim ptfield As PivotField
Dim strField As String
Dim xlSheet As Excel.Worksheet
Set xlSheet = xlAPP.Worksheets(1)
Dim PTOutput As Excel.Worksheet
Dim PTCache As PivotCache
Dim PRange As Range
Dim finalRow As Variant
Dim finalCol As Variant

'Setting Pivot Range on Sheet1
'=============================
xlSheet.Activate
' Find the last row with data
finalRow = xlSheet.Cells(xlAPP.Rows.Count, 3).End(xlUp).Row - 3
' Find the last column with data
finalCol = xlSheet.Cells(4, xlAPP.Columns.Count).End(xlToLeft).Column
' Find the range of the data
Set PRange = xlSheet.Cells(4, 1).Resize(finalRow, finalCol)
ActiveSheet.ListObjects.Add(xlSrcRange, PRange, , xlYes).Name = "T_PivotData"

Set PTOutput = xlAPP.Worksheets(3)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="T_PivotData")
'Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(20, 1), TableName:="ForecastPivotFTE")
Muhammad Mateen
  • 11
  • 1

1 Answers1

1

The issue you're facing is likely due to the way the Excel reference is being added in the AddReferenceToExcel subroutine. The loop is trying to add references starting from older versions of Excel and stops when it finds a version that works. This might be causing your code to reference an older version of Excel, even if you have the latest version installed.

Here's how you can address the issue:

  1. Explicitly Set Excel Version: Instead of looping through older versions, explicitly set the reference to the latest version. For Microsoft 365, you might want to use version 16. Here's how you can modify the AddReferenceToExcel subroutine:
Sub AddReferenceToExcel()
    'this sub adds the correct reference to excel to ensure that the export works
    On Error Resume Next
    With ActiveProject.VBProject.References
        .Remove .Item("Excel")
        .AddFromGuid "{00020813-0000-0000-C000-000000000046}", 1, 16 ' Explicitly set to version 16
    End With
End Sub

  1. Use Version-Specific Object Variables: Instead of using generic object types like PivotTable, PivotField, etc., try using version-specific object types like Excel.PivotTable, Excel.PivotField, etc. This ensures that you're using the latest version's objects.

  2. Update Pivot Cache Creation: When creating the pivot cache, you're using ActiveWorkbook.PivotCaches.Add. Instead, try using the xlAPP reference to ensure you're working with the correct Excel instance:

Set PTCache = xlAPP.ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="T_PivotData")
  1. Save in Modern Excel Format: After creating the pivot table, save the workbook in the modern Excel format (.xlsx). This ensures that the file is not in compatibility mode:
xlAPP.ActiveWorkbook.SaveAs "YourFilePath.xlsx", FileFormat:=51 '51 corresponds to .xlsx format

  1. Check Excel Add-ins: Sometimes, certain add-ins can interfere with Excel's functionality. Try disabling add-ins one by one to see if any of them are causing the issue.

  2. Update Office: Ensure that your Microsoft 365 subscription is up-to-date. Sometimes, updates can fix compatibility and functionality issues.

  3. Recreate Pivot Table in Excel: As a last resort, after exporting the data to Excel, manually create the pivot table in Excel to see if the slicer option is available. This can help you determine if the issue is with the VBA code or with Excel itself.

If you've tried all these suggestions and the issue persists, there might be other factors at play, and a deeper dive into the entire code and environment would be needed.

Joaquin
  • 452
  • 7