Microsoft Office SharePoint Server and Excel Services - 06 Aug 2008

Dan Holme
Office & SharePoint Pro
Community Manager

Hi from Beijing, where I'm serving as the Microsoft Technologies Consultant for NBC television to help bring the 2008 Beijing Olympic Games to television and the Internet. I've arranged for several guest columnists to fill in for me when I'm not able to write a commentary. This week, Alan Sugano, networking consultant, custom developer, and contributing editor for Windows IT Pro magazine, writes about using Microsoft Excel Services with Microsoft Office SharePoint Services (MOSS) 2007.

Enjoy!

Microsoft Office SharePoint Server and Excel Services

When I first read the description about MOSS 2007 and Excel Services, I thought you could edit an Excel spreadsheet without having Excel installed on the computer that was accessing the portal. I thought that this would be a perfect solution for a client that works with large Excel spreadsheets. This particular client has remote offices that access the portal, so if a remote user needed to make a quick change on a spreadsheet, he or she could make it directly on the portal without having to download the entire spreadsheet. After taking a closer look at Excel Services, it doesn’t really have the functionality that this client needs. However, Excel Services does have some useful features that you might be able to use. However, there are quite a few issues that you should be aware of before you implement Excel Services on your MOSS portal.

• MOSS Version. You must have the MOSS Enterprise Client Access License (CAL) to use Excel Services.
• Excel Services and Data Connection Libraries. You can use Excel Services in conjunction with a Data Connection Library to access Business Intelligence (BI) data. In my opinion, this is one of the more useful features of Excel Services. You could create a data connection library that connects to an Enterprise Resource Planning (ERP) package to produce reports, charts, or graphs in Excel. The information in the spreadsheet can be displayed and refreshed directly on the portal. However, you cannot modify any of this data and save it back on the portal without downloading the spreadsheet and working on it locally.
• Displaying Excel Spreadsheets. With Excel Services you can view a spreadsheet on the portal without having Excel installed on the local machine. Only Excel Spreadsheets saved in Excel 2007 (*.xlsx) can be directly viewed on the portal. Previous versions (*.xls) cannot be viewed directly on the portal.
• Microsoft Office Version and Modifying Excel Spreadsheets. You can modify designated fields on an Excel Spreadsheet if you plan ahead. Once the designated fields are modified, you can recalculate the spreadsheet to view the results. However, you can't save any of your changes to the spreadsheet without first downloading it and using Excel to modify it locally. To configure a spreadsheet to allow for changes of designated fields, you must have the correct version of Microsoft Office to designate these fields. These versions include Microsoft Office 2007 Professional Plus, Enterprise, and Ultimate.
• Assigning Names to Cells to Allow for Changes on the Portal. If you can anticipate which fields users would like to change on the portal, you must create a name for each of these cells. You do this by selecting a single cell you want to name, and clicking on Formulas, Define Name and giving the cell a descriptive name. It’s important to use a descriptive name, because if you allow for multiple cell changes, it may not be clear which name a cell refers to.
• Publishing the Spreadsheet. After you assign names to all of the cells that will be modified, you must designate that these cells can be changed on the portal. Click Office Orb, Publish, Excel Services. Click on Excel Services Options, Parameters Tab, select the Parameters that you want to modify in MOSS, and save the file. If the Excel Services menu item doesn’t appear, you probably aren’t running a version of Microsoft Office that allows publishing of Excel spreadsheets on the portal.
• Place the Spreadsheet on the Portal. After you’ve properly configured the spreadsheet you can place it on the portal. Select the page (or create a new page) where you’d like the spreadsheet to appear and click on Site Actions, Edit Page, Add a Web Part and select Excel Web Access. Then select the spreadsheet you created for publishing. Now you should be able to view the spreadsheet and modify the entries that you designated when you published the spreadsheet. Note, however, that there is no way to save your changes with any new values you input on the portal, but you can display the results of your modifications.

I was a little disappointed with the lack of functionality of Excel Services, because I was expecting to be able to modify a spreadsheet directly on the portal without having to download it first and then use Excel to modify the file. But in reality, if you want to make any significant changes on a spreadsheet, you still have to download the file and work on it locally. Hopefully, with the next release of MOSS, Microsoft will improve Excel Services to at least allow for simple modifications to Excel spreadsheets to be made and saved on the portal without downloading them first. If you have to work with extremely large spreadsheets, one possible workaround is to use a terminal server that is installed at the same location as the MOSS portal and download the spreadsheet to the terminal server and use Excel on the terminal server to download, modify, and upload the changes to any large Excel files.

Alan Sugano is the president of ADS Consulting Group, which specializes in networking, custom programming, Microsoft .NET Web development, and SQL Server development. [email protected]

Events and Resources

Doing it Right! Deploying the Perfect SharePoint Farm
If you're like most IT shops, you've either implemented or are considering SharePoint. How do you deploy the optimal solution with limited time and expense? This fall, Windows IT Pro and Office & SharePoint Pro.com present the event series Deploying SharePoint. Industry experts will share best practices regarding infrastructure, design, forms configurations, and redundancy. Register early to save $100! Early-bird pricing applies through August 29.

Connect With Your Peers
Now there's a new way to connect with your IT peers! With IT TV (www.ittv.net), an exciting video website by Windows IT Pro, engaging interactively with other IT pros and developers has never been easier.

SharePointConnections Conference Fall 2008
Don't miss the premier event for Microsoft IT professionals in Las Vegas, November 10-13. Register and book your room by August 25 and receive a FREE room night (based on a three-night minimum stay).

Choose the Right Hosted Email Service for Your Business
Are you considering outsourcing email, arguably your business's most mission-critical application? A hosted Exchange service can save companies tens of thousands of dollars. Download this paper for a complete evaluation checklist for hosted Exchange services.

Access Expert SharePoint Solutions for only $5.95!
With the online Monthly Pass, you can have all the SharePoint solutions in Windows IT Pro right at your fingertips, including access to the more than 10,000 articles in our content archives! You'll also receive a full digital copy of the latest issue of Windows IT Pro!