Initialization of data source failed – When Opening PowerPivot Excel

data source Initialization failed
This error occurs due to Excel Version Compatibility. When a New Data Model is opened in an unsupported old Excel Version then it will throw out the “Initialization of data source failed” error.

Initialization of data source failed – When Opening PowerPivot Excel was last modified: October 6th, 2017 by Justin Antony

Slicer control on a multiple data source pivot excel – 1004 Runtime Error in VBA Script.

Slicer control multiple data source pivot excel VBA Script
Using the below code you can control the Slicer_Name2 from Slicer_Name1 belongs to different data source of a pivot Excel.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim sc1 As SlicerCacheLevel
Dim sc2 As SlicerCacheLevel
Dim sc3 As SlicerCache
Dim sc4 As SlicerCache
Dim ST1 As SlicerItem
Dim ST2 As SlicerItem
Dim sc As SlicerCache
Dim si As SlicerItem
Dim list As Variant
Application.ScreenUpdating = False
Application.EnableEvents = False
Set sc3 = ActiveWorkbook.SlicerCaches("Slicer_Name1")
Set sc4 = ActiveWorkbook.SlicerCaches("Slicer_Name2")
For Each sc1 In ThisWorkbook.SlicerCaches("Slicer_Name1").SlicerCacheLevels
For Each ST1 In sc1.SlicerItems
For Each sc2 In ThisWorkbook.SlicerCaches("Slicer_Name2").SlicerCacheLevels
For Each ST2 In sc2.SlicerItems
If ST1.Selected = True And ST1.Value = ST2.Value Then
If IsEmpty(list) Then
list = Array(ST2.Name)
Else
ReDim Preserve list(UBound(list) + 1)
list(UBound(list)) = ST2.Name
End If
sc4.VisibleSlicerItemsList = list                        
Exit For
End If
Next
Next
Next        
Next     
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Slicer control on a multiple data source pivot excel – 1004 Runtime Error in VBA Script. was last modified: October 1st, 2017 by Justin Antony

Refresh PowerPivot excel using SSIS

Refresh PowerPivot excel using SSIS

Install the SSIS_EXCEL_REFRESH.EXE in your ETL System and then you will find that component displaying in the SSIS Toolbox.

  1. Configure the EXCEL Refresh Tool
  2. Provide the Path where the Excel resides
  3. Set the Timeout for Refresh
  4. Run the ETL and check the Excel being refreshed.

You can check that by seeing the excel last modified date gets updated.

 

Refresh PowerPivot excel using SSIS was last modified: October 1st, 2017 by Justin Antony

Count cells that matchs criteria in Excel

Count cells match Excel criteria

COUNTIF Function:

=COUNTIFS(RangeHeading,">100",RangeHeading,"<500")
=COUNTIF(A1:A100,">500")     -- Count cells greater than value 500    
=COUNTIF(A1:A100,"jesus")    -- Count cells having jesus  
=COUNTIF(A1:A100,">" & B1 )  -- Count Cells greater than value in B1

Range Heading can be your table heading or column heading in the excel.

Count cells that matchs criteria in Excel was last modified: October 1st, 2017 by Justin Antony