Hi All,
I was new here and was stuck since 2 weeks ago...
Our company is in the midst of upgrading the SQL server 2000 to SQL Server 2012. This means we also upgrade Analysis Services 2000 to Analysis Services 2012 (AS2012).
We had hundreds of Excel reports that need to point to new AS2012. This excel uses PivotTable that connected to the AS2000.
All this excel is working fine currently (when pointing to AS2000) but when I re-point to AS2012, several errors occured.
In Excel VBA, I already tick the Microsoft ActiveX Data Library 2.8, change the connection string. Below is the entire code to refresh the Macro:
When I run this, the error will occured:
unable to get pivotfields property of the pivottable class
and it will point to the code in red above.
I'm almost give up finding the solution and I need to solve this ASAP.
Also there around 500 reports need to be converted by me alone.
Please help me.
I was new here and was stuck since 2 weeks ago...
Our company is in the midst of upgrading the SQL server 2000 to SQL Server 2012. This means we also upgrade Analysis Services 2000 to Analysis Services 2012 (AS2012).
We had hundreds of Excel reports that need to point to new AS2012. This excel uses PivotTable that connected to the AS2000.
All this excel is working fine currently (when pointing to AS2000) but when I re-point to AS2012, several errors occured.
In Excel VBA, I already tick the Microsoft ActiveX Data Library 2.8, change the connection string. Below is the entire code to refresh the Macro:
Option Explicit
Const cnnStr = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=DW_EDM_BPM;Integrated
Security=SSPI;"
Public cnn As New ADODB.Connection
Sub dbConnect()
If cnn.State = adStateClosed Then
cnn.CommandTimeout = 500
cnn.Open cnnStr
End If
End Sub
Public Sub refreshDate()
sqlShowData "SELECT zsystem.TimeD_Year,zsystem.SEM_ID,zsystem.Quarter_ID,zsystem.TimeD_Mnth,zsystem.TimeM_ID,zsystem.TimeD_ID,[Day]=timeD_DD,Mnth_NM FROM DW_EDM_BPM.dbo.zsystem zsystem", "MISDEP_033", 2, 22, True
End Sub
Sub sheetClear(sSheet As Object, Optional startRow As Long = 1, Optional startColumn As Long = 1)
Dim i As Long
i = startRow
While Len(sSheet.Cells(i, startColumn)) > 0
sSheet.Cells(i, 1).EntireRow.Clear
i = i + 1
Wend
End Sub
Sub Create_Data()
Dim a As String
Call refreshDate
Sheet1.Activate
'Get date from zsystem table in Column V-Z in Sheets Working
a = "[Date].[All Date]." & "[" & ActiveSheet.Cells(2, 22) & "]" & "." & _
"[" & ActiveSheet.Cells(2, 26) & "]"
'Refresh Cube Reporting Date to current Date in Working
ActiveSheet.PivotTables("PivotTable1").PivotFields("[Date].[Year Nm]").CurrentPageName = a
ActiveSheet.PivotTables("PivotTable2").PivotFields("[Date].[Year Nm]").CurrentPageName = a
ActiveSheet.PivotTables("PivotTable3").PivotFields("[Date].[Year Nm]").CurrentPageName = a
ActiveSheet.PivotTables("PivotTable4").PivotFields("[Date].[Year Nm]").CurrentPageName = a
ActiveSheet.PivotTables("PivotTable5").PivotFields("[Date].[Year Nm]").CurrentPageName = a
ActiveSheet.PivotTables("PivotTable6").PivotFields("[Date].[Year Nm]").CurrentPageName = a
ActiveSheet.PivotTables("PivotTable7").PivotFields("[Date].[Year Nm]").CurrentPageName = a
ActiveSheet.PivotTables("PivotTable8").PivotFields("[Date].[Year Nm]").CurrentPageName = a
ActiveSheet.PivotTables("PivotTable9").PivotFields("[Date].[Year Nm]").CurrentPageName = a
ActiveSheet.PivotTables("PivotTable10").PivotFields("[Date].[Year Nm]").CurrentPageName = a
End Sub
Function dtIsNumeric(dtType As DataTypeEnum) As Boolean
If dtType = adBigInt Or dtType = adCurrency Or dtType = adDecimal Or dtType = adDouble Or _
dtType = adInteger Or dtType = adNumeric Or dtType = adSingle Or dtType = adSmallInt Or _
dtType = adTinyInt Or dtType = adUnsignedBigInt Or dtType = adUnsignedInt Or dtType = adUnsignedSmallInt Or _
dtType = adUnsignedTinyInt Or dtType = adVarNumeric Then
dtIsNumeric = True
End If
End Function
Sub sqlShowData(sSQL As String, displaySheet, Optional startRow As Long = 1, Optional startColumn As Long = 1, Optional multiSheetEnable As Boolean)
Dim rs As New ADODB.Recordset
Dim i As Double, j As Integer
Dim sht As Object
dbConnect
If IsObject(displaySheet) Then
Set sht = displaySheet
Else
Set sht = Sheets(displaySheet)
End If
sheetClear sht, startRow, startColumn
rs.Open sSQL, cnn
While Not rs.EOF And (i < 65535 Or multiSheetEnable)
If i >= 65535 Then
Set sht = Sheets.Add(, sht)
i = 0
End If
For j = 0 To rs.Fields.Count - 1
If Not dtIsNumeric(rs.Fields(j).Type) Then
If IsNumeric("" & rs.Fields(j).Value) And InStr(1, "" & rs.Fields(j).Value, ".") = 0 Then
sht.Cells(startRow + i, startColumn + j).Value = "'" & rs.Fields(j).Value
ElseIf Len("" & rs.Fields(j).Value) > 0 Then
sht.Cells(startRow + i, startColumn + j).Value = rs.Fields(j).Value
Else
sht.Cells(startRow + i, startColumn + j).Value = ""
End If
ElseIf Len("" & rs.Fields(j).Value) > 0 Then
sht.Cells(startRow + i, startColumn + j).Value = rs.Fields(j).Value
Else
sht.Cells(startRow + i, startColumn + j).Value = ""
End If
Next
i = i + 1
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
End Sub
When I run this, the error will occured:
unable to get pivotfields property of the pivottable class
and it will point to the code in red above.
I'm almost give up finding the solution and I need to solve this ASAP.
Also there around 500 reports need to be converted by me alone.
Please help me.