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

Question

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

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" & _
                           "from TableA a, TableB b " & _
                           "where a.col3=b.col5 " & _
                           "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

... 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).

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?

Answer 1

You can use a macro recorder to generate a VBA code that will add a connection to your excel instance.
I have added the code at the end of this answer, however you can generate your own if you follow the below steps:
1) Start a macro recorder
2) On the ribbon, click on Data tab. Click on the Connections and then choose the Add button like shown in the below screenshot

3) On the next screen, choose your existing DB connection, and follow the steps on the next 2 or 3 screens to configure your connection.
4) Once your connection is established and appears in the connections list, click on the Properties button and on the next screen Export Connection File

5) Stop your macro recorder and open VBE (alt+F11) and edit the code in your Module1 6) Remove these lines from your macro code

.ServerFillColor = False
.ServerFontStyle = False
.ServerNumberFormat = False
.ServerTextColor = False

7) Save and close the file now

Note when you reopen the file and run the macro the connection should be added to your connections list

you can now add the connection from the exported file with this code

Workbooks("Book1").Connections.AddFromFile _
        "C:\Users\...\exported_file_name.odc"

or can run the recorded code and let the macro add it for you