98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Pivot Tables from external data sources
Pivot Tables from external data sources
Resolved · Low Priority · Version 2007
Thu 22 Jan 2009, 14:08Delegate Beyers said...
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.
Mon 20 Apr 2009, 10:46Trainer Anthony said...
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
Mon 27 Apr 2009: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Copying Formulae QuicklyThere is a quicker way of copying a formula down a column. |
