VBA macro to update data connection. Connection field not accepting a variable

Question

I'm writing a macro that asks for user input to update the catalog and server fields within the connection string of an excel data connection.

Sub Connection_String()

Dim ConnectionString As String
Dim Catalog As String
Dim Server As String

Catalog = InputBox("Catalog", MsgTitle, "")
Server = InputBox("Server", MsgTitle, "")
ConnectionString = "Provider=MSOLAP;Persist Security Info=True;Initial Catalog=" & Catalog & _
";Data Source=" & Server & ";Auto Synch Period=10000;Cache Ratio=1;MDX Compatibility=1;MDX Unique Name Style=2;" & _
"Safety Options=2;MDX Missing Member Mode=Error;Disable Prefetch Facts=True"

With ActiveWorkbook.Connections("Example Connection").OLEDBConnection
    .CommandText = "Example Command Text"
    .CommandType = xlCmdCube
    .Connection = ConnectionString
    .RefreshOnFileOpen = False
    .SavePassword = False
    .SourceConnectionFile = ""
    .MaxDrillthroughRecords = 1000
    .ServerCredentialsMethod = xlCredentialsMethodIntegrated
    .AlwaysUseConnectionFile = False
    .RetrieveInOfficeUILang = True
End With

The problem I'm running into is that I get a "Runtime Error 1004 Application Defined or Object Defined Error" referencing the .Connection = ConnectionString line within the with statement. If I set .Connection to the full connection string itself rather than the ConnectionString variable it works.

Answer 1

Connection string and changing it seems to be a bit problematic with VBA. Check these posts:

Microsoft Excel Data Connections - Alter Connection String through VBA

Excel macro to change external data query connections - e.g. point from one database to another

Thus, probably it is better idea to follow the MS examples for connection: https://technet.microsoft.com/de-de/library/ee692882.aspx