Archive - Blog Posts on VBA

Extract Calculated Fields from an Excel Workbook - I was working with a client last week and the unthinkable happened.  The client made a simple editing mistake inside the Calculated Field editing dialogue box and it corrupted the Power Pivot workbook. We could still edit the corrupt workbook, but the Pivot Tables stopped working.  Oh No!!  Now what?  He had written half a […] Read More
Excel Keeps Crashing? Check your VBA code - I use Excel 2013 64 bit and sometimes Excel just keeps crashing on me.  It used to happen to me a lot more in the past, but after I discovered what was causing the repeat behaviour I have been able to greatly reduce how often this occurs. First a description of the problem If this […] Read More
How to Copy and Load VBA for Excel - I love VBA for Excel. VBA can automate tasks that you use often, making the process faster, easier and more accurate to execute. I like writing VBA for myself to solve my own problems, I like sharing what I write with others, and I like taking the VBA that others write and use it myself. […] Read More
Create Incremental Backups when using Power Query - Edit 16 May 2015: I now know the exact cause and cure for this problem.  See this post here This backup tip is still useful though. This is a follow up to this post where I talked about Power Query and warned to take frequent backups when editing your Power Query Workbooks.  The issue is […] Read More
VBA to Insert a Power Pivot Table - When Power Pivot was originally released by Microsoft, it was introduced as a Plug-In to Excel 2010.  After the second version of the Plug-In was built and released, the Microsoft Build team moved its focus to writing the product into the 2013 release of MS Office.  Unfortunately they ran out of time to write in […] Read More
Use Simple SQL to Manage your data in Power Pivot - When I started learning Power Pivot, I learnt there are 2 ways to import data. There is the “standard way” that uses the table import wizard – good for us Excel folk. There is a second way that uses the Query Editor to write some SQL code and extract the data you need direct from […] Read More
How to Bulk Refresh PowerPivot Workbooks on your PC - There are a number of different ways that you can refresh PowerPivot workbooks automatically on a schedule. The main ones I know of are: Using Power BI Using SharePoint Auto Refresh Using some sort of custom built desktop automation tool The problem with Power BI and SharePoint The problem with Power BI is that you […] Read More