Exception from HRESULT: 0x800a03ec when trying to clearallfilters

See more:

MS-Excel

VBA

SSIS

pivot-table

I have an Excel sheet that has a pivot table in it. This pivot table pulls its data from a table that I have no other access to. I need to upload this data into a MySQL table on a daily basis. The part that will take the pivot table and upload it to MySQL works perfectly. The problem is that I have to automatically update the pivot table. I need to change the filter from whatever date is in it to yesterday's date. I am using a script task in SSIS to attempt this. Below is the code that I am using to attempt this. It fails at the ClearAllFilters call.

        Dim objExcel, objWorkbook, objWorksheet, objPivotTable, objPivotField
        Dim dtmYesterday

        Const xlCSV = 6

        dtmYesterday = FormatDateTime(Date.Now, 2)
        dtmYesterday = DateAdd("D", -1, dtmYesterday)objExcel = CreateObject("Excel.Application")
        objWorkbook = objExcel.Workbooks.Open("c:\data\datafile.xlsm")
        objExcel.DisplayAlerts = False
        'objExcel.Visible = True'

        objWorksheet = objWorkbook.Worksheets("Build Your Own")
        objPivotTable = objWorksheet.PivotTables("PivotTable4")
        'objWorksheet.PivotTables("PivotTable4").PivotFields("[Time].[Day1_2].[Day1_2]").ClearAllFilters()'
        objPivotField = objPivotTable.PivotFields(6)

        objPivotField.ClearAllFilters()
objPivotField.currentpage = "[Time].[Day1_2].[Day1_2].&[" & dtmYesterday & "]"

What I have tried:

I have tried everything that I can think of. I tried to call it explicitly, and using the field numbers. Neither worked. I thought that maybe clearing the fields is giving me too much data so I tried to change the filter without clearing it first. I received the same error. It seems that any time I try to do anything with the field it gives me the same error. The field is correct and working. When I step through the program I am getting all the proper data for the field. I just cannot seem to figure out how to clear it or change it. Any help would be greatly appreciated.