Take a walk through of the new PivotTable functionality in the release of OfficeWriter 8.4. To access the new API, download an OfficeWriter Evaluation.
To view a complete list of features and fixes in this release, see What’s New in OfficeWriter 8.4.
All posts by Alison
What’s new in OfficeWriter 8.4
PIVOT TABLES ARE HERE!
OfficeWriter 8.4 packs a powerful punch with exciting new features, most notable of which is ExcelApplication support for PivotTables in OOXML (XLSX, XLSM) files. ExcelWriter already supports the use of PivotTables in ExcelTemplate and SSRS reports, but now you can programmatically create, manipulate, and remove PivotTables with ExcelApplication.
The new PivotTable API gives you the freedom to:
- Create PivotTables from scratch (see our tutorial on Creating a Basic PivotTable)
- Add and manipulate PivotTable fields – data value fields, column labels, row labels, and report filter page fields
- Access common settings like empty/error values, refresh data when opening a file, and the number of cached items to retain
With the API you also have the ability to change the data source of a PivotTable. Continue reading What’s new in OfficeWriter 8.4
Cookie Wars Participant: Alison
On the heels of the successful pumpkin pie extravaganza, 12 brave volunteers will present their finest at a banquet in one week. Before the banquet, we’re holding the Cookie War Challenge: where all of the participants will use technology (bonus points and automatic win if they incorporate the technologies used to power OfficeWriter) to represent their cookie, recipe, or baking process. For example, code your recipe in Chef.
As the cookie contest organizer, I decided to kick it off and use Excel VBA and conditional formatting to represent my entry (Peanut Butter Cookies).
How I did it: I made a macro that updates the timer; then layered conditional formats Continue reading Cookie Wars Participant: Alison
3 ways to have a very agile Thanksgiving
As we head rapidly into the holiday season, taking vacation to celebrate with family and friends is bound to occur. For most, it is a time to relax and reflect on what to be thankful for, accompanied by turkey (or tofurkey for the meat-conscious) and cranberry sauce. For others, it can be a hectic time of delegating responsibility and tying up loose ends. Not so for this product owner! The following are 3 golden rules I follow before vacationing to keep the holidays happy and the hectic at bay.
#1 – Give someone the executive power!
Since the product owner makes the decisions, it is important to appoint someone else to make all of these choices. I usually pick someone who has a similar mindset when it comes to the product and prioritizing customer issues. That way, even if I don’t make the decisions, the outcomes are usually the way I would have wanted them.
#2 – Plan ahead, but don’t plan too much.
One of the luxuries of being a Scrum product owner, is that Scrum is a system that adapts well to change. If the unexpected happens (as it frequently does), having well-defined stories, a prioritized backlog, and someone with the decision-making power, goes a long way. Rather than plan for every possibility (because we all know that polar bear is going to destroy the server room), Continue reading 3 ways to have a very agile Thanksgiving
How to Fix Those Pesky Number Formats on Excel Charts
You’ve just finished putting together the best sales report ever, but you realize that the value axis is sporting 9 digits of data instead of the cleaned up version of $1M, $2M, etc…
All I wanted to do was to show $1M instead of $1,000,000.00. Was that too much to ask for?? Is there any hope for getting the value axis formatted properly in this forsaken world of despair?!
Yes. Do not despair. It’s pretty easy, actually.
Start by right-clicking on the value axis Continue reading How to Fix Those Pesky Number Formats on Excel Charts
New in OfficeWriter 8.3: Auto Filters and More!
OfficeWriter 8.3 is filled with exciting new features. Here are some of our favorites:
Auto filters in ExcelApplication
Auto filters are an easy way to view and filter data in a worksheet, and now you can add them programmatically with ExcelApplication in XLSX and XLSM files!
All you need to do is create an area on the worksheet where you want the auto filter to be, then apply the auto filters to that area. This works on data sets that already have data, and data sets that just have ExcelTemplate data markers.
wksht.AutoFilter.Area = wksht.CreateArea("A6:D14");
Remember, you only get one set of auto filters per worksheet, so if you set the auto filter to a new area, you’ll lose your original filters. Continue reading New in OfficeWriter 8.3: Auto Filters and More!
Beneath the Surface: Why Windows 8 Isn’t Completely Doomed
Now that you’ve all groaned over my “punny” choice of title for this post, let’s get down to it!
In my post on why I think Windows 8 is like the Water Bear, I voiced some concerns over adopting Windows 8 for desktop PCs and how that would impact my ability to perform my current job. Here’s a quick rebuttal to why I think Windows 8 isn’t completely doomed, especially when paired with the Surface tablet:
Sharing and Consuming is easy on Windows 8
After I finally relented and got a tablet (a Nook – before I was running out of shelf space for my books), I realized that I spent most of my content consumption time on my tablet. It was easy enough to watch a video, look out for e-mail notifications, or Facebook updates while reading a book. The Windows 8 Metro mode is better for sharing content or getting quick glance updates. This is perfect for when I don’t want to go to my laptop or use my tiny phone screen.
The Surface has a keyboard, for those of us who survive on haptic feedback.
The most prominent complaint I had with modern tablets was the lack of a keyboard. As a touch-typist, it is literally impossible for me to type with a touch-screen. If I can’t generate written content, this takes away half of the usefulness of a tablet.
Luckily, the Surface has a keyboard that easily stores away when you’re not using it. It’s also not a detached accessory, so it (hopefully) has the same stability that a laptop has, with all the bonuses of a touch-tablet.
Continue reading Beneath the Surface: Why Windows 8 Isn’t Completely Doomed
Why Windows 8 is like the Water Bear: Criticisms of Windows 8
Behold, the mighty Water Bear!
If you don’t know what a Water Bear (or Tardigrade) is, you should. These water-dwelling creatures are 0.3-0.5mm long and can survive up to 10 years without water. It was also shown in 2007 that they could survive after being exposed to the vacuum of space for a number of days.
Now, what does the Water Bear have in common with Microsoft’s latest operating system? Let’s see…
- It’s kind of fun to see them in action in a short YouTube video, but after 90 seconds you’ve already moved on to other things.
- Water bears can survive in the most extreme environments…so can Windows 8 (?).
- Water bears have 8 legs…Windows 8…well, you get the gist.
As you might have noticed, I am not ecstatic over the emergence of Windows 8. Granted, I’m going off of a collection of online reviews and the 60-second release preview video, but there were still some red flags that popped up on my radar and are worth mentioning.
You can’t minimize applications
John C. Dvorak from PCMag.com points this out in his post, Metro: That’s Not My Name. Minimizing applications is a very common need, especially in the enterprise arena. This spells trouble for me in Windows 8 because at my job I am constantly switching contexts depending on which hat I am wearing. Each of my roles demands an exclusive set of applications to accomplish what needs to be done, and I need to be ready at a moment’s notice to switch contexts.
Continue reading Why Windows 8 is like the Water Bear: Criticisms of Windows 8
Outlining Excel Reports with ExcelWriter
Outlines in Excel are a useful way to organize and present lots of data in workbooks. ExcelWriter offers several different ways to integrate outlines into Excel reports:
ExcelWriter’s Application object (available in Enterprise Edition), provides full control to modify Excel files programmatically. This includes adding or removing Excel outlines and more:
- Group an area of rows or columns
- Read or set the level of outlining for any given row or column in a worksheet
- Read or set whether the group that a given row or column belongs to is expanded or collapsed
- Ungroup all the rows or columns in an area of a worksheet
- Detect where the summary rows or columns are located for all the groups in a worksheet
Run-time error 1004 with PivotItems
I was working on some VBA code to hide all the values for a particular report filter, except for a specific one. The code looped through all the pivot items for a particular field, turned them off and then turned the one I wanted back on.
Sub SetFilter(ByVal Worksheet As String, ByVal pivotTable As String, ByVal pivotField As String, ByVal selectValue As String)
Sheets(Worksheet).Select
Dim pvtTable As pivotTable
Set pvtTable = ActiveSheet.PivotTables(pivotTable)
Dim pvtField As pivotField
Set pvtField = pvtTable.PivotFields(pivotField)
For Each filterValue In pvtField.PivotItems
filterValue.Visible = False
Next
pvtField.PivotItems(selectValue).Visible = True
End Sub
While testing the code, it worked until it hit the last filter value, at which point it would throw an exception: Continue reading Run-time error 1004 with PivotItems