Author Posts

October 22, 2013 at 5:47 am

Hi,

I have a number of excel reports that connect to a sql server data source. I am trying to automate the deployment of these excel files. This sticking block is that the connection the excel file uses differs between deployments.

Is it possible to edit the workbook connection string property using powershell?

The below code gives me access to the workbook connections but I don't seem to be able to edit the connections. I am not able to access the connection through the item method as i can't figure out the indexer. If I could I could maybe delete the connection but I don't seem to be able to edit it. I have used MSDN to get an ideal about the objects and on the whole they seem to be returning read only objects.

Is it possible, can anyone help ???

$Excel = New-Object -comobject Excel.Application

$ExcelWorkbook = $Excel.workbooks.open("C:\Users\syeadon\Desktop\CashFlowActual.xlsx") 


$Connections = $ExcelWorkbook.Connections

$Connections

October 22, 2013 at 7:54 am

If the data contained in $connections is a string, you can use a replace to replace the value with what you need.

For example, if $connection contains the following string
"c:\users\john.smith\My Documents\blah"
$connection2 = ($connection -replace "john.smith","Bob.Thomas")