Is it possoble to edit Excel.Workbook.Connections

Tagged: ,

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of notarat notarat 3 years, 8 months ago.

  • Author
    Posts
  • #10986
    Profile photo of Stephen Yeadon
    Stephen Yeadon
    Participant

    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
    
  • #10988
    Profile photo of notarat
    notarat
    Participant

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

You must be logged in to reply to this topic.