Last week, a Microsoft Dynamics GP User (Glenda) asked about making an Excel Refreshable Report refreshable. She not only wanted the table to refresh, but she wanted the associated PivotTable to refresh as well.
After some research, she found that she could attach some VBA code to the Excel file to make the PivotTable auto refresh. The problem being, Microsoft Dynamics GP doesn’t recognize Macro Enabled Excel worksheets.
That attached video shows how to use Query Editor in Get & Transform (formerly Power Query) to achieve Glenda’s desired results.
It’s about a 12-minute video, but I walk users through connecting to data, modeling the data, storing the file so it’s accessible within GP and making it refresh upon opening.
Hope this helps!
Note: If using Excel 2013, download Power Query HERE!