Andrew's Excel Tips

Charts and Excel Services 2

noreply@blogger.com (Andrew) — Sun, 11 Dec 2011 04:34:00 +0000

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.

AET Chart Tools Update

noreply@blogger.com (Andrew) — Sun, 11 Dec 2011 04:26:00 +0000

I've made an update to AET Chart Tools.

The menu items are now available on the "regular" Tools menu and "chart" version of the Tools menu (this is activated when you have a chart selected), both found on the menu at the top of Excel.

Something I overlooked - please go to this previous post to see what these tools can do.

Charts and Excel Services

noreply@blogger.com (Andrew) — Sun, 23 Oct 2011 04:02:00 +0000

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.

Random Data Generator

noreply@blogger.com (Andrew) — Sun, 02 Oct 2011 02:14:00 +0000

This week I have been looking at the Random Data Generator made by Jimmy Peña from Code For Excel And Outlook.

It allows you to quickly generate fake and random data according to your specific requirements. So if you wanted some data to show the payroll list or sales of a fictious company, there's no need to hurt your head thinking up details - you can use the Random Data Generator to select the fields you want and get it at the click of a button.

Here's a pic. You can use the horizontal arrow keys to add/remove fields and the vertical arrow keys to sort the order to show them. (You can also double-click on the fields to add/remove them). I've entered in 100 in the Quantity text box to generate 100 rows of data.

And here are the results.

Simple!

You can also choose a variety of formats to export the data.

My only beef is that I did not think of it first! But jokes aside, thanks Jimmy for something that saves a lot of time and hassle.

Named Ranges and Excel Services

noreply@blogger.com (Andrew) — Thu, 22 Sep 2011 02:00:00 +0000

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"

Financial Date Formulas

noreply@blogger.com (Andrew) — Sun, 11 Sep 2011 03:06:00 +0000

Here are some financial date formulas that may come in handy.

To get the financial quarter use

=CHOOSE(INT((MONTH(A1)+2)/3),3,4,1,2)

To get the financial year use

=IF(MONTH(A1)<7,YEAR(A1)-1 &"-"& YEAR(A1),YEAR(A1) &"-"&YEAR(A1)+1)

Let me know if you have any alternatives.

Macro Comments v2

noreply@blogger.com (Andrew) — Sun, 04 Sep 2011 00:41:00 +0000

Back in 2007, I made an add-in called Macro Comments. I've been meaning to update it for a while now. Something I found though is that's it not that easy programming the VBE - a lot of care and experimentation is required to make sure things work right.

Anyway, here is the new version of Macro Comments at last. It allows you to add, replace or delete comments under Sub or Function names.

These are some of the features,

You can work with either the entire project or individual modules
Mutli-line comments are okay
Comment text is saved each time (so if you use generic comments this might you save a lot if time!)

Here's a couple of screenshots

Adding macro comments

The comments are added!

I've tested in Excel 2007 and Excel 2010. Let me know if you spot any bugs.

The download link is here.

By the way, you can download an improved version of my VBA Code Indenter is also available here. While I think of it, anyone want to help me make a Word version? (and for any other Office application that uses VBA?)

For both Macro Comments and VBA Code Indenter, the password is "password" (That's the only I could remember it!)

When should I use a chart?

noreply@blogger.com (Andrew) — Sun, 28 Aug 2011 11:00:00 +0000

There is no easy answer for this question. But, first and foremost I think charts should show something not easily recognisable just from looking at raw data. (Not just exist because throwing in a chart makes things look pretty)

Examples include trends over time, largest vs. smallest, frequency and comparisons of performance.

All of the above hint at a significant range of data. I'm think the smaller the range, the simpler the chart. And vice versa.

Your thoughts?

What's wrong with this chart?

noreply@blogger.com (Andrew) — Tue, 23 Aug 2011 12:39:00 +0000

Well, what do you think? I'll tell you what I think in a day or two. (see below)

Update: 24th August
Wow, I was a little surprised and very happy to see so many comments. (I'll be honest, I was hoping to get one or two at the most!) I'm guessing everyone likes a challenge, and those who like charts especially so.

And I was doubly surprised that so many commenters picked up on what I was trying to convey, even with such deliberately little to go on. The problem, as I see it, is not the chart so much but it's reason for existence.

I was tempted to name the post "Charts, who needs them?" and this probably is not such a bad idea. What's motivated me to write this post is something I see on a regular basis, something I call "charts for no reason". Let's face it, the two values speak for themselves. A chart in this case adds little to no value.

Of course, this is my own opinion and not written in stone. But it seems that some agree and that is reassuring. Of course, I'm just as happy to hear from you who think differently so don't hold back (as if I had to tell you, right?)

Next time, I'll be looking at when a chart is a good idea and how to go about it.

PS. Thanks for the technical tips too. They're all valid and very much appreciated! Maybe it would be fun to have something like "Rate my chart". I know other bloggers do it but why not me too? :-)

PPS. I'm going to set comments to no moderation on a trial basis and see how things go.

AET Chart Tools

noreply@blogger.com (Andrew) — Mon, 15 Aug 2011 09:18:00 +0000

When working with lots of charts in a workbook, you can quickly find yourself overwhelmed. AET Chart Tools make working with charts a lot easier.

Problem 1
I have so many charts, I don't what sheets have which charts or where they are on the sheet. Scrolling around is driving me crazy!

Solution - Use the Chart Selecter tool
The Chart Selecter tool allows you see all charts in the active workbook and select them without having to scroll to where they are.

Problem 2
I have to reset the ranges or labels in a lot of charts. Not only is it time consuming, it's also prone to user error since it's such a boring and repetitive task.

Solution - Use the Series Find and Replace tool
It helps you find and replace in series values in the active chart, all charts on the active sheet, or the entire workbook.

Problem 3

I have so many charts, with so many series. Sometimes the chart type and series chart type are different, some have a secondary axis, etc, etc. I wish there was a way I could see the details of all the charts I'm working with.

Solution - Use the Workbook Chart Report
It tells you details of all charts in the workbook. Information includes the Sheet Name, Chart Name, Chart Type, Series Name, Series Formula (range references and series number), Series Axis Type and Series Chart Type.

Problem 4
All of the tools above sound good. But my workbook is so slow and it takes so long for my charts to render. Is there a faster way to see them?

Solution - Export the charts as images
The Export Charts as Pictures tool allows you to export whatever charts you want from the active workbook as GIF, JPG or PNG files. Using Preview on Windows Explorer right-click menu, you can browse through the charts that you exported using the navigation arrow buttons at the bottom. This is also a great way to keep a backup of your charts so you know what they looked like before you edited them.

You can download AET Chart Tools here!

Welcome

noreply@blogger.com (Andrew) — Sun, 14 Aug 2011 17:47:00 +0000

Welcome to my new blog.

Why the change?

Well, my old blog site was getting very tired. Comments were disabled due to the inability to fight spam effectively and I missed getting feedback.

After looking at some other blog companies, I decided Blogger was the best for what I wanted. Some editing of the CSS and the odd tweak here and there - and a new blog is born.

Now all it needs is some content, and I think you will like some of the stuff that is coming up...

There are some major changes coming to my site and some new add-ins will be available for download soon.

And I'll be looking more at the analysis side of things - data presentation, charts, design and best practice, something I've not blogged about much before - it's time to become a little more serious.

Stay tuned!

Andrew