none
How to alter Excel Connection Properties using Powershell? RRS feed

  • Question

  • Hello,

    I am new to Powershell and I am trying to determine how to alter the Connection Properties of an Excel spreadsheet.
    The background to this is that there is a template Excel spreadsheet in which everything remains the same except the database it connects to, which is changed for specific users. The intention is to automate copying the template and connection property changes using Powershell.

    The specific changes are

    • Description: Replace the text TESTDB with the relevant text
    • "Connection String": Change the Initial Catalog
    • "Command Text": Change all occurances of TESTDB with the relevant text

    A screenshot of the Connection Properties is shown in the image below.

    I found some Powershell code which display the details of the connections on an Excel Workbook but it does not show any of the details I am interested in; I have included the code and the output below.

    So what I am looking for is advice on how to acheive this.

    Many thanks,

    Garrie

     

     
    # Powershell Code
    

    $xlsx = "DMS.xlsx" $Excel = New-Object -comobject Excel.Application $ExcelWorkbook = $Excel.workbooks.open($xlsx) $conn = $ExcelWorkbook.Connections $conn | Out-File('conn.txt') $Excel.Workbooks.Close()

    # conn.txt file

    Application : Microsoft.Office.Interop.Excel.ApplicationClass Creator : 1480803660 Parent : System.__ComObject Name : DMS-Connection Description : DMS connection for TESTDB _Default : DMS-Connection Type : 1 OLEDBConnection : System.__ComObject ODBCConnection : Ranges : System.__ComObject ModelConnection : WorksheetDataConnection : RefreshWithRefreshAll : True TextConnection : DataFeedConnection : InModel : True ModelTables : System.__ComObject Application : Microsoft.Office.Interop.Excel.ApplicationClass Creator : 1480803660 Parent : System.__ComObject Name : ModelConnection_R_TESTDB_CTU_CRF_COUNT Description : Data Model _Default : ModelConnection_R_TESTDB_CTU_CRF_COUNT Type : 7 OLEDBConnection : System.__ComObject ODBCConnection : Ranges : ModelConnection : System.__ComObject WorksheetDataConnection : RefreshWithRefreshAll : True TextConnection : DataFeedConnection : InModel : True ModelTables : Application : Microsoft.Office.Interop.Excel.ApplicationClass Creator : 1480803660 Parent : System.__ComObject Name : ModelConnection_R_TESTDB_CTU_DATA Description : Data Model _Default : ModelConnection_R_TESTDB_CTU_DATA Type : 7 OLEDBConnection : System.__ComObject ODBCConnection : Ranges : ModelConnection : System.__ComObject WorksheetDataConnection : RefreshWithRefreshAll : True TextConnection : DataFeedConnection : InModel : True ModelTables : Application : Microsoft.Office.Interop.Excel.ApplicationClass Creator : 1480803660 Parent : System.__ComObject Name : ModelConnection_R_TESTDB_CTU_DATES Description : Data Model _Default : ModelConnection_R_TESTDB_CTU_DATES Type : 7 OLEDBConnection : System.__ComObject ODBCConnection : Ranges : ModelConnection : System.__ComObject WorksheetDataConnection : RefreshWithRefreshAll : True TextConnection : DataFeedConnection : InModel : True ModelTables : Application : Microsoft.Office.Interop.Excel.ApplicationClass Creator : 1480803660 Parent : System.__ComObject Name : ModelConnection_R_TESTDB_CTU_MISS_QUEST_DET Description : Data Model _Default : ModelConnection_R_TESTDB_CTU_MISS_QUEST_DET Type : 7 OLEDBConnection : System.__ComObject ODBCConnection : Ranges : ModelConnection : System.__ComObject WorksheetDataConnection : RefreshWithRefreshAll : True TextConnection : DataFeedConnection : InModel : True ModelTables : Application : Microsoft.Office.Interop.Excel.ApplicationClass Creator : 1480803660 Parent : System.__ComObject Name : ThisWorkbookDataModel Description : Data Model _Default : ThisWorkbookDataModel Type : 7 OLEDBConnection : ODBCConnection : Ranges : System.__ComObject ModelConnection : System.__ComObject WorksheetDataConnection : RefreshWithRefreshAll : True TextConnection : DataFeedConnection : InModel : True ModelTables :




    Garrie Powers IT Developer, CCTU, University College London


    • Edited by MoonBrain Wednesday, January 31, 2018 12:48 PM Image was missing
    Wednesday, January 31, 2018 12:12 PM

