TM1OLAP Provider can not be found by Excel

[jimshen]

Hi,

Years ago, I could write MDX query from VBA for Excel report from TM1.

When I now try to replicate the codes for current client, it shrows out the message saying "Provider cannot be found".

I checked the connection inside TM1 explorer in TI pad, the connection works. So the TM1 OLEDB provider is installed correctly as TI can find it.

In VBA, Microsoft ADO Ext for DDL and Security has been referenced at \Program files\....\ado\msado.dll

I wonder if it is this dll file has not been updated during TM1 installation.

How can we handle this so let VBA to find out TM1OLAP provider, or some work around can be used?

The codes I used briefly are:

Dim db As ADODB.Connection
Set db = New ADODB.Connection
sConnection = "DATA SOURCE=" & sServer & ";PROVIDER=TM1OLAP"
db.Open sConnection, sUser, sPass

db.Close

Can somebody help me out?

Thanks in advance

Jim

[rmackenzie]

You need to have the TM1 OLEDB for OLAP (ODBO) provider installed on the client. Whether this is installed as part of the 'standard' Perspectives install differs between versions and definitely you need to tick that box as part of a custom install in some cases.

So the TM1 OLEDB provider is installed correctly that TI can find it.

So the provider is probably correctly installed on the TM1 server.

In VBA, Microsoft ADO Ext for DDL and Security has been referenced at \Program files\....\ado\msado.dll

You need that too, but in addition to the TM1 ODBO provider.