VBA for Excel 2010: Pivot Table Workbook.Connections

Question

I'm trying to retrieve the information stored in in ActiveWorkbook.Connections("1-RES Shopping Lists").OLEDBCConnections

                 .CommandText = Array("Recipes$")

                 .Connection = Array(Data Source=C:\Users\robertsutor\Desktop\1-RES Shopping Lists.xlsm)  ' Only this portion

How do I get these values stored in a variable and what should the data type be for the variable?

In this code I'm trying to retrieve this information and display it in a MsgBox (VBA doesn't like the code lines in bold; Object doesn't support property or method):

Sub ConnectionString()
Dim strConnShtName As String
Dim strConnPath
strConnShtName = ActiveWorkbook.Connections("1-RES Shopping Lists").OLEBConnetion.CommandText
strConnPath = ActiveWorkbook.Connections("1-RES Shopping Lists").OLEBConnetion.Connection = Array("Data Source")
MsgBox ("Sheet Name:  " & strConnShtName & "Path:  " & "strConnPath")

End Sub

Any help would be appreciated.  I'm stumped??

  Here is where the related VBA code sets in a recorded Excel Macro:
    With ActiveWorkbook.Connections("1-RES Shopping Lists").OLEDBConnection
        .BackgroundQuery = False
        .CommandText = Array("Recipes$")
        .CommandType = xlCmdTable
        .Connection = Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\robertsutor\Desktop\1-RES Shopping Lists.xlsm;Mode=Share De" _
        , _
        "ny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLE" _
        , _
        "DB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Passw" _
        , _
        "ord="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet " _
        , _
        "OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Va" _
        , "lidation=False")
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
        .ServerFillColor = False
        .ServerFontStyle = False
        .ServerNumberFormat = False
        .ServerTextColor = False
    End With
    With ActiveWorkbook.Connections("1-RES Shopping Lists")
        .Name = "1-RES Shopping Lists"
        .Description = "Added text"
    End With
End Sub

Answer 1

see if this helps

Dim strConnShtName As String
Dim strConnPath As String
Dim connType As String
Dim oCccODBC As ODBCConnection
Dim oCcOLEDB As OLEDBConnection
Dim strType As String
Dim oConn As Object

'Set oConn = ActiveWorkbook.Connections("myConn")

For Each oConn In ActiveWorkbook.Connections

connType = oConn.Type

Select Case connType
  Case xlConnectionTypeOLEDB
    strType = "OLEDB"
    Set oCcOLEDB = oConn.OLEDBConnection
    strConnShtName = oCcOLEDB.CommandText
    strConnPath = oCcOLEDB.Connection
    strConnPath = Right(strConnPath, Len(strConnPath) - InStr(1, strConnPath, "Data Source=") - 11)
    strConnPath = Left(strConnPath, InStr(1, strConnPath, ";") - 1)
  Case xlConnectionTypeODBC
    strType = "ODBC"
    Set oCccODBC = oConn.ODBCConnection
    strConnShtName = oCccODBC.CommandText
    strConnPath = oCccODBC.Connection
    strConnPath = Right(strConnPath, Len(strConnPath) - InStr(1, strConnPath, "DBQ=") - 3)
    strConnPath = Left(strConnPath, InStr(1, strConnPath, ";") - 1)
  Case xlConnectionTypeTEXT
    strType = "Text"
    strConnShtName = ""
    strConnPath = ""
    
  Case xlConnectionTypeWEB
    strType = "Web"
    strConnShtName = ""
    strConnPath = ""
    
  Case xlConnectionTypeXMLMAP
    strType = "XML"
    strConnShtName = ""
    strConnPath = ""

---

Ed Ferrero
www.edferrero.com

Answer 2

Ed,

The code worked great.  Thanks so much.  Well I know which type of data connection a pivot table uses and now with your help I know all the connections in the workbook.  Now I'm trying to figure out how to find out which specific connection is associated with which pivot. 

Please let me know if you have any ideas on that.

Thanks again you saved me a ton of time.

...bob