How to migrate Excel pivot 2000 to 2012?

[Kerbeross]

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:

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.

 

[Kerbeross]

Is anybody able to help? Pleaseee...