PowerPivot VBA Refresh Code – Bug Fix

Just a quick post to alert those of you using my PowerPivot Refresh code to a bug in its “refresh a single table” logic. Under certain circumstances, linked tables (i.e. those on the “many” side of a relationship) will fail to refresh if specified individually (fine when part of a refresh all). See here for the details behind the bug, and thanks again to Rob Parker for bringing it to my attention. I’ve updated the sample code with the fix, download it here.

I’ve also tested the code against the just released SQLServer 2010 RC0 version of PowerPivot and it appears to work.

I’ve updated the equivalent PPREFRESH code in HAMMER and this is now part of the tool’s latest release (V1.3.4(Beta)). My previous post, Excel as a Book of Record, previewed the most important new commands available in this release. Alongside those, I’ve also added the following:

  • ISTABLE, if the previous COMMAND’s result  or the previous argument is not a table, this will abend the command sequence.
  • ISARG, as above, but this time checks for an argument (a HAMMER parameter may either be a table, a command or an argument- aka, an ARG).
  • ISOK, previous argument must be the string value “OK”.
  • TABLESARETHESAME, will fail if the last two tables are not identical. Intended mainly for automated regression testing.
  • ARGSARETHESAME, as above. but this time for ARGS.
  • _GUID, will return a globally unique identifier.

Download the latest version of HAMMER from here …

Advertisements

5 responses to “PowerPivot VBA Refresh Code – Bug Fix

  1. This is a bit off topic, but my end-users want to see the raw data behind the power pivot that they interact with in a powerpivot workbook published on a sharepoint site. Getting PowerPivot to flatten the data with many columns is pretty troublesome, and I can’t get the sharepoint site to refresh data connections other than for powerpivot. Any ideas?

  2. Pingback: Excel as a book of record. | Gobán Saor

  3. Pingback: oData in-process Server – auto refreshing PowerPivot linked Excel tables | Gobán Saor