Created PivotTables that have same SourceWorkSheet and same SourceRange, but manually created slicer cannot see both of them about importexcel HOT 2 OPEN

I have also tried further updating the source of my pivot tables utilizing ComObjects with

$excelCom = New-Object -ComObject Excel.Application
$workbookCom = $excelCom.Workbooks.Open($ExcelFilePath)

$worksheetCom = $workbookCom.Worksheets.Item("Data Overview")
$worksheetCom.Activate()

worksheetCom.PivotTables(1).SourceData = $worksheetCom.PivotTables(2).SourceData
worksheetCom.PivotTables(2).SourceData = $worksheetCom.PivotTables(1).SourceData

to try to ensure they have the same source and it still does not work

from importexcel.

For now, I have made a workaround for this but creating a macro in Excel that does all the manual actions and having my PowerShell script add this macro to my generated Excel files and then run it.

For those who might read this and are looking for the code to do that (assuming you already have created the macro in an Excel file and exported it into your file explorer)...

$excelCom = New-Object -ComObject Excel.Application
$excelCom.Visible = $true
$workbookCom = $excelCom.Workbooks.Open($ExcelFilePath)
$worksheetCom = $workbookCom.Worksheets.Item($workSheetName)
$worksheetCom.Activate()

$xlmodule = $workbookCom.VBProject.VBComponents.Add(1)
$xlmodule.CodeModule.AddFromFile($filePathtoYourMacro)
$excelCom.Run("MacroName")

There might also be a way to use the VBA code that gets generated from the Macro and rewrite it in PowerShell and it would work. But as I just have it working right now with the Macro, I am not going to try to rewrite it.

from importexcel.