Auto-Refresh Excel PowerPivot Data

When working with Excel a common reality is connecting to your databases in order to create for example a data abstraction layer for you analysis which can be accomplished with PowerPivot.

Although Excel comes with a lot of wizards to connect to databases and even to refresh your data, when you start developing multiple reports and the number of files you need to refresh for example every day increase, this might become a headache and not very easy to manage.

In order to ease your life, the following PowerShell script accepts as a parameter a “folder path” where you can put all your excel files and then refreshes all power pivot models in it.

cls

$input_folder_name="[YOUR_FOLDER]" 

[System.Threading.Thread]::CurrentThread.CurrentCulture = [System.Globalization.CultureInfo]::GetCultureInfo("en-US")
 
$interopAssembly = [Reflection.Assembly]::LoadWithPartialname("Microsoft.Office.Interop.Excel") 

$allWbks=@((Get-ChildItem -Path $input_folder_name -Filter "*.xls*"))
 
$allWbks | %{

    $file = $_.FullName

    "File: " + $file
     
    #kill all instances of MSExcel

    Get-Process | where{$_.ProcessName -like "*EXCEL*"} | kill

    #Create new Excel app
 
    $excel = new-object Microsoft.Office.Interop.Excel.ApplicationClass
 
    $book = $excel.Workbooks.Open($file)
 
    $book.Connections | %{
        

        #If PowerPivot Model (Type=7)
        #if($_.Type -eq "7")
        #{
            "Refreshing PowerPivot Model: " + $_.Name

            if($_ -ne $null){

                $_.Refresh()
            }
            
        #}   
        
    }

    $book.Save()

    $book.Close()
    
 
}

"Finished Processing"

Thank you