I need some to provide the following VBA for a project that I am working on.
We have an Analysis Services SQL Server 2008 cube. It can be accessed through the creation of a Data Connection.
I need to automated the setup of the Data Connection, the Testing of the Data Connection and then application of that Data Connection to the Pivot Source of all pivot reports in a workbook.
I cannot provide access to the Database that we are using as it is local and cannot be accessed outside of our network.
I have used the Excel Macro Recorder to record the code that is created when the connection is created for a workbook. Also the code that is created when a pivot report is created...(see the attached)
Please provide the correct VBA script that can automate the Testing of this connection and if it does not work the script should then create the connection within the workbook.
Finally the script should reset the pivot source to be allocated to the new connection that has been created.
Any questions please let me know.
Only those with skills in this area should apply