Excel Services with Excel 2003

You don’t actually need the Excel 2007 client installed in order to create and deploy Excel workbooks that can be used in Excel Services in MOSS 2007.  In order to use Excel Services, you *do* need MOSS Enterprise 2007.  And workbooks used by Excel Services *do* need to be in Excel 2007 format, but as long as you have the Office 2007 Compatibility Pack installed, you can use Excel 2003 to save your workbook in a compatible Excel 2007 format.

Named Areas
One of the capabilities provided by Excel Services is displaying a specific range of cells or a specific chart on a web part page in Sharepoint.  The behavior and implementation of how you setup these named areas is slightly different between Excel 2007 and Excel 2003.  With Excel 2003 it provides a default name for each chart that is created.  This looks like “Chart 1″, “Chart 2″, etc.  You can override this name by selecting the chart object and entering a new name in the Name Box field, which appears by default in the top left portion of the screen. 

ExcelNameBox

Note that the trick to selecting the chart object so that you get this Name Box populated with the actual name of the chart is to hold down the Ctrl key while clicking the chart.  Otherwise you’ll end up selecting a particular element of the chart (e.g., Chart Area, Plot Area, etc.).  This is slightly different behavior than Excel 2007, and it drove me nuts trying to figure this out.

To get a range of cells to be a named area, simply select the range of cells and type in a name in the Name Box.  To change the name you need to select the identical range of cells before the Name Box will populate with your name to change.  Again, not obvious.

Save As Excel 2007 Workbook
Once you’ve done this, simply save your Excel workbook in the Excel 2007 Workbook format with the Save As menu item.

Excel2003SaveAs2007

You can then upload this workbook file into your Sharepoint document library, like any other file you might want to make available in a library.  I won’t go into details on Trusted File Locations, but you do need to have your document library setup as trusted in Sharepoint Central Admin in order for Excel Services to display your workbook in the web part or a web page.  Also note that there are a variety of unsupported features and requirements that you need to keep in mind when preparing your workbook for Excel Services consumption.

Sidebar – Excel 2007 Publish Option
Excel 2007 provides you with a an alternative approach to making your workbook available to Excel Services.  It has the idea of “publishing” your workbook to Excel Services.  You’ll see a new menu item for this in the Excel 2007 client.

Excel2007PublishExcelServices

When you select this option you basically get the same Save As dialog, but with a couple of extra options.  One of which is the Excel Services Options button.

Excel2007SaveAsExcelServices

Clicking this button presents a dialog where you can restrict which specific worksheets, named range of cells, or specific charts you want to make available for viewing in Excel Services.

ExcelServicesOptions

In the text of this dialog it reiterates that what you are specifying is really only restricting what is seen in the browser view of the document.  A user with privileges to open the workbook in their Excel client will be able to see the entire contents.  All that Excel is really doing when you specify these options is adding some metadata to the file so that Excel Services knows what to show in the browser view of the workbook.  So don’t think of this as a high level of security control over the contents.  However, you actually can restrict users to only opening the workbook in the browser view by only granting them View Only access in Sharepoint (instead of Read, for example).

This Excel Services Options dialog is also used to specify specific cells that you want to make available as Parameters in Excel Services.

‘Show’ and ‘Parameters’ – Not Features in Excel 2003
Note that these two features in the Excel Services Options dialog are two features you will not be able to support if you’re using Excel 2003 to create your workbooks for Excel Services.  So, if that’s important to your solution, then you’ll need to upgrade to the Excel 2007 client.

Excel Web Access Web Part
Once you have the workbook saved in the document library you can make it available on a Sharepoint page using the Excel Web Access web part.

ExcelWebAccessWebPart

Then modify the web part properties to specify the specific Excel 2007 file, and the named item/area.

ExcelWebAccessWebPartProperties

The Named Item field corresponds to the Named Area within the Excel workbook, and will restrict what is actually shown in the web part on the page.  You can use multiple instances of the Excel Web Access web part on the same page if you want to display multiple charts or multiple cell regions.

I’ll provide another post in the future that demonstrates how to hook up Sharepoint filters to dynamically control the named area shown.

About these ads

3 Responses to “Excel Services with Excel 2003”

  1. Hari Menon Says:

    Thanks for the detailed post.

    We have MOSS Enterprise 2007 but only Office 2003. I want to use an Excel 2003 worksheet to import external data (from SQL Server) and then use this for feeding into a KPI list.

    We do have the compatibility pack installed. These are the steps I followed:
    1. Created a workbook in 2003 with a query to the database
    2. Made the query cells a named area.
    3. Saved in Excel 2007 format (*.xlsx)
    4. Uploaded to a document library that is a trusted file location
    5. Added an Excel Web Access web part and used this xlsx file location.

    But I get an error as in this screenshot – http://i44.tinypic.com/6yffpy.jpg

    Could you please let me know what would have gone wrong?

    Many Thanks,
    Hari

  2. sidersdd Says:

    @Hari – Do you have this implemented as a Query Table? If so, that is not supported by Excel Services. Pivot Tables with an external data source are supported however.

  3. I can not get get the Nmae Box to allow me to change Chart Area to a specific name. I and trying various ways to use the Ctrl key to allow me to to this. Then I highlight my data sheet I can give a name.

    I have 2 problems:

    The first I have 2 graphs I want to show, but I do not want to show the data tab for this workbook (which I will update weekely) I tried the above as a solution for showing graphs only. I do not mind having the two tabs showing for the graphs for the user to toggle between

    The second when I do figure out how to get this to work. I need to know how to control the size in my web part. Saving the image at 25% does not make the image smaller on my web page.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: