PDA

View Full Version : Create OLAP Connection in Excel 2010 via VBA Code under the Button



Saadation
08-24-2016, 04:03 AM
Hello Guys,
I need your urgent support to fix this problem, I created Olap Macro saved the connection as below code in VBA, but I can't run this Macro on my PC and another PC the flowing error message stopped me "Run Time Error 9 subscript out of range ".
thanks

Sub OLAP_PO1()
'
' OLAP_PO1 Macro
'
' Keyboard Shortcut: Ctrl+r
'
With ActiveWorkbook.Connections( _
"alg-app.salesbuzz-eg.com Pepsico_Reports PO1 Sales Target").OLEDBConnection
.CommandText = Array("PO1 Sales Target")
.CommandType = xlCmdCube
.Connection = Array( _
"OLEDB;Provider=MSOLAP.4;Persist Security Info=True;User ID=MMMM;Password=MMMM;Data Source=.;Initial Catal" _
, "og=Pepsico_Reports")
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.MaxDrillthroughRecords = 1000
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
.RetrieveInOfficeUILang = True
.ServerFillColor = True
.ServerFontStyle = True
.ServerNumberFormat = True
.ServerTextColor = True
End With
With ActiveWorkbook.Connections( _
"alg-app.salesbuzz-eg.com Pepsico_Reports PO1 Sales Target")
.Name = ". Pepsico_Reports PO1 Sales Target"
.Description = ""
End With
Workbooks("Book1").Connections.Add _
"alg-app.salesbuzz-eg.com Pepsico_Reports PO1 Sales Target", "", Array( _
"OLEDB;Provider=MSOLAP.4;Persist Security Info=True;User ID=MMMM;Password=MMMM;Data Source=.;Initial Catal" _
, "og=Pepsico_Reports"), Array("PO1 Sales Target"), 1
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections( _
"alg-app.salesbuzz-eg.com Pepsico_Reports PO1 Sales Target"), Version:= _
xlPivotTableVersion14).CreatePivotTable TableDestination:="Sheet1!R1C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14
Cells(1, 1).Select
End Sub