Need to change connection string of data connection to power BI in excel VBA

Question

I have an excel workbook that is linked to an open Power BI file. The connection name is "mtbsql6??v-dev_mssqlinst01 PRS Model" and the connection string is

"Provider=MSOLAP.8;Integrated Security=SSPI;Persist Security Info=True;User ID="";Initial Catalog=2a527000-c71f-4b0d-8506-36d1799ec663;Data Source=localhost:6514?;Location=localhost:6514?;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2".

I need to update the local host number which I already have in a variable. Is it an OLEDB connection? Have tried some below code but it's failing. Got this code from another answer on stack overflow. It is failing on the line marked as ##.

 Dim ConnectionString As String
    ConnectionString = UpdatedString (This is stored in a variable)
    UpdateQueryConnectionString ConnectionString


Sub UpdateQueryConnectionString(ConnectionString As String)

  Dim cn As WorkbookConnection
  Dim oledbCn As OLEDBConnection
  Set cn = ThisWorkbook.Connections("mtbsql6??v-dev_mssqlinst01 PRS Model")
  Set oledbCn = cn.OLEDBConnection
  oledbCn.Connection = ConnectionString ###

End Sub

Thanks for any help.

GeekGurl · Accepted Answer · 2020-04-22 12:00:37Z

1

You didn't mention what the error was... Might it be the quotes after User ID in your connection string?

UpdatedString = "Provider=MSOLAP.8;Integrated Security=SSPI;Persist Security Info=True;User ID=" & chr(34) & chr(34) & ";Initial Catalog=2a527000-c71f-4b0d-8506-36d1799ec663;Data Source=localhost:6514?;Location=localhost:6514?;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2"

answered Apr 22, 2020 at 12:00

GeekGurl

213 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Repairer Over a year ago

The error coming up is Run Time Error 1004, application defined or object defined error and the line oledbCn.Connection = ConnectionString shows the error.

Repairer Over a year ago

Tried the Connection string with User ID=" & chr(34) & chr(34) & " but same error on same line

Repairer Over a year ago

I feel that this connection is not an OLEDB connection..I wanted to try an approach like For Each conn In ActiveWorkbook.Connections With conn---------and then I want to refer the connection string as conn.something.connection string = UpdatedConnectionString, just this reference I can't figure out. Is it possible?

Collectives™ on Stack Overflow

Need to change connection string of data connection to power BI in excel VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related