1

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.

41686d6564 stands w. Palestine
  • 19,168
  • 12
  • 41
  • 79
randyvelour
  • 11
  • 2

1 Answers1

0

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

Community
  • 1
  • 1
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 1
    Thank you for the links. I had looked at those previously but must have missed one of the solutions. The solution as described in one of the links is to append `OLEDB` to the connection string stored in the variable and it works for my purposes. Thanks again – randyvelour Jan 18 '17 at 20:29