Just because we less-than-3 you (and Excel).
See how we built it and how to make your own cookies in Excel by visiting the original post.
Tag Archives: Excel
Watch What’s New in OfficeWriter 8.4’s PivotTable API
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.
Is Microsoft Excel the Next Great Business Intelligence Tool?
With Microsoft’s release of Office 2013, now fully equipped with features such as PowerPivot and PowerView, news outlets and blogs are abuzz speculating this is a push to make Excel the next Business Intelligence (BI) tool. Software Advice sat down with Rob Collie, CTO of PivotStream and one of the founding engineers of PowerPivot, to get his perspective on how the new Excel will affect Business Intelligence and Excel professionals.
[Read the full interview here.]
Takeaways from the interview:
1. More adoption of PowerPivot in the Excel community. PowerPivot has yet to receive a lot of attention among the Excel audience.
“Unlike programmers, BI specialists, and other IT pros, the Excel audience doesn’t congregate at conferences and they don’t closely monitor what Microsoft is saying about the next version of their toolset. Overwhelmingly, the way they learn about new Excel capabilities is by inspecting the latest version once it lands on their desktop.”
All of that is about to change now that Office 2013 has more tightly integrated PowerPivot into Excel. Originally a separate download, PowerPivot is now part of the original package upon purchase.
2. The PowerPivot community is growing.
“Using PowerPivotPro traffic as a guide, I’ve seen the PowerPivot audience double in size every year since 2009. But I’d still estimate that less than one percent of the eventual PowerPivot target audience has been exposed to the product as of today.”
3. All Office users now data analysts? Continue reading Is Microsoft Excel the Next Great Business Intelligence Tool?
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
NEW OfficeWriter ASKs Forum
You have opinions and we want to hear them. That’s why we’ve created OfficeWriter Asks, a new forum where you can post all of your feedback and wishlist items. Our aim: to bring you the features you desire. Each week we’ll pose a new question related to our OfficeWriter product and how we can improve upon the features you use and need.
- Pivot Tables Love them? Hate them? How do you use them?
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
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
Creating Dynamic Menus in VBA Add-Ins
With the new release of OfficeWriter 8.1’s designer ribbon, I’ve been working more with the ribbon interface. This is a follow up post to one written previously on updating an old Office Add-In to use the new ribbon interface. In this post we’ll explore how to create dynamic menus in VBA Add-Ins.
Once you have the ribbon for a VBA Add-In, there are a number of things you can add to make the UI more useful. One of the more versatile elements available is the dynamic menu. This menu lets you change it during runtime! Unfortunately, working with it in VBA is a little strange.
First add the base xml to the ribbon xml like you would for any UI element.
…
<group label=”Ribbon Group” id=”group1”>
…
<dynamicMenu label=”Dynamic Menu” id=”menu1″
imageMso=”TableInsert” getContent=”ReloadMenu”/>
</group>
…
The important attribute here is “getContent”. This is a callback to a method in the VBA which will create the items for the menu. The method will literally create xml for a menu from scratch and return it. Anything that can normally be in a menu can be added this way – including buttons, submenus, and separators. The sample code below creates a button and a submenu with another button inside it. Continue reading Creating Dynamic Menus in VBA Add-Ins
Comparing Excel Export Functionality in SSRS 2012 to OfficeWriter
In the latest version of SQL Server, SQL Server 2012, Reporting Services now supports the Office 2007\2010 XLSX file format (aka Office Open XML file format) in its Excel renderer. In fact that is the default format for the Excel renderer. The old renderer for the legacy Excel 2003 XLS format has been named to “Excel 2003” but it has been deprecated and is not visible in the available export options list by default. For reference see the related section in the MSDN documentation. Although SSRS 2012 has now the ability to export an RDL report into an Excel XLSX workbook, the Excel renderer still has certain limitations. In this blog post, we will discuss some of these limitations and compare them to the OfficeWriter renderer in SSRS.
Charts
Probably one of the major limitations is that charts will be exported as pictures. In the related section in the MSDN documentation it states:
“Charts, sparklines, data bars, maps, gauges, and indicators are exported as pictures. The data they depict, such as the value and member labels for a chart, is not exported with them and is not available in the Excel workbook unless it is included in a column or row in a data region within a report.”
With the OfficeWriter renderer on the other hand, the charts will be native Excel charts with live data. Let’s see the difference in the following screenshots:
“Product Sales Report.xlsx” generated using the SSRS 2012 Excel renderer (note that the chart is a picture)
Continue reading Comparing Excel Export Functionality in SSRS 2012 to OfficeWriter