Answers

  • Hello MoonBrain,

    You could loop through the the Connections collection to get the WorkbookConnection which you want according to the WorkbookConnection.Name.

    Then you could get the description string form WorkbookConnection.Description,

    the Connection string from WorkbookConnection.OLEDBConnection.Connection,

    the CommandText string from WorkbookConnection.OLEDBConnection.CommandText.

    Once you get these string, you could replace specific string with new string you want and re-set it to them.

    Here is a piece of simply code. You could try to adjust it for your need.

    # Powershell Code
     $xlsx = "C:\Users\v-guaxu\Desktop\TestFolder\1.xlsx"
     $Excel = New-Object -comobject Excel.Application
     $ExcelWorkbook = $Excel.workbooks.open($xlsx) 
     $conn = $ExcelWorkbook.Connections
     $ReplaceDB='NewDB'
     $DebugPreference="Continue"
     foreach($con in $conn)
     {
      if($con.name -eq 'DMS-Connection')
       {  
        Write-Debug ($con.Description)
        Write-Debug ($con.OLEDBConnection.Connection)
        Write-Debug ($con.OLEDBConnection.CommandText)
        $con.Description=$con.Description -ireplace 'query',$ReplaceDB
        $flag=$con.OLEDBConnection.BackgroundQuery
        $con.OLEDBConnection.BackgroundQuery=$false
        $con.refresh()
        Write-Debug ('Updated Description:'+$con.Description)
        $con.OLEDBConnection.BackgroundQuery=$flag
       } 
     }

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    • Edited by Terry Xu - MSFT Thursday, February 1, 2018 6:49 AM
    • Marked as answer by MoonBrain Monday, February 5, 2018 2:34 PM
    • Unmarked as answer by MoonBrain Monday, February 5, 2018 2:35 PM
    • Marked as answer by MoonBrain Monday, February 5, 2018 2:35 PM
    • Unmarked as answer by MoonBrain Monday, February 5, 2018 2:35 PM
    • Marked as answer by MoonBrain Monday, February 5, 2018 2:35 PM
    Thursday, February 1, 2018 6:46 AM

All replies

  • Hello MoonBrain,

    You could loop through the the Connections collection to get the WorkbookConnection which you want according to the WorkbookConnection.Name.

    Then you could get the description string form WorkbookConnection.Description,

    the Connection string from WorkbookConnection.OLEDBConnection.Connection,

    the CommandText string from WorkbookConnection.OLEDBConnection.CommandText.

    Once you get these string, you could replace specific string with new string you want and re-set it to them.

    Here is a piece of simply code. You could try to adjust it for your need.

    # Powershell Code
     $xlsx = "C:\Users\v-guaxu\Desktop\TestFolder\1.xlsx"
     $Excel = New-Object -comobject Excel.Application
     $ExcelWorkbook = $Excel.workbooks.open($xlsx) 
     $conn = $ExcelWorkbook.Connections
     $ReplaceDB='NewDB'
     $DebugPreference="Continue"
     foreach($con in $conn)
     {
      if($con.name -eq 'DMS-Connection')
       {  
        Write-Debug ($con.Description)
        Write-Debug ($con.OLEDBConnection.Connection)
        Write-Debug ($con.OLEDBConnection.CommandText)
        $con.Description=$con.Description -ireplace 'query',$ReplaceDB
        $flag=$con.OLEDBConnection.BackgroundQuery
        $con.OLEDBConnection.BackgroundQuery=$false
        $con.refresh()
        Write-Debug ('Updated Description:'+$con.Description)
        $con.OLEDBConnection.BackgroundQuery=$flag
       } 
     }

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    • Edited by Terry Xu - MSFT Thursday, February 1, 2018 6:49 AM
    • Marked as answer by MoonBrain Monday, February 5, 2018 2:34 PM
    • Unmarked as answer by MoonBrain Monday, February 5, 2018 2:35 PM
    • Marked as answer by MoonBrain Monday, February 5, 2018 2:35 PM
    • Unmarked as answer by MoonBrain Monday, February 5, 2018 2:35 PM
    • Marked as answer by MoonBrain Monday, February 5, 2018 2:35 PM
    Thursday, February 1, 2018 6:46 AM
  • Hello Terry,
    Thank you for this, it was exactly what I was looking for and worked well.

    However, after further investigation, I realised that I needed to check all properties. If the property is of type String,  to see if they contained the text to be replaced and replace it. If the property is of type Object, search all the properties, etc.

    So for the moment I'll mark this as answered as you did answer my original question.

    Kind regards,
    Garrie

    Garrie Powers IT Developer, CCTU, University College London

    Monday, February 5, 2018 2:35 PM