Chatter Of The Squirrel

Website using Drupal with PEAR extensions for users to export class schedules into an Excel file.

Certain class schedules won’t export. Throwing an error like:

Fatal error:  Call to undefined method PEAR_Error::write() in /opt/lampp/htdocs/xxx/xxx/xxx.module on line 166

The line of code was trying to write some stuff into a field.

$worksheet[$i]->write(0, 0, $group, $info);

I first thought special characters were causing the problems. Since it’s only making a mess on schedules with parentheses in the fields. Used preg_replace() to sanitize the necessary. Still the same error.

Turns out, the problem is this. This is a 5 year old bug, and I dismissed it when Google threw this result at me. Thought the PEAR would’ve been sorted out ages ago. And there wasn’t an issue with my worksheet names.

Seems that the problem was never resolved. There’s a limitation in the length of an Excel worksheet name. The PEAR is aware of this. And how it handles the error, is to crash routine, and throw you a cryptic error.

Thus, it’s as easy as rewriting line 325 and line 333 of /PEAR/Spreadsheet/Excel/Writer/workbook.php

Around line 325 and 333, just add the following respectively:

$name = substr( $name, 0, 30 );

$name = substr( $name, 0, 27 ) . ($index+1);

To make sure the worksheet name is not over 30 characters long, and is not repeated. Then everything works. Again.

Synopsis: IT IS TERRIBLE!!! DON’T DO IT!!! Google for a solution using OpenOffice!!! GET OUT WHILE YOU STILL CAN!

The adventure started easy enough… We are migrating about ten thousand students over to a new email system. There’s this Live@Edu email system that someone keeps selling to management, and I can’t avoid it.

I jumped all the hoops and have created those ten-thousand mailboxes in the MS Exchange server. It’s kinda cool that I can do some management via PowerShell2. It’s painful to learn, but most useful when management suddenly changes their mind and decides that student mailboxes should not have GAL (Global Address List), and I needed to effect this change to mailboxes I’ve already created…

So I’ve got all those mailboxes ready, I figured I could just prepare an Excel sheet with all the student names, current email addresses, email password etc and just mail-merge the new email login/passwords to everybody. Job done. Easy?

Apparently not…

Managed to import a CSV file into the recipient list. Filter it to only send where “EMAIL” field is not blank. That’s kinda nifty. Got the form letter typed out nice and good.

Attempt #1, the “Finish & Merge” is completely gray-ed out. Or, I can set the merge to print, but NOT to Email.

Cause: My default email client is Thunderbird, like most sane people. Word 2007 attempts to merge out to Thunderbird, and gets stuck. Without showing any hanging dialog boxes or any indication that it’s stuck.

Solution: Set Outlook 2007 as the default mail client. Close all Office applications. Then reload the Word mail merge document and re-do the shit. And finally, the “Send Email Messages…” at Finish & Merge is no longer gray-ed out.

Attempt #2, Outlook now starts throwing up a dialog box, warning you that a software is trying to send an email, and asks you to confirm that you want to do this, and make you click an “Allow” button. After a 5 sec mandatory pause. OK, I can appreciate this ‘feature’. You don’t want innocent machines to be infected by malware and become inadvertent spam relays. Right. That’s like GM designing a car with SQUARE tyres, because round tyres make the car move too fast and causes accidents.

I obviously can’t sit there and click a button every 5sec ten-thousand times. So there must be a way to stop it from doing that.

Most obvious setting to fix, would be in Outlook -> Tools -> Trust Center -> Programmatic Access

Maybe I can set it to “Never warn me…” but the options are gray-ed out. I was using Vista. It’s fine on a WinXP machine though. But even setting it to “Never warn” on the WinXP doesn’t make this dialog disappear.

And enabling the gray-ed out settings in Vista was all but impossible.

Figured it’ld be some kind of policy settings. Found this to maybe be relevant… Downloaded the 2007 Office System Administrative Templates. Did some random shit or another to install those templates. Look for relevant settings and fiddled with some. Mostly to use the admin templates to override the Programmatic Access in MS Outlook.

Still didn’t work.

Rebooted.

No work.

Gave up. Left it over the weekend. Came back to it 4 days later.

And it works.

Somehow, I can now Mail out without the stupid interference.

But…

The Outlook.com server bounces the 501st email that I send out. There’s a 500 mail limit per user account per dunno how many hours.

Solution: created 20 email accounts to shoot out 10k worth of emails.

So. Much. Fun.

Very useful plug-in.

Setting up a blog to act as a collective brain for the department. Thus thought of using WordPress and then use a plug-in to make it private so that only those who are able to log in can view the pages.

Very easy to set up. And just works.