1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

    ActiveWorkbook.Sheets("MDX Query").Select
    strMDX_Query = Range("A1").Value

    ActiveWorkbook.Sheets("Compare").Select

'' Period settings
    strMDX_Period_1 = Range("B7").Value
    strMDX_Period_2 = Range("E7").Value

'' Server settings
    strSSAS_Server_1 = Range("B5").Value
    strSSAS_Database_1 = Range("B6").Value
    strSSAS_Server_2 = Range("E5").Value
    strSSAS_Database_2 = Range("E6").Value

With ActiveWorkbook.Connections("SSAS_Environment_1").OLEDBConnection
        .Connection = "OLEDB;Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" + strSSAS_Database_1 + ";Data Source=" + strSSAS_Server_1 + ";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error"
        .CommandText = Replace(strMDX_Query, strMDX_Filter, strMDX_Period_1)
End With

With ActiveWorkbook.Connections("SSAS_Environment_2").OLEDBConnection
        .Connection = "OLEDB;Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" + strSSAS_Database_2 + ";Data Source=" + strSSAS_Server_2 + ";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error"
        .CommandText = Replace(strMDX_Query, strMDX_Filter, strMDX_Period_2)
End With

    ActiveWorkbook.Connections("SSAS_Environment_1").Refresh
    ActiveWorkbook.Connections("SSAS_Environment_2").Refresh

    MsgBox "Compare has been finished successfully.", vbInformation, "SSAS Measure Compare"