Level 400 - Expert

Recently I have been rummaging through the blogs over at MSDN and I just stumbled across what has to be one of the best Excel 2007 posts of the year!

“Microsoft Excel and Twitter via Smart Tags in a VS 2008 Solution”

Basically, John Durant (a Microsoftie) has built some code which enables him to use Smart Tags to tweet the contents of a cell with only a couple of clicks.

How cool is that!  If you are interested in extending the functionality of Excel 2007, make sure you read more in John’s post here.

And if you are on Twitter, why not follow @thenewpaperclip for regular Office 2007 hints, tips, tricks and tutorials.

(and if you are really bored, you could follow the guy who is behind The New Paperclip - @paulwoods)

‘till next time!
TNP

Have you or a creative genius close to you built an unreal shockwave/flash animation and you want to show the world?

Did you know that you can show off that animation using PowerPoint 2007?  Bet you didn’t.  That being said it isn’t the simplest of things you can do – but it does work.  Here is the step by step guide to doing it!

1) Open PowerPoint 2007 (obvious right!)

2) Make sure you can see the “Developer” tab in the “Ribbon”.  If you can’t, click on the Office menu (the circle button in the top left hand corner), then click on “PowerPoint Options”, and make sure that you check the “Show Developer tab in the Ribbon” box!

3) Go to the slide you want the .swf file to appear

4) Click on the “Developer” tab in the Ribbon

5) Look for the “More Controls” button in the controls group.  It is the one with a spanner and a hammer with three dots underneath it.  Once you find it – click on it

6) The “More Controls” box will appear.  Scroll down until you find “Shockwave Flash Object”.  Click on that, then click “OK”

7) You will notice your cursor is now a cross, and no longer an arrow.  That means you can now draw where you want your Shockwave/Flash file to appear on your slide.  Simply draw with your mouse, and use the adjustment handles on the box to make sure it is just the right size and position on your slide.  When you are done you should see a wireframe box with a big “X” through the middle of it

Now we want to link your .swf file into your PowerPoint 2007 presentation.  Click on the box you just drew, then right click, and select “Properties”

9) In the properties box that appears, look for the “Movie” property, and then add the location of your .swf file (for example c:\temp\test.swf)

10) Quickly run your presentation and watch in awe as your shockwave file hits the big screen in your PowerPoint 2007 presentation

So how did you go?  Sure it is a little complicated to get there, but boy does it look good once you have that file playing through PowerPoint.

Just a word of warning though – this only LINKS to the .swf file, so if you email or share the presentation with others, chances are the link will break and you will need to go through the above process again to re-link the PowerPoint deck to the .swf

That being said… it is well worth the effort!

‘till next time!
TNP

So… you have spent hours collating your data in Excel 2007.  You have finally got the information where you want it – with sub-totals and all.  You collapse the outline to only show the sub-totals, because that is all you are interested in.  But when you try to copy those subtotals into another spreadsheet, it copies EVERY row, not just the sub-totals.

So how on Earth are you supposed to copy all those sub-totals in Excel 2007 without copying all the ‘hidden’ rows as well?

The problem is that when you select rows using the sub-total outline view, it is selecting every row, not just those sub-totals.  The way to solve this is to only select the visible cells – which means we have to use a feature of Excel called “Go To Special”.  Here is how to do it:

1) Select all your sub-totals that you want to copy

2) On the “Home” tab of the Ribbon, look for the “Editing” group – usually it is the last one, all the way over at the right hand side of the Ribbon.

3) Click on “Find & Select”

4) On the little menu that appears, click “Go To Special”

5) In the box that appears, make sure you select “Visible Cells Only” in the right hand column

6) Click OK

7) Copy using the menu (or ctrl+c) and then paste your sub-totals in your new spreadsheet

You can stop pulling your hair out now!

‘till next time!
TNP

Has this ever happened to you?

You morning starts off great.  You beat all the traffic, get into the office precisely at the time you are supposed to start, and enjoy the best cup of coffee you have had in months.

You open up Outlook, and put that appointment in your calendar that you had thought of on the way into office.

