CODEWITHSUNDEEP
excelvbamdxolap
Erik Rasmussen
Erik Rasmussen

Reputation: 331

VBA Reptitive MDX query to Analysis Services

I have a report where I run the same MDX query each week, and paste the results in a worksheet to then generate a report. I know how to connect to the OLAP cube in excel, but I don't like manipulating the pivot table to match my query.

I'd like to use VBA to connect to the cube and and return the results of the specific query in a worksheet as a values paste, or table. I can save the MDX query in a seperate sheet. Would this be possible? I'm new to VBA, so I'm not sure where to get started. Any example code would be great.

Upvotes: 2

Views: 3787

Answers (1)

whytheq
whytheq

Reputation: 35557

I think this previous question is pretty much what you need:
Any MDX query within Excel vba?

I adapted the code in that previous question-answer just wrote the following it seems to return a number ok:

Sub getFromCube()

Dim strConn As String
strConn = _
    "Provider=MSOLAP.6;" & _
    "Data Source=imxxxxxx;" & _                                     '<<<name of your server here
    "Initial Catalog=AdventureWorksDW2012Multidimensional-EE;" & _  '<<<name of your Adv Wrks db here
    "Integrated Security=SSPI"

Dim pubConn As ADODB.Connection
Set pubConn = New ADODB.Connection
pubConn.CommandTimeout = 0
pubConn.Open strConn

Dim cs As ADOMD.Cellset
Set cs = New ADOMD.Cellset

Dim myMdx As String
myMdx = _
  " SELECT" & _
  "  NON EMPTY" & _
  "    [Customer].[Customer Geography].[State-Province].&[AB]&[CA] ON 0," & _
  "  NON EMPTY" & _
  "    [Measures].[Internet Sales Amount] ON 1" & _
  " FROM [Adventure Works];"

With cs
    .Open myMdx, pubConn
    ActiveSheet.Range("A1") = cs(0, 0)
    .Close
End With


End Sub

If you look in that previous question you will see that getting hold of the cellset of data is quite easy but then pasting it into the worksheet is not so trivial. For simplicity and to quickly check things are working as expected I just used this ActiveSheet.Range("A1") = cs(0, 0). I think you will need to loop through the cellset.

note - you need to add two references for the above to work:

  1. Microsoft ActiveX Data Objects 6.1 Library
  2. Microsoft ActiveX Data Objects (Multi-dimensional) 6.0 Library

(or the latest version of each that you have available)

Upvotes: 2

Related Questions