Updating Page Sizes

This drives me crazy. I'm not sure if it was the move to Excel 2013, or if it was something else…

I have this massive model, and we print it out on 11x17 paper. Somewhere, sometime, Excel decided that this is a "Custom page size", which causes me problems. I need to reset all the pages to 11x17. Easy right? Select all the worksheets, go to Page Layout à Size and choose 11x17.

Not so fast… if you do that, it replicates ALL the print settings including orientation, margins, fit to x pages by x pages, etc.. Nasty stuff. You can actually see why when you record a macro to change the paper size. This is what I get:

[code] Sub Macro1()
' Macro1 Macro
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$8"
.PrintTitleColumns = ""
End With

Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = "Printed &D &T"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.7)
.RightMargin = Application.InchesToPoints(0.7)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaper11x17
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With

Application.PrintCommunication = True

End Sub[/code]

This is what I need:

[code]Sub Macro1()
With ActiveSheet.PageSetup
.PaperSize = xlPaper11x17
End With
End Sub[/code]

Actually, even more pointed, this will fix it without messing all my other settings up:

[code] Sub Macro1()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.PageSetup.PaperSize = xlPaper11x17
Next ws

End Sub[/code]

I know why the code records as it does, as Excel doesn't know what settings I truly need, so it records the current state of all PageSetup stuff. But I sure wish when I tried to update one print setting via the user interface that it gave me the ONE I changed, not everything.

A successful weekend

This past weekend was a good one for Excelguru.

On Thursday evening we travelled over to Vancouver and stayed at the Westin Grand, downtown. I have to say that I quite enjoy the Westin hotels, and this one was no exception. Exceptional service, clean and comfortable room, and central to everywhere that we needed to be, which was awesome.

Friday morning we were up and off to Blink Media Works to shoot 3 more Excel videos for the Certified General Accountant's PD Net. I shoot all the Excel specific on-screen footage in the Excelguru offices, but I'm on screen in full HD when going through PowerPoint slides doing introductions and wrap-ups for each of the examples. It's always an interesting thing being on camera, as it's not forgiving at all. Unlike a live audience where, if you make a mistake you can just laugh it off, you can't do that. Naturally, with 3 videos being done, there's no way to memorize all the material (it would detract from the "realness" even if we did, I think), so I've got bullet point notes for each slide. Forgot one, reshoot. Forget the points you want to make, reshoot. Stumble over a couple of words, reshoot. It's actually pretty hard work!

