Tuesday, January 26, 2016

PowerShell to update and refresh Excel data connections

Scenario:
You have multiple Excel files that applies the same template and formatting, but uses different data sources.
*** My requirement was to replace oData connections. I needed a simple script that would open an Excel file, loop through the data connections, and replace the data connection string.

Tested on Excel 2013, run PowerShell 64bit using Windows 7 Pro.

 $libraryPath = "D:\PowerShell\"  
 # To view Excel file open need to set below property to True  
 #$excel.visible = $true  
 $excel = new-object -comobject Excel.Application  
 # Give delay to open  
 Start-Sleep -s 5  
 $allExcelfiles = Get-ChildItem $libraryPath -recurse -include “*.xls*”  
 $newString = "Test ABC"  
 foreach ($file in $allExcelfiles)  
 {  
      $workbookpath = $file.fullname  
           Write-Host "Updating " $workbookpath  
           # Open the Excel file  
           $excelworkbook = $excel.workbooks.Open($workbookpath)  
           $connections = $excelworkbook.Connections  
           foreach ($c in $connections)  
           {  
                if ($c.DataFeedConnection -ne $null)  
                {  
                     $conn = $c.DataFeedConnection.Connection  
                     # Use regex to search and replace part of connection string  
                     $new = $conn -replace 'ProjectName eq ''(.*)''', "ProjectName eq '$title'"  
                     $c.DataFeedConnection.Connection = $new  
                     Write-Host "Connection replaced."  
                }  
           }  
           # This will Refresh All the pivot tables data.  
           $excelworkbook.RefreshAll()  
           # The following script lines will Save the file.  
           $excelworkbook.Save()  
           $excelworkbook.Close()  
      }  
 }  
 $excel.quit()  

2 comments:

sve7 said...

Hi,
Thank you for this code, i found it to be pretty useful. I modified the script to replace a text connection string, which works but the new csv is then imported without taking into account the comma delimiter.I tried using the bold section below but i get an exeption, given below the script.

f ($c.TextConnection -ne $null)
{
$conn = $c.TextConnection.Connection

# Use regex to search and replace part of connection string
$new = $conn -replace "VERSIONS.csv", "VERSIONS2.csv"


$c.TextConnection.Connection = $new

$c.TextConnection.TextFileCommaDelimiter = $True



Exception from HRESULT: 0x800A03EC
At line:35 char:22
+ $c.TextConnection.TextFileCommaDelimiter = $True
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Do you have an idea how can this be tackeld ?

Thank you!

sheela rajesh said...

Awesome informations that you have shared for us.I eagerly waiting for more updates in future.
Hadoop Training in Chennai
Big data training in chennai
Big Data Course in Chennai
JAVA Training in Chennai
Python Training in Chennai
Selenium Training in Chennai
Hadoop training in chennai
Big data training in chennai
hadoop training in Velachery

Related Posts Plugin for WordPress, Blogger...