CODEWITHSUNDEEP
pythonexcelwin32com
MattR
MattR

Reputation: 5136

Retrieve Excel Workbook Connection Properties

I am attempting to grab the "Command Text" from the Connection Property window in an Excel file using python. However, I cannot find the object that contains this information. In the below picture I would like to retrieve the highlighted EXEC sp_FooBar as a string:

enter image description here

I am able to retrieve the Connection names with:

import odbc
import win32com.client
file = r'PATH_TO_FILE'
xl = win32com.client.DispatchEx('Excel.Application')
wb = xl.workbooks.open(file)
for x in wb.connections:
    print(x)

But I'm not sure how to use the <COMObject <unknown>> object further to grab the command text. I'm thinking win32com may have something, but can't seem to crack the code.

Upvotes: 4

Views: 2262

Answers (1)

drec4s
drec4s

Reputation: 8077

You can get the CommandText property from a OLEDBConnectioninstance like this:

import odbc
import win32com.client

file = r'PATH_TO_FILE'
xl = win32com.client.DispatchEx('Excel.Application')
wb = xl.workbooks.open(file)
for x in wb.Connections:
    print(x.OLEDBConnection.CommandText)
xl.Application.Quit()

Upvotes: 4

Related Questions