Panuy

March 31, 2009

Why Not OpenOffice Calc?

Filed under: Uncategorized — panuy @ 7:24 am

I’m still working with microsoft’s Excel for few reasons. I actually started spreadsheeting in the 80s with Lotus 1-2-3, then Quattro, Quattro-Pro, Excel 5, Gnumeric, tested Calc, but now – Excel 2003.

Why Excel? Because

  • There’s no way to configure OpenOffce Calc to add the current date & time in a single keystroke.
    In excel it’s being done by Ctrl-; but Calc doesn’t have this feature. I tried to build a macro for it, but it was so much more complicated (see below).
  • Excel has a bit better encryption method.
    yes, i know they are all weak, but it’d make an attacker’s life a bit more complicated
  • VBA macros are simpler to learn than OpenOffice’s.
    I’m a VBA heavy user, and until someone simplifies OO-Basic, or incorporate Python as debuggable, editable, recordable built-in Macro language, i’ll stay with VBA.
  • I got used to separate arguments with commas (,). Calc uses semicolon, and i’m very annoyed by this non-standard way of doing things.

So Why Not Excel?

  • It costs lots of money, and i have lots of computers i’m using
  • It’s as portable as the Statue of Liberty. I prefer portable apps!
  • THe new version’s human interface isn’t usable, isn’t meant for power users, reource-hungry, and doesn’t include previous features!
  • It’s microsoft, and I’m against monopoly of any kind. At least in theory :)
  • Gnumeric is really cute, and most of the bugs are solved by now
  • Excel works only on Windows, and not even all versions! Gnumeric works on my Zaurus and on my Linux. Quickoffice works on my phone.
  • It’s not open-source.

Your comment is welcome.

Excel Standby Failure – A Workaround

Filed under: Uncategorized — panuy @ 5:54 am

When I Close my Laptop’s Lid,

it’s going into Standby mode. Well, it is supposed to. But since I have an Excel file constantly open (why not OpenOffice Calc?), it doesn’t go StandBy; instead, it just pops up a stupid modal MsgBox saying:

Windows cannot go on standby because Microsoft Office documents or application components are being accessed from the network. You must close the open documents or exit the applications before you can put the computer on standby.

This is a known bug in office suite which happens when Excel is having an open document on a non-fixed media like usb sticks or encrypted drives.

The result of the bug is a depleted battery, plus an ugly MsgBox to click on.

Non-Working Solutions:

The following didn’t work for me, but they might work for you.

  • Changing the setting in the control panel’s Power Options.
    it didn’t work, since the only options allowed by Microsoft are “hibernate” or “standby”, or “do nothing”. Hmmm.
  • Looking for the lid button driver’s options.
    My current laptop is Fujitsu Lifebook u810 running winXP. I couldn’t find a way to intercept the lid-closure process. YMMV
  • intercepting the standby windows messages.
    that one’s quite a big black hole.

    • Here are my findings as posted in Stack Overflow (to which no-one answered yet)
      1. close the notebook’s lid
      2. Windows send each running process the WM_POWERBROADCAST: PBT_APMQUERYSUSPEND (i traced them using SPYXX.EXE)
      3. Each program answers “True”,
      4. … until Excel answers “False”, and the whole process stops.
      5. It theoretically broadcasts also PBT_APMQUERYSTANDBYFAILED to whom it may concern
    • I wrote a Python script using WMI and pywin32 modules to intercept the messages. It basically works.
      However, when Excel is running, the python script didn’t receive the desired events (APM failure or query) !!

      	Import wmi
      	#...
      	query = "SELECT * FROM Win32_PowerManagementEvent"
      	power_watcher = wmi.ExecNotificationQuery ( query )
      	power_event = power_watcher.NextEvent ()
      	print power_event
    • I found a VBScript snippet that does the same thing, and got similar results.
    • There are several APM open/closed source utilities which aim to log and interfere with APM messages and actions, like MonitorState.exe, PMMon.exe, etc..  They all failed at knowing when the lid closes
    • I assume Excel just “swallowed” the message something queue something Melkovitch melkovitch.
      But since It didn’t matter if I ran my message intercepting scripts before or after Excel, this theory as falsified.
  • I tried to use SPYXX to trace the messages sent by the lid close action, found nothing useful.
  • I tried to remap the keyboard driver, hopefully replace “sleep” button with anything else. I used KeyTweak (which works fine for the horrible u810 keyboard), but that didn’t change the lid behavior.
  • next thing was to run a keyboard macro program… wait!

