Charts and Excel Services 2

Continuing on from the first post, I learned something very interesting from a colleague doing her own experimenting.

In addition to substituting an Image Web part for an Excel Web Access web part, you could also save an Excel file as a htm file, then after saving it in a suitable location on your SharePoint site, use a Page Viewer web part. It should look great.

I really like this because you can use all kinds of things not possible using a Excel Web Access web part. (Keep in mind any image is not going to be interactive though). For both image (jpg) and htm files, the rendering is a lot better, depending on the complexity of what you are showing.

I'm not done yet. No matter how good things look, somebody may want to print everything. I've noticed that trying to print from Internet Explorer is sometimes not possible because not everything will show (don't know about other browsers as I've not tried them for this)

A solution is to save the charts/ranges from Excel as a PDF file. PDF files can't be used in a Page Viewer web part, but you can upload them as a separate file, then link to them (preferably have some text above or below the Page Viewer web part, along the lines of "Click here to print"). Where I work, the file will open in Adobe which is just the right tool for printing PDF files.

Okay, next time I'll have some pics. Because I want to show you how easy it is for Excel to create HTML tables in SharePoint.

Charts and Excel Services

My apologies for no pictures, especially considering the content. I will try to get some uploaded soon to show a couple of good examples.

Let's say you have a chart and you show it in SharePoint using an Excel Web Access web part. Depending on the chart, it may show up looking the same as what it does in Excel. If so, have a nice day and read no further.

If (and this is quite common) it looks nothing like it, there is almost embarrassingly simple way to solve this problem.

Step 1: Use an Image web part instead (after all, it is an image right?)

Step 2: Use a jpg file. (Use my Chart Tools to export it)

And you're done. If you need extra help, leave a comment.

Named Ranges and Excel Services

You can export (copy) a worksheet to make a new workbook. Right-click the sheet tab, select "Move or Copy...", then select "(new book)" from the dropdown at the top. Tick "Create a copy" if you want to retain the original sheet in the source workbook.



This is where it get’s fun. Let’s imagine if your workbook has several named ranges that use the same name on different worksheets. Excel does allow you to do this, and even though it’s something that can cause confusion, it can be a good thing if used “wisely”.

The first name will be workbook-scoped by default, all subsequent names will be worksheet-scoped. If you export (copy) worksheets with worksheet-scoped named ranges, they will not magically change over to being workbook-scoped. They will remain worksheet-scoped, and what’s worse, you won’t be able to (manually) change them to be workbook-scoped. Perhaps this would not bother you normally, but if you were planning to use the named ranges of these new workbooks with Excel Services, you will run into trouble. This is because Excel Web Access web parts only work with workbook-scoped range names.

What to do? The simplest solution is to delete the names and replace them. This is what the code below does. First it exports each worksheet, then it loops though the named ranges. (Don’t use this for dynamic ranges – they won’t work with Excel Services anyway). While looping, the named range is selected, the name is deleted, then a new name is set for the selected range that is workbook-scoped. (It does not matter if the original named range happened to be workbook-scoped)

Finally the new workbook is published (saved) to a document library in SharePoint.

Sub ExportWorksheetsToSharePoint()
    Dim n As Name
    Dim ws As Worksheet
    Dim wbMain As Workbook
    Dim wbReport As Workbook
    Dim sRangeAddress As String
    Dim sName As String
    
    'Note: You can watch the macro progress in the Status Bar (at the bottom of Excel)
    
    With Application
        .ScreenUpdating = False
        .StatusBar = "Started..."
    End With
    
    Set wbMain = ActiveWorkbook
    
    For Each ws In wbMain.Worksheets
        
        With ws
            
            .Select
            .Copy
            
            Application.StatusBar = "Processing " & .Name & "..."
            
        End With
        
        Set wbReport = ActiveWorkbook
        
        With wbReport
            
            For Each n In .Names
                
                sRangeAddress = WorksheetFunction.Substitute(n.RefersToLocal, ActiveSheet.Name, "")
                sRangeAddress = WorksheetFunction.Substitute(sRangeAddress, "=", "")
                sRangeAddress = WorksheetFunction.Substitute(sRangeAddress, "!", "")
                sRangeAddress = WorksheetFunction.Substitute(sRangeAddress, "'", "")
                
                Range(sRangeAddress).Select
                
                sName = WorksheetFunction.Substitute(n.Name, ActiveSheet.Name, "")
                sName = WorksheetFunction.Substitute(sName, "'", "")
                sName = WorksheetFunction.Substitute(sName, "!", "")
                
                n.Delete
                
                Selection.Name = sName
                
            Next n
            
            .SaveAs "Your Document Library path/" & ws.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook
            
            .Close False
            
        End With
        
    Next ws
    
    Set wbMain = Nothing
    
    Set wbReport = Nothing
    
    With Application
        .StatusBar = "Finished!"
        .Wait (Now + TimeValue("0:00:01"))
        .StatusBar = False
        .ScreenUpdating = True
    End With
    
End Sub
Now you have each worksheet published as a workbook with all of the named ranges ready to be accessed by Excel Services. With versioning set up the right way in the document libraries, you can make changes to your main workbook, then run the code to update each “reporting” workbook. Notice how I use

Selection.Name = sName
to set the name again. I'm sure you will find that easier than using

ActiveWorkbook.Names.Add Name:="myName", RefersToR1C1:="=Sheet1!R1C1"