The overall process is linking MS Excel 2013, via the PowerPivot Data Model, to MS Access 2013.  From the PowerPivot Data Model, I set up SQL queries which come through as Tables in PowerPivot Data Model.  From the Tables, I create PivotCharts and PivotTables which are placed into the MS Excel Sheets.  The PivotCharts and PivotTables are then formatted.  I then create UserForms into which the PivotCharts and PivotTables are displayed for the users of MS Excel.  These are then printed and saved as images (jpg) for the users of MS Excel.

However the problem I have is that there are hundreds of spreadsheets to set up in MS Excel, each with numerous PivotCharts and PivotTables, therefore I am looking for help on how to automate this process with Visual Basic for Applications rather.

I have managed to work out how to use Visual Basic for Applications to automate most of the formatting of the PivotCharts and PivotTables, and am left with the following specific tasks to automate with Visual Basic for Applications:

1. Automate Connection between MS Excel PowerPivot Data Model and MS Access (with VBA)

1. In PowerPivot Data Model

1. Set up PowerPivot Data Model database connection to MS Access
2. Set up Table consisting of SQL query to the database
3. Create PivotTables and PivotCharts in the Sheets from the Table

2. In sheets

1. Rename sheets tab (this sheet name is referred to when formatting therefore is required)
2. Name the range of the PivotTable and PivotCharts (this range is referred to when formatting therefore is required)
3. Select required attributes to be shown in the PivotTable and PivotCharts as fields

2. Format Tables (with VBA)

1. In sheets

1. Hide sub-totals
2. Remove the border shadow on the table
3. Hide filter drop-down icons

3. Format Charts (with VBA)

1. In sheets

1. Extend pie chart type PivotCharts overlapping labels to display leader lines neatly
2. Sort Legend items from A to Z

4. Create UserForms (with VBA) 

1. Automate the template creation
2. Assign a Save As button to save the form/image as any document/file type and to any file folder.

Hi Paul,

I suggest you post your issue to Power BI> Power Pivot forum.

https://social.technet.microsoft.com/Forums/office/en-US/home?forum=sqlkjpowerpivotforexcel&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

Regards,

Emi Zhang
TechNet Community Su