title: How can I use VBA to add a Connection (to an External Data Source) in Excel and Save it to that Excel spreadsheet's list of Connections
body: <p>I can use VBA to create a new ADODB.Connection and associated ADODB.Command and ADOBD.Parameter and then create a PivotCache and a PivotTable</p>
  
  <pre><code>Sub CreatePivotTable()
      'Declare variables
      Dim objMyConn As ADODB.Connection
      Dim objMyCmd As ADODB.Command
      Dim objMyParam As ADODB.Parameter
      Dim objMyRecordset As ADODB.Recordset
  
      Set objMyConn = New ADODB.Connection
      Set objMyCmd = New ADODB.Command
      Set objMyRecordset = New ADODB.Recordset
  
      'Open Connection'
      objMyConn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=myMIS;Data Source=localhost;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WKSTN101;Use Encryption for Data=False;Tag with column collation when possible=False"
      objMyConn.Open
  
      'Set and Excecute SQL Command'
      Set objMyCmd.ActiveConnection = objMyConn
      objMyCmd.CommandText = "select a.col1, a.col2, b.col3, b.col4" &amp; _
                             "from TableA a, TableB b " &amp; _
                             "where a.col3=b.col5 " &amp; _
                             "and a.col1=?"
      objMyCmd.CommandType = adCmdText
  
      Set objMyParam = objMyCmd.CreateParameter("COLUMN1", adChar, adParamInput, 20, Range("AnotherSheet!A3").Value)
  
      objMyCmd.Parameters.Append objMyParam
  
      'Open Recordset'
      Set objMyRecordset.Source = objMyCmd
      objMyRecordset.Open
  
      'Create a PivotTable cache and report.
      Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
      Set objPivotCache.Recordset = objMyRecordset
      objPivotCache.CreatePivotTable TableDestination:=Range("A11"), TableName:="PivotTable1"
  
      With ActiveSheet.PivotTables("PivotTable1")
          .SmallGrid = False
          With .PivotFields("Col3")
              .Orientation = xlRowField
              .Position = 1
          End With
          With .PivotFields("Col4")
              .Orientation = xlRowField
              .Position = 1
          End With
          With .PivotFields("Col1")
              .Orientation = xlColumnField
              .Position = 1
          End With
          With .PivotFields("Col2")
              .Orientation = xlDataField
              .Position = 1
          End With
      End With
  </code></pre>
  
  <p>... BUT after I have run this macro, if I check the Connection properties in the Connections list (in the Data tab of the Ribbon) they appear disabled (grayed-out) and the SQL Command doesn't appear there (limiting further changes through VBA only).</p>
  
  <p>How can I create these same objects but have them integrate with the Excel UI so future users don't need to use VBA? Any ideas?</p>