Where is that &$#@ command in Office 2007?!

 

Sounds familiar? This is the No. 1 complain for first time user of Office 2007 because of the new Ribbon user interface, quite a number of commands have been repositioned. I keep on telling people that after a couple of weeks they will get used to the new UI and actually be happy with it. Honestly at least I do (whether or not I working with Microsoft)

So for the rest of us, Office Labs launched a new Office add in called Search Commands. It helps you find commands, options, wizards, and galleries in Microsoft Office 2007 Word, Excel and PowerPoint. Just type what you’re looking for in your own words and click the command you need. Search Commands also includes Guided Help, which acts as a tour guide for specific tasks. (pic above)

Go here for more details.

Identify Duplicate Values in an Excel Table

In Microsoft Office Excel 2007, you can easily highlight duplicate values with conditional formatting.

1. Select the list in which you want to identify duplicates.
2. Click the Home tab on the Ribbon.
3. In the Styles section, click Conditional Formatting, point to Highlight Cells

Rules, and then click Duplicate Values. Pairs of duplicates will then be highlighted, and you can choose which of each pair to delete.

Office Live Workspace available worldwide

Microsoft just launch the beta version of Office Live Workspace after allowing pre-registration for US based customers few months ago. Good news is this time it is available worldwide. Goto this URL to sign in using your Windows Live/ Hotmail ID http://workspace.office.live.com/

Upon signing in you will get a screen as below, notice that there is an ‘Install Office Add-in’ button. This is a less than 1Mb .msi file download which gives you integration on Word, Excel and PowerPoint.

I look at Office Live Workspace as a online extension of the Office client. There is a personal document library for you which you also can share with other people. Then for Outlook you can synchronize your tasks, appointments, contacts and even notes on your workspace.

On the help file it stated that the integration is available for Office 2003 and XP as well. Cool, FREE new features for older version of Office product. How many vendor does that?

Integration with Office 2003

Integration with Office 2007

Hiding Task Pane programmatically with VSTO

Today I struggled for some time to figure out how to control the visibility of custom task pane in Excel 2007. I had done quite a few Word 2007 addin projects but this is the first time I try to do it in Excel.

In Word 2007 with VSTO 3.0 (that comes with VS2008) to hiding the custom task pane I would add this in the Click event code

Me.Application.TaskPanes(Word.WdTaskPanes.wdTaskPaneDocumentActions).Visible = False

But with Excel 2007 it is different. You have use the DisplayDocumentActionTaskPane class

Me.Application.DisplayDocumentActionTaskPane = False

I found the solution in the MSDN article here.

Just FYI, adding a action pane is same for both application using the following method

Me.ActionsPane.Controls.Add(actions)

Excel mapping to XML Schema

While OpenXML’s WordProcessingML has the capability to attach a custom XML instance and data bind with Content Controls, Excel achieve similar goal (similar only, not exactly) by attaching a XML schema (from xl\xmlMaps.xml file) and have xpath mapping to cells. Please refer to my previous post, I can even map a sequence of XML elements as a table in Excel. This post is more like a beginner’s guide to how all this work.

1. Enable Developer tab in the Ribbon UX. Click on the Office button and open Excel options.

2. Click on the Popular tab, and check "Show

Now the Developer is visible

3. Now click on the Source button to open up the XML Source task pane on the right.

4. Click on the XML Maps button on the bottom to open up XML Maps window

5. The click on Add button and attach the XML Schema (.xsd) file you want.

6. Click OK at XML Maps window and you will see the XML Source task pane XML tree got populated with the elements of your XML schema.

7.  To map the elements onto the Excel sheet, I just drag and drop the elements onto the cell. Should I want to add the item list as a table, I drag the item over.

8. Let me populate the sheet with some data.

9. Now to export the data I key in just now as a XML instance of Claims, click on the Export button on the Developer tab.

10. Give a proper name to the XML file in the Save dialog, then open it with IE.

 

Wala!! You can see data in the XML file is same as the one in Excel. So you can export this XML instance to any Line of Business application for data processing.

Custom XML Schema in Excel 2007 – Multi line items tip

 

Excel enables you to import your own custom XML Schema or public XML standards such as RosettaNet and bind the elements to a particular cell. Excel 2007 take one step further with the ability to detect detail line items and apply table formating as well as filtering when you drop the detail line onto the spreadsheet as below.

However there is a rule apply here, you must define the minOccurs and maxOccurs attribute in the element level (example here would be LineItem)

if you left out the minOccurs and maxOccurs attribute, Excel will not able to determine the element is a detail line.

How to export ASP.NET GridView to Excel spreadsheet

You can use this and create a button on your ASP.NET page to export a GridView report to Excel.

http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html

Oracle BI’s blog poll about .NET 2.0 installation and support for Office 2007

Oracle Business Intelligence blogger  is doing a poll on .NET 2.0 installation out there. This maybe because that the new Oracle BI Add In for MS Office 2007 requires the .NET 2.0 installation. Yes, and I think this is a fantastic OBA adoption here with Office 2007 as the front end to Oracle’s OLAP Cube than creating a new one.

And to my surprise there is a 58% YES (23/ 39) response out there.

Offline OLAP Cube browsing in Excel 2007

Just realize today that I can actually download the whole OLAP cube and browse with Excel 2007 when you offline. Below are the steps:

1. Access the .ODC file online

2. When you click Open, you will fire up Excel and the Pivot Table Field List.

Construct you Excel Pivot Table view. For example I put Reason on Row Labels, Calendar Year, Quarter and Month on Column Labels and Incidents Count on Values.

To take the cube offline, click on Options –> OLAP Tools –> Offline OLAP

Because you don’t have a Offline data file,  on the Offline OLAP Settings window you will see Offline OLAP option is grayed out. Create the data file by click on Create Offline Data File.

 You will be presented by a wizard, click Next on the first screen

Because a real life data warehouse is huge and you might not want every data, you can choose the level of field you want to store in the offline data file. Then click Next.

Then specify the item within the level and click Next.

 Finally specify where you want to store the data file and click Finish

Now you see Offline OLAP option is enabled.