VBA Reptitive MDX查询到Analysis Services [英] VBA Reptitive MDX query to Analysis Services

问题描述

我有一个报告，我每周运行相同的MDX查询，并将结果粘贴到工作表中，然后生成报告。我知道如何连接到excel中的OLAP多维数据集，但是我不喜欢操纵数据透视表来匹配我的查询。

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.

我想使用VBA连接到多维数据集，并将工作表中特定查询的结果作为值粘贴或表返回。我可以在单独的表中保存MDX查询。这可能吗我是VBA的新手，所以我不知道从哪里开始。任何示例代码都很棒。

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.

推荐答案

我认为以前的问题几乎是你需要的：

Excel vba中的任何MDX查询

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

我在以前的问题回答中修改了代码，刚刚写了以下内容，似乎返回一个数字OK：

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

如果您查看上一个问题您将看到获取数据单元格是非常容易的，但是将其粘贴到工作表中并不是那么简单。为了简单起见，快速检查事情正如预期的那样工作，我刚刚使用这个 ActiveSheet.Range（A1）= cs（0，0）。我想你将需要循环通过单元格。

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数据对象6.1库


2. Microsoft ActiveX数据对象（多维）6.0库

（或您可用的每个最新版本）

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

这篇关于VBA Reptitive MDX查询到Analysis Services的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！