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