Archive for 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 dozen new Measures (Calculated Fields) and all that effort now seemed to be lost.

Luckily we had incremental backups of the workbook we were developing.  Well actually it wasn’t luck – it was good forward planning.  Even though Power Pivot is an excellent tool, anyone that uses it will know it is a bit flaky and it can cause problems when editing.  As a precaution I always keep incremental backups of my workbooks when doing data modelling work.  I have optimised this process using an incremental backup macro I created.  You can read about that here and even download the VBA code to use yourself.

OK, but we still had a problem.  We had a backed up copy of the workbook, but this backed up copy didn’t contain the last 30 mins work – about half a dozen DAX formulae.  Power Pivot doesn’t allow you to export the Measures (Calculated Fields) and then reuse them.  But there is a solution using DAX Studio.

What is DAX Studio?

image DAX Studio is a great tool that allows you to do many things with your Excel Power Pivot data models.  It will allow you to use DAX as a query language to interrogate your data model and many other things.  One of those “other things” it allows you to do is get a list of all of your DAX Measures in a new sheet in your workbook.

You can read more about DAX Studio and download it here.  Note I installed version 2.2.1 today and it didn’t work for me.  I am currently using version 2.1.1 and that works fine.

Step by Step Instructions on how to Extract Calculated Fields

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

excel has stopped working2If this happens to you then you will already be aware of this scenario.  You are inside your workbook doing something important (it always happens when you are doing something important), and then the screen background greys out, and you get a message saying Microsoft Excel has stopped working.  It then “pretends” to be looking for a solution prior to telling you that it can’t fix it and you have to close the program.

And if your experience is anything like mine, once this starts to happen with a workbook, it keeps on happening over and over with seemingly no way out.

So what causes this repeat behaviour?

Well there are 2 main causes for this problem.

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.

Now that I am teaching a lot of students how to use Power Pivot, I am coming across a lot of burgeoning Power Pivot users that could benefit from VBA but have exactly zero experience in how to copy and load VBA code that is made available by others for you to use. As Chip Pearson pointed out to me, you have a responsibility to ensure you have the rights to use the code. The purpose of this post therefore is to provide step by step instructions on how to take someone else’s VBA code (that you have permission to use) and get it working on your PC – zero VBA experience assumed.

The steps I will cover in this post are:

  1. Turn on the Developer Menu
  2. Set up your personal.xlsb workbook so that you have somewhere to save your VBA code
  3. Copy your first VBA code into your personal workbook
  4. Add the code to the quick launch menu

I am writing this post using Excel 2013 as the tool, but the process is essentially the same for other versions too (although the user interface may be slightly different).

Turn on the Developer Menu

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 that Power Query is a bit flaky, and hence it is well advised to create incremental workbook backups with different file names in case bad things happen.

Anthony Newell kindly posted an idea (and some code) that he used to automate the process of taking incremental backups.  I took his code as inspiration, and then made quite some changes to meet my needs.  I am sharing this new code here for those that want to use it.  It is equally as useful for any workbook that you have invested time in, and you can’t afford to lose.  It doesn’t have to be a Power Query workbook.

What the Code Does

  • First it checks if there is a “version sheet” in the active workbook.  If not, it creates this version sheet.  The version sheet contains the current version number and the location of your backups.  You can manually change the default backup location in this sheet (but it always saves this as a sub folder of the original file location).
  • It then saves the active workbook with its current name in the current active folder (no version number added yet).  I have done it this way because I don’t want my active workbook file name changing all the time.  What I want is a backup that I can recover later if needed.
  • It then saves a copy of the active workbook in the archive folder with the current version number appended on the end.
  • It then closes the version with the version number added and reopens the original file so that you are working on the original file without the version number.
  • Finally it increments the version number on the version page.

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 all the features into the Office 2013 version – consequently there are a number of features that are actually better in Excel 2010 plug in version than the Office 2013 version.

In 2010, it is easier to edit your measures

right clickIn Excel 2010 you can right click on a measure in the Field List Window and select “EDIT”.  In 2013 you have to open the Manage Calculated Fields dialog and then find the measure you want to edit.

And 2010 is easier to insert a Power Pivot Table

missing button In Excel 2010 there is an “insert pivot table” button on the Power Pivot tab in Excel.  Just click on this button to create a new Pivot Table that is connected to the data model.  No such button exists in Excel 2013 (unless you go into the Power Pivot window.

The alternative to navigating to the Power Pivot window is you have to use 8 clicks to insert a table (Shown below). 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 the database. The problem is that you need to know SQL to be able to do this. So right from the start I decided this second approach wasn’t a very useful tool for the typical Excel user.
import choices

Recently though I have come across some real life challenges that I needed to solve. I have a very large “master” workbook that has all my measures and all my data (for several years) loaded – it is almost 400 MB and contains more than 40 million rows of data. I keep a master copy so I don’t have to re-write all these measures every time I have a new request for a report. But on the flip side, if someone wants a report that say contains just 10 products, I don’t want to send them the entire 400 MB workbook. 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:

  1. Using Power BI
  2. Using SharePoint Auto Refresh
  3. Using some sort of custom built desktop automation tool

The problem with Power BI and SharePoint

The problem with Power BI is that you need to have a subscription for every user that wants to consume the reports – and it is not cheap. I cover the downsides of Power BI in this post about how to access PowerPivot on iPad. SharePoint is a good solution if you have SharePoint Enterprise and you want to share your files in SharePoint. Many people don’t have access to SharePoint and/or need to refresh files for distribution in other ways. So that brings me to option 3 – desktop automation.

How to bulk refresh powerpivot workbooks on your PC

I have been a closet VBA coder for many years. In fact I first taught myself “really bad coding” using Lotus 123 Macros. Over the years I have accumulated code that I reuse for new purposes. At some stage about 10 years ago, I created a VBA workbook that will list the contents of a folder into a workbook. As I was thinking about the need to refresh PowerPivot Workbooks on a PC, I realised that I could reuse this code as the core of the project. Now I don’t profess this to be the best code, or even good code for that matter. But I can tell you this has been working successfully for me for some time.

How the refresh process works

Read More