How to List CommandText from Connections

[mgoebel97]

I have about 30 connections connected to different excel sheets within 1 workbook. I want to know how to get the command text for each connection using VBA code.

This is what I have thus far:
Function Connections()


Dim conn As WorkbookConnection
Dim celltxt As String


For Each conn In ActiveWorkbook.Connections
Range("A" & ActiveCell.Row) = conn.Name
Range("B" & ActiveCell.Row) = ActiveWorkbook.Connections(conn).ODBCConnection.CommandText
Selection.Offset(1, 1).Select


Next conn


End Function

The conn.name works great and I get the connection name but the command text is not working. I keep getting an error message. I dont want to go through each connection to find out what table it is connecting to in Access. I want to see if there is code within VBA to do it for me.

 

[ranman256]

try:
ActiveWorkbook.Connections(conn).ConnectionString

 

[mgoebel97]

try:
ActiveWorkbook.Connections(conn).ConnectionString

Hi,

Thank you for the reply. I changed my code to this:
Function Connections()

Dim conn As WorkbookConnection
Dim celltxt As String

For Each conn In ActiveWorkbook.Connections
Range("A" & ActiveCell.Row) = conn.Name
Range("B" & ActiveCell.Row) = ActiveWorkbook.Connections(conn).ConnectionString
Selection.Offset(1, 1).Select

Next conn

End Function


but now I "Subscript out of range"

 

[ranman256]

i missed that...
either:
conn.connectionstring
or
ActiveWorkbook.Connections(1).ConnectionString

Note: Connections(N) ,the n is numeric, 1..N
but Conn is an object so:
conn.Connectionstring

 

[mgoebel97]

HI
Tried with activeworkbook and without. Same error message: Object does not support this property or method

Function Connections()
Dim conn As WorkbookConnection
Dim celltxt As String
For Each conn In ActiveWorkbook.Connections
Range("A" & ActiveCell.Row) = conn.Name
Range("B" & ActiveCell.Row) = conn.ConnectionString OR Range("B" & ActiveCell.Row) = activeworkbook.conn.ConnectionString
Selection.Offset(1, 1).Select
Next conn
End Function