4

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.

petezurich
  • 9,280
  • 9
  • 43
  • 57
MattR
  • 4,887
  • 9
  • 40
  • 67
  • 1
    Complete shot in the dark here, but is it possible that `commandtext` is a property of `connections`? Perhaps `.connections.oledbconnection.commandtext`? – dwirony Nov 02 '18 at 15:56

1 Answers1

4

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()
drec4s
  • 7,946
  • 8
  • 33
  • 54
  • this is amazing. Do you have any links to documentation on the OLEDBConnection instance? I want to know *how* you solved this, as I'm sure I'll have future needs similar to this question – MattR Nov 02 '18 at 16:20
  • The documentation that exists is the one from [MSDN](https://learn.microsoft.com/en-us/office/vba/api/overview/excel/object-model). You can find all the references there. – drec4s Nov 02 '18 at 17:13
  • Your solutions work well with the xlsx files, but I am unable to open the xlsm file, the code just spins indenfinitely. Is there a way to see the connections in the xlsm? Thanks. – Aleksandar Ciric Oct 19 '22 at 14:13