Toluu

Microsoft Excel 12

The team blog for Microsoft Excel and Excel Services.

Using the Open XML SDK to Work With Excel FilesNovember 25

Over on Brian Jones' blog there are two recent articles that may be of interest to our readers here.  They deal with using the Open XML SDK to programmatically interact with Excel's XML-based file format.  One deals with "document assembly", which is essentially building a file mostly from scratch based on other data that is fed from some other system (e.g. a database).  The other deals with using the SDK to read data from an Excel file.  If these scenarios interest you, have a look see:

Document Assembly Solution for SpreadsheetML

Reading Data from SpreadsheetML

 

Using PivotTables to Analyze Data From a SharePoint ListNovember 6

Today's author, Diego Oppenheimer, a Program Manager on the Excel team, talks about connecting PivotTables to data stored in SharePoint Lists.

Many of us on the Excel team have been approached by customers asking us how to create a connection to a SharePoint list. SharePoint lists can be exported easily by creating a Microsoft Office Excel Web Query. The connection can then be tied directly to lists or PivotTables in the workbook like any other Data Connection.

I have a SharePoint List that we use to upload and keep track of all project management reports. The list contains information like the project manager that uploaded it, the date, and the name of the reports as well as other automated fields created by SharePoint to make my life easier. Using the filters provided by SharePoint gives me a great way navigate my list quickly and easily but I am more interested in creating a macro view of the team reports so an easy solution would be to connect my SharePoint List to a PivotTable. The fact that my SharePoint List has more than 250 rows makes the use of a PivotTable even more appealing.

Creating the Connection and the PivotTable:

Once we have located the SharePoint list we want to export we go to "Export to Spreadsheet"in the "Actions" menu button.

Excel Web Application Announced at PDCOctober 29

During today’s Professional Development Conference keynote, Ray Ozzie made an exciting announcement about a new way to view, edit, and collaborate with Excel.  If you weren’t able to watch the keynote, feel free to check out the following links: PDC Keynote Video, Official MS Press Release, and Channel 9 Video.

In case you’re in a hurry, here’s the bottom line:

• Excel is providing a web application which allows you to view, edit, and do real-time collaboration on spreadsheets in your choice of web browser – Internet Explorer, FireFox, or Safari!
• The Excel web application will be available in two channels: as a consumer service offered via Office Live and as a business offering via either hosted subscription or volume licensing.
• Any spreadsheets you author or edit online will be compatible with the Excel desktop client.

I know, I know: “Thanks for the teaser; where’s the app?”

While we will have a limited technical preview later this year, we won’t have a broadly public release for awhile.  As soon as I can share a date, and, more importantly, a way to sign up for the public release, I

Creating a Master-Detail View in ExcelOctober 24

Today's author, Dan Battagin, a Program Manager on the Excel team, talks about joining two tables in Excel (a.k.a. returning multiple rows for VLOOKUP).

Today, we'll take a look at the VLOOKUP function, and work out a way to get around its major drawback - it returns only a single value that matches the lookup.

If you're like me, there are times when you have a big table of data which is pretty well normalized, and you want to pull all of the rows out of that table that match a certain criteria. Well, it's easy to get the first match in that table by using VLOOKUP (in fact, it's easier than ever with the new structured references in Excel 2007):

Figure 1: VLOOKUP is an easy way to return a value out of the first row that contains a customer name.

As you can see above, with structured references in Excel 2007, not only is VLOOKUP easy to use, but it's actually pretty readable - we're matching the value of A9 in the first column of Table1 and retur

Don't forget about the "A" in "VBA"!October 17

Today's author, Stephane Viot, a Program Manager in the Office Global Experience Platform team, reflects on how leveraging application features could help speed up the development of your VBA solutions, while improving their performance.

Often -but hopefully not always- VBA developers forget about the "A" in "VBA". And by that I mean they might have the tendency of trying to do everything in VBA, like they would in VB, forgetting about what the host application has to offer. To illustrate that point -that is the main purpose of this post- let me share with you a practical example.

The problem

I often need to extract the list of unique values from a range -usually from a list or a table- and paste it into a mail as a comma-delimited string where all values are sorted in alphabetical order.

For example, given the following list, how would you pull out the list of unique states you are shipping products to, in alphabetical order, using VBA code -so that you can re-use it over and over?

The VBa-only approach

Before actually getting into the details of