Excel vba 中的任何 MDX 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11188747/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Any MDX query within Excel vba?
提问by matandked
is there any way to execute MDX query within Excel VBA?
有没有办法在 Excel VBA 中执行 MDX 查询？
I thought that it can be done through ADO, similarly as in SQL case (yes, I'm aware that SQL is different than MDX - issue which was mentioned many times on Stackoverflow).
Unfortunately I can't find any examples.
我认为它可以通过ADO完成，与 SQL 情况类似（是的，我知道 SQL 与 MDX 不同 - Stackoverflow 上多次提到的问题）。
不幸的是，我找不到任何例子。
- Some told about using external tools to accomplish this task, but I don't want to pay for them.
- Some give an examples in XMLA, but I want to execute simple MDX query instead
- 有些人谈到使用外部工具来完成这项任务，但我不想为它们付费。
- 有些人在 XMLA 中给出了一个例子，但我想改为执行简单的 MDX 查询
.
.
回答by Terry Bochaton
We have the following generic function that's called in VBA that based on an input MDX string, writes the data to excel. The spreadsheet does require a reference to ADO and ADOMD
我们在 VBA 中调用了以下通用函数，该函数基于输入的 MDX 字符串，将数据写入 excel。电子表格确实需要对 ADO 和 ADOMD 的引用
Public Sub DisplayMDX(ipCell, ipMDX, ipExclHeadings)
Dim sQry As String
Dim sConnection As String
Dim rs As ADOMD.Cellset
Dim sServer, sDB, ts As String
Dim hyper As Hyperlink
Dim i, j, k, h, rowStart, colStart, dimCount As Integer
Dim sURLLink, sCustCaption, sCustLink As String
Dim db As ADODB.Connection
'Open a new ADO connection
Set db = New ADODB.Connection
sConnection = "Provider=MSOLAP; Data Source=DW3; Initial Catalog=FDMDW1; Integrated Security=SSPI"
db.CommandTimeout = 0
db.Open sConnection
'Open a CellSet to store the results of the query.
Set rs = New Cellset
'Tidy the query of an erroneous spaces
sQry = Trim(ipMDX)
'Open the query that was constructed above
Application.StatusBar = "Getting OLAP Data"
With rs
.Open sQry, db
End With
With ActiveSheet
'Goto cell specified
Range(ipCell).Select
'Find the starting point
rowStart = ActiveCell.Row
colStart = ActiveCell.Column
For j = 0 To rs.Axes(1).Positions.Count - 1
If Not ipExclHeadings Then
dimCount = rs.Axes(1).DimensionCount
For h = 0 To rs.Axes(1).DimensionCount - 1
Cells(rowStart + j, colStart + h) = rs.Axes(1).Positions(j).Members(h).Caption
Next
End If
For k = 0 To rs.Axes(0).Positions.Count - 1
If Not (k = 1) Then
If rs(k, j) <> "" Then
Cells(rowStart + j, colStart + dimCount + k).Value = rs(k, j)
Else
Cells(rowStart + j, colStart + dimCount + k).ClearContents
End If
End If
Application.StatusBar = rs(k, j)
Next
Next
End With
rs.Close
Application.StatusBar = "Done"
Exit Sub
errMsg:
MsgBox Err.Description, vbOKOnly + vbCritical, "Error #" & Err.Number
End Sub
