Is it possoble to edit Excel.Workbook.Connections

Welcome Forums General PowerShell Q&A Is it possoble to edit Excel.Workbook.Connections

This topic contains 1 reply, has 2 voices, and was last updated by

5 years, 7 months ago.

  • Author
  • #10986

    Topics: 14
    Replies: 15
    Points: 21
    Rank: Member


    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 = $"C:\Users\syeadon\Desktop\CashFlowActual.xlsx") 
    $Connections = $ExcelWorkbook.Connections
  • #10988

    Topics: 8
    Replies: 33
    Points: 0
    Rank: Member

    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")

The topic ‘Is it possoble to edit Excel.Workbook.Connections’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort