We are developing Dashboard using Power Query and Power Pivot; I intend to refresh the queries and Model at a scheduled Time.
The script is working but not refreshing the power query, please assist
$input_folder_name=$args[0]
[System.Threading.Thread]::CurrentThread.CurrentCulture = [System.Globalization.CultureInfo]::GetCultureInfo("en-US")
$interopAssembly = [Reflection.Assembly]::LoadWithPartialname("Microsoft.Office.Interop.Excel")
$ens = Get-ChildItem -Path $input_folder_name -Filter *.xlsx | sort LastWriteTime -Descending | select-object FullName,BaseName
foreach($e in $ens)
{
$file = $e.FullName
$name = $e.BaseName
"File: " + $file
$E = New-Object -ComObject Excel.Application
$E.Visible = $true
$E.DisplayAlerts = $false
$wb = $E.Workbooks.Open($file)
"Refreshing PowerPivot Model: "+$name
if($wb -ne $null){
"Refresh"
$wb.RefreshALL()
}
$wb.Save()
$wb.Close()
}
"Finished Processing"
