ishakro1967
New Member
- Joined
- Jul 9, 2015
- Messages
- 1
Is it possible to modify CommandText on existing ActiveWorkbook connection. It is OLAP connection to MSAS for PowerPivot table.
When I try to modify CommandText with new MDX query it throws an error "Application-Defined or object-defined error" run time error 1104
please see my code below
Dim wkbook As Workbook
Dim conn As OLEDBConnection
Dim sMDX As String
sMDX = "SELECT NON EMPTY { [Measures].[EARNED], [Measures].[TOTAL INCURRED], [Measures].[LossRatio] } ON COLUMNS, NON EMPTY { ([PolEffectiveData].[Year].[Year].ALLMEMBERS * [MART TIME DIM].[Date ID].[Date ID].ALLMEMBERS * [MART TIME DIM].[Year].[Year].ALLMEMBERS * [PRODUCT SEGMENTS].[PROD ID].[PROD ID].ALLMEMBERS * [PRODUCT SEGMENTS].[MAJOR PROD].[MAJOR PROD].ALLMEMBERS * [PRODUCT SEGMENTS].[MINOR PROD].[MINOR PROD].ALLMEMBERS * [Rank].[Company Rank].[Company Rank].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [MART TIME DIM].[Month].&[6] } ) ON COLUMNS FROM [YearToYear_Compare_Month_June]) WHERE ( [MART TIME DIM].[Month].&[6] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
Set wkbook = ActiveWorkbook
Set conn = wkbook.Connections(1).OLEDBConnection
conn.CommandText = sMDX
conn.Connection = "OLEDB;Provider=MSOLAP;Data Source=eldbi01;Initial Catalog=MonthlySnapshotPolicy;Integrated Security=SSPI;Persist Security Info=false;DbpropMsmdFlattened2=true"
Thank you
When I try to modify CommandText with new MDX query it throws an error "Application-Defined or object-defined error" run time error 1104
please see my code below
Dim wkbook As Workbook
Dim conn As OLEDBConnection
Dim sMDX As String
sMDX = "SELECT NON EMPTY { [Measures].[EARNED], [Measures].[TOTAL INCURRED], [Measures].[LossRatio] } ON COLUMNS, NON EMPTY { ([PolEffectiveData].[Year].[Year].ALLMEMBERS * [MART TIME DIM].[Date ID].[Date ID].ALLMEMBERS * [MART TIME DIM].[Year].[Year].ALLMEMBERS * [PRODUCT SEGMENTS].[PROD ID].[PROD ID].ALLMEMBERS * [PRODUCT SEGMENTS].[MAJOR PROD].[MAJOR PROD].ALLMEMBERS * [PRODUCT SEGMENTS].[MINOR PROD].[MINOR PROD].ALLMEMBERS * [Rank].[Company Rank].[Company Rank].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [MART TIME DIM].[Month].&[6] } ) ON COLUMNS FROM [YearToYear_Compare_Month_June]) WHERE ( [MART TIME DIM].[Month].&[6] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
Set wkbook = ActiveWorkbook
Set conn = wkbook.Connections(1).OLEDBConnection
conn.CommandText = sMDX
conn.Connection = "OLEDB;Provider=MSOLAP;Data Source=eldbi01;Initial Catalog=MonthlySnapshotPolicy;Integrated Security=SSPI;Persist Security Info=false;DbpropMsmdFlattened2=true"
Thank you