But then something horrific happens.  When you go to check your email, there is a new meeting request in there – for the exact same time you just set the appointment in your calendar.  What are you to do????

…

Ok… I might be getting a little dramatic here, but this has happened to me a couple of times over the last few months – and today thanks to my friend Alistair Speirs (Australia’s premier Microsoft Office Client Technology Specialist – try Googling/Live Searching that now Al!), I have found a way to avoid this exact situation in the future.

There is a command line switch for Outlook 2007 which will start Outlook, force detection of all the new meeting requests that people sneakily invited you too overnight, and push them straight into your calendar before you get a chance to look at it.

The outcome being that you have a clear and accurate picture of how your time will be used today – before you start digging into your email.

And the best part is, the switch has a cool name… /sniff

For those that don’t know how to use switches, my advice would be to visit the Microsoft Office Online page that talks about Command-line switches for Microsoft Office Outlook 2007.

And if you want to take a look at some other switches that will make you more productive – check out the original post on Alistair’s blog.

‘till next time!
TNP

Are you going to be somewhere near Sydney, Australia in mid November?

If you are, and are interested in all things Office, make sure you get to OfficeDevCon08!

From the blurb… “OfficeDevCon is the first Australian community-driven event aimed squarely at Microsoft Office developers and power-users.  It allows people with interests across the Microsoft Office platform come together in one place to network and hear expert presenters speak on a wide range of advanced Office-related topics”

Well guess what… one of those expert presenters will be your’s truly… The New Paperclip.

In fact, in reality it will be my alter-ego… the guy behind The New Paperclip… Paul Woods

Paul will be presenting TWO sessions that should not be missed - especially if you work in Corporate IT.  Here is a quick outline of each presentation…

“The Office Suite Smackdown”
Saturday 15 November - 3:00-3:50PM

Office might seem like the obvious choice… but recently the competition has heated up for desktop productivity suites.  Your manager might be asking about alternatives like Google Docs, Open Office, Corel Wordperfect or Zoho Office.  In this session we compare the alternatives to Office 2007 from a users point of view - to help you pick the right tool for your organisation.

“The 10 most common Office 2007 user frustrations… and how to avoid them”
Sunday 16 November - 9:00-9:50AM

Don’t want your whole organisation angry with you because you forced them to use this horrible steaming pile of Office 2007 software?  It is a lot easier than you think to ensure a smooth and pain free transition to Office 2007 - it isn’t about buying 400 page dummies books, or just pointing people to the help button.  Simply by making sure you anticipate the most common questions up front you can win most of your potential battles.  In this session we will look at the most searched for answers by hundreds of thousands of frustrated Office 2007 users at http://www.thenewpaperclip.com… and see how you can ensure your people don’t end up asking YOU those questions.

So what do I need to do to attend these presentations?

Simple.  Go to http://www.officedevcon.com.au/ and register your spot.  Tell them that TNP sent you!  And make sure you catch up with me at the bar on Saturday afternoon!

’till next time!
TNP

I was surfing the net tonight, and totally out of left field I found an article on OfficeLabs.com that I thought you would love to read.

Did you know that you can use your Nintentdo Wii Controller to control your PowerPoint 2007 presentations?

It is a little tricky to set up, but once it is you simply need to flick your Wiimote to the left or to the right to control your slides.  HOW COOL IS THAT!

You can read more about it on OffliceLabs.com

’till next time!
TNP

Have you ever wanted to display what the current time was in a PowerPoint presentation.  Not just the time you opened the presentation… but a real-time view of what the time is?

For example, you might be putting together some slides for an event or business seminar, and you would like to display something at the start of the presentation that says when the session will start, and what the current time is (like the slide above).

Well I thought it wasn’t possible… until today that is!

It is actually really easy to do.  But first you need to download an add-in for PowerPoint 2007.

1) Make sure that PowerPoint 2007 is not open
2) Go to http://officeone.mvps.org/autodatetime/autodatetime.html and download the Office One Auto Date Time 2.3 Add-in for PowerPoint
3) Run the file you just downloaded
4) Start PowerPoint 2007

So that is the first part out of the way, setting up PowerPoint 2007 to display the current time.  The next step is to enable the add-in.

