[PowerShell] 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

 

 

Anúncios

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s