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

 

 

3 thoughts on “Auto-Refresh Excel PowerPivot Data

  1. Hello Seguir, I have my powerpivot conencted to Teradata and when i try to use above script it prompts me to put username and password. This is a manual step which I want to avoid. Would you have any ideas how can we do it? Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s