Changing Pivot Table SQL Code

[Dreadknight Nasus]

I'm trying to change the SQL query in my pivot table, with a macro, but I'm getting a 1004 "Application-defined or object-defined error" with the following code:

Private Sub cmndRefresh_Click()


Dim strSQL1 As String


Dim strXSTreaty As String


'Setting Variable value based on XS Treaty Selection
strXSTreaty = cmbxXSTreaty.Value


'Writing SQL code for Pivot Table source data
strSQL1 = "SELECT Pet_Tool_Excel_Export.EVA_DATE, Pet_Tool_Excel_Export.Months, Pet_Tool_Excel_Export.ReserveCategory, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.AccountingName, Pet_Tool_Excel_Export.MCO, Pet_Tool_Excel_Export.PCO, Pet_Tool_Excel_Export.Program, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Company, Pet_Tool_Excel_Export.ASL, Pet_Tool_Excel_Export.ASL2, Pet_Tool_Excel_Export.Assumed, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Year, Pet_Tool_Excel_Export.Active, Pet_Tool_Excel_Export.Captive, Pet_Tool_Excel_Export.Allowance, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Branch, Pet_Tool_Excel_Export.ProgramGroup, Pet_Tool_Excel_Export.INDEX1, Pet_Tool_Excel_Export.MCOPCO,"
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Gross_Earned_Premium, Pet_Tool_Excel_Export.Net_Earned_Premium, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Gross_Paid_Loss_and_ALAE, Pet_Tool_Excel_Export.Gross_Case_Loss_and_ALAE, Pet_Tool_Excel_Export.Gross_Incurred_Loss_and_ALAE, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Excess_Paid_Loss_and_ALAE , Pet_Tool_Excel_Export.Excess_Case_Loss_and_ALAE, Pet_Tool_Excel_Export.Net_Paid_Loss_and_ALAE, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Net_Case_Loss_and_ALAE , Pet_Tool_Excel_Export.Net_Incurred_Loss_and_ALAE, Pet_Tool_Excel_Export.Closed_WithOut_Payment_Claims, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Closed_Expense_Only_Claims, Pet_Tool_Excel_Export.Closed_With_Payment_Claims, Pet_Tool_Excel_Export.Open_Claims, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Reported_Claims, Pet_Tool_Excel_Export.Gross_IBNR_Loss_and_ALAE, Pet_Tool_Excel_Export.Net_IBNR_Loss_and_ALAE, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Excess_IBNR_Loss_and_ALAE, Pet_Tool_Excel_Export.Gross_ULAE_Reserve, Pet_Tool_Excel_Export.Net_ULAE_Reserve, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Gross_Ultimate_Loss_and_ALAE, Pet_Tool_Excel_Export.Net_Ultimate_Loss_and_ALAE, "
strSQL1 = strSQL1 + strSQL1 + "Pet_Tool_Excel_Export.Number_of_Closed_Claims_Excluding_Closed_no_pay, Pet_Tool_Excel_Export.Number_of_Reported_Claims_Excluding_Closed_no_pay, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.""QUARTER EVALUATION"", Pet_Tool_Excel_Export.Exclusion, Pet_Tool_Excel_Export.XS_Treaty, Pet_Tool_Excel_Export.CAT_Treaty, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.CLASH_Treaty, Pet_Tool_Excel_Export.SEC_LOB "
strSQL1 = strSQL1 + "FROM PET_Tool.dbo.Pet_Tool_Excel_Export Pet_Tool_Excel_Export "
strSQL1 = strSQL1 + "WHERE (Pet_Tool_Excel_Export.EVA_DATE='12312012') OR (Pet_Tool_Excel_Export.EVA_DATE='03312013') OR (Pet_Tool_Excel_Export.EVA_DATE='06302013') OR (Pet_Tool_Excel_Export.EVA_DATE='09302013') OR (Pet_Tool_Excel_Export.EVA_DATE='12312013') "


'Assigning SQL Code to Pivot Tables
    Sheets("2013 Programs").Select
    ActiveSheet.PivotTables(1).PivotCache.Sql = strSQL1
    
Unload UserForm1 'Closes userform and removes it from memory


End Sub

The red line is where I'm getting the error.

Thanks

 

[Dreadknight Nasus]

Nope, that wasn't the problem, but I did finally find my answer in this thread: can't set CommandText property if cache has >1 rpt

Apparently it's a known bug in Excel and this was a valid workaround someone found.

Thanks for the responses guys.