excelvbadatabase-connectionolap-cube

Dynamically Build Cube Connection

I have a Pivot table in Excel which I can access and refresh by opening the Excel with the connection properties - enter image description here

I use the exact properties in this Excel's Macro module -

Sub UpdateQueryConnectionString()
  Dim cn As WorkbookConnection
  Dim oledbCn As OLEDBConnection
  Set cn = ThisWorkbook.Connections("http___xxxxx_olap_msmdpump.dll xxxx All xxxx")
  Set oledbCn = cn.OLEDBConnection
  oledbCn.CommandType = xlCmdCube
  oledbCn.CommandText = "All xxxx"
  oledbCn.Connection = "Provider = MSOLAP.8; Persist Security Info = True; User ID = xxxx; Password = xxxxx; Data Source = https://xxxxx/msmdpump.dll;Update Isolation Level=2;Initial Catalog=xxxxMAIN"
End Sub

An error "Run-time error '1004': Application-defined or object-defined error." occurs in oledbCn.Connection.

What can cause that and how to get more debugging info?

Thanks in advance for any help!

Solution

  • Found out the problem. "OLEDB;" is needed before "Provider = MSOLAP.8;".

    oledbCn.Connection = "OLEDB;Provider = MSOLAP.8; Persist Security Info = True; User ID = xxxx; Password = xxxxx; Data Source = https://xxxxx/msmdpump.dll;Update Isolation Level=2;Initial Catalog=xxxxMAIN"