CODEWITHSUNDEEP
excelpowershell
user812323
user812323

Reputation: 5

Use PowerShell to get Excel Data Connection's SQL command text

I have a bunch of Excel files each of which has a number of Workbook Connections. Each of these workbook connections has a properties with a Definition, which contains a "Connection String" and also a "Command text"

I would like to retrieve the connection string and command text values through PowerShell but cannot see the function to do this

I have got as far as the following snippet, any advice appreciated...

   $excelObj = New-Object -ComObject Excel.Application
   $excelObj.Visible = $false
   $workbook = $excelObj.Workbooks.Open($xlsxLocation)
   foreach ($connect in $workbook.Connections)
    {
     Write-Host $connect.Name
     # This is where I need the connection string and the command text, for this connection.
    }

enter image description here

Upvotes: 0

Views: 2320

Answers (1)

Chuck
Chuck

Reputation: 26

I ran a search for "vba connections command text" which returned Extracting Excel Data Connection Command Text. Then I was able to adapt your code to:

$xlsxLocation="C:\Temp\MyFile.xlsx"
$excelObj = New-Object -ComObject Excel.Application
$excelObj.Visible = $False
$excelObj.DisplayAlerts = $False
$workbook = $excelObj.Workbooks.Open($xlsxLocation)

foreach ($worksheet in $workbook.Worksheets){
    foreach ($listobject in $worksheet.ListObjects) {
        $commandtext = $listobject.QueryTable.CommandText
        if (-not ([string]::IsNullOrWhiteSpace($commandtext))) {
            Write-Host $commandtext
        }
    }
}

$workbook.Close($False) # closed do not save
$excelObj.DisplayAlerts = $True
$excelObj.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelObj) | Out-Null
Remove-Variable excelObj | Out-Null

The last two lines of code will dispose the Excel resourse when you terminate the script.

Upvotes: 1

Related Questions