Beyers has attended:
Excel VBA Advanced course
Pivot Tables from external data sources
I want to update the filters on a pivot table in excel 2007 using VBA. The data comes from an external datasource and a drop down is used to pick the value(date, in this case). When I record a macro, the filters value in VBA is referred to as an index/reference number, and an actual value can't be entered by setting the value of the cell in question(VBA doesn't allow it)
Can you think of a way to geet around this? I have tried calculating the number of days difference between a known reference number(for a date) and the date in question, and then just filling in the VBA accordingly, and although this works, it is not guaranteed that all the days will have data, and if there is a day missing, it will have an impact on the index/reference number, rendering this method useless.
Any ideas would be appreciated.
RE: Pivot Tables from external data sources
Hi Beyers. I'm sure there is a way of doing this programmatically, but without seeing the source data and how you've generated the Pivot table it's difficult to advise. It sounds like you are recording macros to capture the application of a filter through a drop drown, and it's the use of the drop down that is forcing the application of a index/reference number. Coding "from the ground up" would get around this. Alternatively, you might bring your external data into a separate worksheet, filter it there and then turn it into a pivot table. Also, if you do stick the data into a separate worksheet, the days missing in the external datasource could be programatically replaced by 0 values, which would give those days an index value and allow you to calculate the number of days difference.
I hope some of this helps!
Anthony