1) Select the “Add-Ins” tab in the ribbon, and look for the “Menu Commands” Group
2) Click on “AutoDateTime”
3) Check the “Enable AutoDateTime” box
4) Type in how you want the date or time using the legend on the dialog box
4) Click OK

So what if you want to include the current time on every slide in PowerPoint 2007?

1) Click on the “View” tab
2) Look for the “Presentation Views” group and click on “Slide Master”
3) Go to the first master slide
4) Click on the “Insert” tab in the ribbon
5) Look for the “Text” group, and click on “Date & Time”
6) Check the “Date and Time” box
7) Click “Apply to All”
Move the text box around to where you want it, and change the font as you like
9) Click on the “Slide Master” tab in the ribbon, and then click on “Close Master View”

It is even easier if you just want to include the current time on a single slide (like in the example above)

1) Click on the “Insert” tab in the ribbon
2) Look for the “Text” group, and click on “Date & Time”
3) Check the “Date and Time” box
4) Click “Apply to All”
5) Move the text box around to where you want it, and change the font as you like

If you want more detail on how to use the OfficeOne AutoDateTime 2.3 Add-in, check out the detailed instructions on the AutoDateTime website.

’till next time!
TNP

Are you an Office freak from down-under?

On the weekend of the 15th and 16th of November, a group of Aussie Office fanatics are putting together a conference called Office DevCon2008.  From what is on the agenda it looks like a great event for anyone involved in managing Office products in their workplace, or for those interested in getting a little more intimate with the Office 2007 suite, SharePoint and more.

Registration is free… and there might be sneak previews of Office 14 as well!

If you are in Sydney in mid November, make sure you check it out.  You never know, you might bump into yours truly there as well

You can find out more by visiting the Office DevCon 2008 website

’till next time!
TNP

In my day job (yeah.. this Paperclip can’t live off giving you Office 2007 help yet!) I always got frustrated when I tried to check my corporate email from home or on the road.  My office is essentially my laptop, and wether I am on my ADSL connection at home, or on my 3G Wireless connection, whenever I wanted to check my email using Outlook, for security reasons (I am guessing) I needed to connect to work through a Virtual Private Network (VPN).  Very annoying!

But then I discovered a great way to get around that!  It is called "Outlook Anywhere" and is probably the hardest option to find and change in Outlook 2007!

For those interested in the details (from a popup box in Outlook 2007)… "Microsoft Office Outlook can communicate with Microsoft Exchange over the Internet by nesting Remote Procedure Calls (RPC) within HTTP packets." 

Woooo too technical for me… but what that basically means is that you don’t need a VPN connection, you just need access to the internet!  How good is that.

Here is how to do it.

1. Click on "Tools", then "Options"
2. Click on the "Mail Setup" Tab
3. Click on the "E-mail Accounts…" button
4. On the "Email" tab, click on your email account, and then click "Change"
5. Click on "More Settings"
6. Click on the "Connection" tab
7. In the "Outlook Anywhere" section, check the box that says "Connect to Microsoft Exchange using HTTP"
8. Click on "Exchange Proxy Settings…"
9. Type your companies "Outlook Web Access" address in the "Connection Settings" box.  For example, it could be owa.companyname.com
10. Click "OK" about 15 times to close all the windows
11. Restart Outlook

And there you go - probably the most complicated menu structure in Outlook 2007, but when you find it, it is absolute GOLD!

Now go out and enjoy that extra 30 seconds I have given you every time you want to check your email!

’till next time!
TNP

Are you sending something to someone that you don’t want anyone else to see?  Maybe you are talking about your boss behind their back, sending a friend some photos that you don’t want the rest of the office to see… or simply don’t want someone to hit Reply-All on your email.

Well there is a way to turn off Forward and Reply-All.  Yes really!

First things first - this will only work if the person is using Microsoft Office Outlook 2007.  If the person you are sending the email to uses Outlook Express or Outlook Web Access… then you are out of luck

Want to learn how to do it?  Simply have a read of this article on Microsoft Office Online.

(Thanks to the Inside Office Online blog for pointing this great tip out!)

’till next time!
TNP