Regardless, we managed to get through all 3 in one day, which was awesome. In my impression it was the most comfortable, relaxed and on-key shoot we've had yet. What I didn't count on though, was how dehydrated I felt after the shoot was over. Even though I was drinking water all day, I guess the lights and talking cause you to lose a LOT of fluids. (I pretty much spent most of Friday night pounding down the water to get re-hydrated for Saturday's live course.)

When we broke out of the shoot the sun was shining, so we decided to walk back to the hotel. That was a really nice way to stretch the legs, get a little exercise and chat about the experience. We also took the opportunity of being right downtown to walk to the Keg in Yaletown for dinner. This may seem weird, but things aren't all laid out in walking distance in Nanaimo, so this was a bit of a novelty to us.

Saturday morning was a bit drizzly, so we cabbed it down to the Terminal City Club for that morning's session on Data Cleanup and Summarization, hosted by the Certified Management Accountants. I always enjoy teaching there too. The staff are fantastic and easy to work with, the facilities are always impeccable, and it just feels good to be in such a classy place. J

I had ten people for the course on Saturday, who seemed to enjoy the material. As I normally plan my hands on courses for about 20, we ended up going off topic a few times, as we had time to follow some topics I hadn't planned on covering. We also ended up with about 45 minutes of great questions and discussions at the end of the session as well. I always love that, as it really makes me think and approach things from angles I might not be used to. The best part of teaching any of these sessions though, bar none, is when the attendees leave excited about Excel and wanting to learn more about different areas that they haven't worked with. Very cool stuff!

About a week before the session, a colleague had also invited us out to lunch. I'd never actually met Tony before, so it was great to go and chat with him.

Tony and I have very similar backgrounds as we're both Certified Management Accountants, and we're both very solidly branched into the IT field as well. Our discussions primarily revolved around how integrated the roles of IT and accounting are becoming, yet how most people don't seem to recognize that combined value for some reason. When you see job postings it's generally for an accountant OR a systems person, but not a hybrid of both. While that may make sense for large businesses, especially since both roles are complicated and technical in their own ways, for a small business it's important to have an IT person that understands the impact of their decisions on the overall business process and cycle, and an accountant that understands the benefits and drawbacks of the current and future IT enhancement plans.

Having said that, if you're looking for IT work, and want it done by someone who understands the overall impact to your bottom line, Tony runs a company called Abakox Solutions Inc, and that's what they do. J

All in all, it was a great weekend. I met a lot of great people and got to talk about Excel in depth. Tough to beat that!

I hope your weekend was a good to you!

A new article, a new site host, a Facebook contest, an old blog…

It's been crazy busy over here the past couple of weeks.  I've been hard at work creating 3 training videos for a client, (we shoot in Studio on Friday,) preparing for my next live course (this Saturday,) and trying to keep the forum posts all answered... among other things...

Excelguru has a new host

The biggest news, as far as the site is concerned though, is that we're running on a new host.  My former host kept sending me messages about the server usage, asking me to block users and shut things down, as I was too busy.  They suggested that, if I didn't want to do that, I should upgrade to their dedicated server plan.  I have to say I was a little shocked by that, especially when they told me it would move me from $4.95 per month to $164.95 per month.  While I love hosting the forums and site, that was a little much to swallow.

Instead, after some research and conversations with my friend Simon, I've moved the site to host with URLJet, a company that specializes in vBulletin and WordPress hosting.  They're more expensive than what I was paying, certainly, but what a difference in service.  I signed up and within 10 minutes they called me to talk through the migration scenarios.  The site migration was finished, tested and working within 4 hours, all done by them.  They reviewd it, made some performance tuning changes, and recommended a few for me to do too.  Very cool, and all done at no extra cost.  I have to say that so far I'm really pleased with them.

We've been running on URLJet for a week now, and the site seems more responsive and forum posts are picking up.  I'm not sure if that's coincidence (probably), but it's getting to the point where I could certainly use more people on the site.  So if you love Excel or just want to learn, we have posts that need answering!  🙂

The old blog is back!

As if moving my site and optimizing it for me weren't enough already, URLJet also fixed the WordPress blog that I've been locked out of for 1.5 years too!  While I was planning on using the vBulletin blog, WordPress is way more flexible, and I didn't want to lose all the old content so...

With this blog back live, I'm going to resume posting here.  I ported the two articles back from the vBulletin blog that were worth saving, so if you see those in your feed, just ignore them.  🙂

A new article

In celebration of the whole thing, I also created a nice new article on the site as well.  If you've ever been confused about how conditional formatting rules are applied in Excel 2007+, I've written up a VERY detailed, step by step article that will walk you through exactly what happens.  You can find that by clicking here.

Facebook contest

My facebook page at http://www.facebook.com/xlguru is creeping ever closer to 100 likes.  I'd really like to make that milestone, so I announced last week that I'd give away a copy of my Magic of PivotTables video course to the 100th like.

I have to admit that I was hoping I'd be there by now, but then I got the following comment from Rick Grantham on Twitter:

"guess I need to unlike your FB page so I can like it back at the right moment.  Maybe I shouldn't have told you that :)"

I had a good laugh there, but it did make me wonder... so I think I'll change the game up.  (All those of you who are already Facebook fans can thank Rick (@BIStrategyGuy) for this....)  Here's the new deal:

Be one of the first 100 likes on the xlguru page for a chance to win a free copy of my Magic of PivotTables video course!

Once I hit 100 likes, I'll draw a random name from the first 100 people, and then I'll send you a discount code to download a free copy.  It's just that easy.  🙂

See, now no reason to wait.  In fact, you might want to hurry, as there are 97 likes as I post this!  (But don't worry, this won't be the last one.)