The Solution

I downloaded AutoIt automation software, and wrote a script which:

  1. catches Excel’s MsgBox.
  2. Launches sysInternal’s psshutdown.exe which forces the computer to standby no matter what,
  3. closes the annoying MsgBox
  4. GOTO 1

And it works! CPU usage had to be optimized considerably, since the default window locating method uses wildcards and is extremely inefficient. Then I compiled it to EXE, and it runs in the background, works like a daemon (pun intended: it’s a Hebrew phrase meaning “Lean and mean”).

Here’s my AutoIt script…

Opt("WinWaitDelay",400)
; -- exact text match, to save LOTS of cup cycles!
Opt("WinTitleMatchMode",3)
Opt("WinDetectHiddenText",1)
Opt("MouseCoordMode",0)
; Opt("WinSearchChildren",1)
dim $title = "Microsoft Excel"
dim $text = "Windows cannot go on standby because Microsoft Office documents or application components are being accessed from the network. You must close the open documents or exit the applications before you can put the computer on standby."
While True
     ; wait for excel's error msg
     WinWait($title, $text)
     Run("psshutdown.exe -c -d -accepteula -m mooshmoosh -t 5")
     ; the annoying msgbox doesn't close without the 'sleep'
     Sleep(1000)
     ; close the annoying modal msgbox!
     WinClose($title)
     ;1 minute delay, save cpu (?)
     Sleep(1*60*1000)
WEnd
It's cool, it's an ugly solution, I hate smurfs, and I enjoyed it a lot.

I’m still curious though,

why the Python program (and others) didn’t catch all APM events like ‘query’ and ‘failure’ while excel was running. If you know the answer, please post it here. Thanks.

This is the non-working solution:

import win32com.client
import time,os

logfile = 'hook.log'
msgs = {
    0x0000:'PBT_APMQUERYSUSPEND'  ,
    0x0001:'PBT_APMQUERYSTANDBY'  ,
    0x0002:'PBT_APMQUERYSUSPENDFAILED'  ,
    0x0003:'PBT_APMQUERYSTANDBYFAILED'  ,
    0x0004:'PBT_APMSUSPEND'  ,
    0x0005:'PBT_APMSTANDBY'  ,
    0x0006:'PBT_APMRESUMECRITICAL'  ,
    0x0007:'PBT_APMRESUMESUSPEND'  ,
    0x0008:'PBT_APMRESUMESTANDBY'  ,
    0x0009:'PBT_APMBATTERYLOW'  ,
    0x000A:'PBT_APMPOWERSTATUSCHANGE'  ,
    0x000B:'PBT_APMOEMEVENT'  ,
    0x0012:'PBT_APMRESUMEAUTOMATIC'
}

while True:
    wmi = win32com.client.GetObject ("winmgmts:")
    query = "SELECT * FROM Win32_PowerManagementEvent"
    power_watcher = wmi.ExecNotificationQuery ( query )
    power_event = power_watcher.NextEvent ()

    msg = '[none]'
    event_type = power_event.Properties_ ("EventType").Value
    if event_type == 4:
        cmd = 'psshutdown.exe -c –d -m yalla -t 12'
        os.system(cmd)
    event = msgs[ event_type ]
    msg = "%s\t%d\t%d\n" % (time.asctime(), event_type, event)
    open(logfile,'a').write(msg)

The Shocking Blue Green Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.