Category Archives: Office

Backup Utilities (O the joy!)

0
Posted by admin on May 10, 2007 – 3:21 am
Filed under Misc, Office

I’m a bit of a toolhound, both of the physical variety (like screwdrivers, bitstops, routers and mitre saws) and of the more ethereal (clipboard loggers and macro recorders). Tools make the fun things even more fun, and the wretched things not quite so onerous.

And one really onerous thing for me is backups. They’re a pain, and it seems it’s always impossible to find what you need when you need it. I’ve got a pile of QIC-60 and QIC-80 tapes that there’s no way I could restore even if I wanted to (note to self, burn them in the next trash fire…)

Anyway, I went on a search some time ago for a decent backup utility. Something free, or close, that was flexible enough for what I needed, simple enough that I’d actually use it, capable enough to make it worthwhile and fast enough to not get in the way.

I believe my search ended with FileBackPC.

It’s a nifty little app that does a nice job of the “copy to a floppy” type of backup. Well, ok, maybe not a floppy these days, but plug in a 500gb USB2.0 removeable harddrive and this app is fantastic.

The good points:

  • It backs up specified directories, with wildcards and all sorts of file filters
  • It can compress and retain a specified number of “previous versions”
  • You can specify sets of folders in “jobs” that can be run independently
  • You can set jobs to automatically run, either on a schedule or on “an event” (like plugging in that USB harddrive!)
  • It can reconnect to other machines on your network and back up files from the (great for small home offices with a server and several workstations or laptops).
  • It can even run batch files and scripts to automatically execute processes before backing up the results of those processes. For instance, I have an item that uses the windows “backup” utility to create a single backup file of the critical system components and my Exchange server data, then I backup that file automatically using FileBackPC.

Off hand, I can’t even think of any bad points, other than it’s not an open source project.

I think my favorite feature is the backup on an event. I set several jobs up to execute when the X: drive comes online (that’s my backup USB drive). So literally all I do now is plug in the USB drive, wait till it finishes the backup, and then unplug it and file it safely away. Slick. And it’s got a nice reporting facility too, that makes it easy to see if there were any problems (open files, read errors, whatever).

And, since it’s a copy style backup, it’s blindingly easy to find that backup file when you need it. Get a couple of USB drives and be doubly safe.

Backup utilities definitely aren’t my favorite things, but they’re something almost everyone with a PC needs. I know this has probably sounded more like an ad than anything else, but it’s not. I’m not getting paid by the FileBack people. This is one of those utilities that I’ve found so handy, useful and easy to work with, that I just felt like mentioning it.

Excel 2007, Defined Name Comments, and the null string malady

1
Posted by admin on May 2, 2007 – 5:36 pm
Filed under Metadata, Office

New to the Excel 2007 object model is the Comments property of the Name object.

The Name object itself has been around since Excel 2000 and possibly before, but the Comment property is a handy new development.

I like Comment properties personally. Any object model representing items such as this would benefit from a property where you can stash whatever info is pertinent. Take, for instance, the venerable tag property of VB controls. Of course, these things can be misused, but then, is there anything that can’t be misused in the wrong hands?

Anyway, comments like this are great, but they also represent potentially sensitive information that you might not want to reveal to third parties, especially in this case, because the comments themselves are persisted to the workbook. Excel 2007 has a nifty Document Inspector you can use to remove these comments, which is nice. However, when you run the inspector, it simply says that there were comments detected, would you like to delete them? Nice, but not terribly informative.

Retrieving the comments on a Name is not too hard.

cmt = ExcelApp.ActiveWorkbook.Names(1).Comment

But, when you go to clear the comment:

ExcelApp.ActiveWorkbook.Names(1).Comment = ""

Nothing happens…

Excel, and Word, and PowerPoint for that matter, all seem to be afflicted by this particular “a blank string doesn’t really count” malady in one way or another. Setting the Creation Date, for example, or the Username or UserInitials all seem to have similar problems with being set to a null string.

The solution, though not ideal, isn’t too terribly bad either, I suppose.

ExcelApp.ActiveWorkbook.Names(1).Comment = " "

Yes, that’s right, just set it to a space, instead of a null string.

It’d be interesting to see the code responsible for implementing these properties, just to see how it could be coded such that a null string is ignored like this. It seems like it just couldn’t have been intentional.

And just for the record, passing an actual null char doesn’t work either, so it doesn’t appear to be a “C string/BSTR related” issue.

Excel XLSB Format

0
Posted by admin on April 19, 2007 – 9:43 pm
Filed under Office

For anyone that might not be familiar with the issue yet, Office 12 introduces a rather large number of new file formats.

There are formats that include macros (DOCM, XLSM, etc), formats that explicitly exclude macros (DOCX, XLSX, PPTX), and, for the Excel aficionados out there, a whole new binary format, XLSB.

This new binary format is not the old style XLS format, although both are binary and relatively proprietary. It is, however, based on a ZIP container file, just like the other new Office formats. This is a big difference from the older DOC and XLS files, which were OLE Structured storage files.

Stephane Rodriguez has written an excellent article on Code Project about the new format, with some good info on the old formats too.

Very interesting that the BIN files within the zip container turn out to be OLE Structure Storages almost exactly like what the old formats used, just wrapped up in a ZIP container instead of a Stuctured Storage container.

So much for really simplifying anything.

Fun with the Office 2007 Ribbon

0
Posted by admin on April 19, 2007 – 3:24 am
Filed under Office

I was trying to exercise the repurposing functionality in the Office 2007 ribbon today.

Specifically, I wanted to intercept the FileDocumentInspect command on the Office Menu.

Now, I’ve already added a button to the ribbon, so I added the <commands> section just like all the examples I’ve found indicate:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
 <ribbon>
  <tabs>
   <tab idMso="TabHome">
    <group id="MyGroup" label="My Controls" insertAfterMso="GroupClipboard">
     <button id="MyButton" label="Hello, Word!"
      imageMso="HappyFace" onAction="HelloMacro" size="large"/>
    </group>
   </tab>
  </tabs>
 </ribbon>
 <commands>
  <command idMso="FileDocumentInspect" onAction="MySub"/>
 </commands>
</customUI>

Run the app, load up Word, no joy.

Is the example I found based on Beta bits? Have I mistyped a quote somewhere?
So I start hunting.

Eventually, I had what you might call an AHA moment.

If you have ever played with the .NET configuration stuff, you know that one of the infuriating aspects of it is that the XML sections can be “positional”, meaning that if you put a particular section after another section, it won’t work, but put it BEFORE and all is good. I suppose there might be a rational explanation for this, and for that matter, I suppose there might be a rational reason I’d want two different files to be distinguishable only by case<g>, but sorry, I just don’t see it.

Anyway, put the commands section BEFORE the ribbon section and all is right in the world again:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
 <commands>
  <command idMso="FileDocumentInspect" onAction="MySub"/>
 </commands>
 <ribbon>
  <tabs>
   <tab idMso="TabHome">
    <group id="MyGroup" label="My Controls" insertAfterMso="GroupClipboard">
     <button id="MyButton" label="Hello, Word!"
      imageMso="HappyFace" onAction="HelloMacro" size="large"/>
    </group>
   </tab>
  </tabs>
 </ribbon>
</customUI>

Positionally dependent XML. Gotta love it.

Changing an existing group in the Office 2007 Ribbon

0
Posted by admin on April 18, 2007 – 10:00 pm
Filed under Office

I ran into this several weeks ago and initially blew it off as something I was probably not doing right.

I was trying to add my own button to an existing group on one of the Word 2007 tabs.

Nothing I tried would work. I finally gave up and just created a new, 1 button group with my button in it.

Fine.

Today, I ran across this PDF of a presentation by Ken Getz. On one of the slides towards the end, he makes an offhand comment about not being able to customize an existing group in the Ribbon.

Instead, he goes on, you could hide the existing group, and recreate it entirely, then add your own button(s).

He does not recommend doing so, though, because other addin’s might end up wanting to alter the original group and the results would be less that perfect.

So, I guess you just can’t.

The Office 2007 Document Inspector

0
Posted by admin on April 18, 2007 – 8:33 pm
Filed under Metadata, Office

I just stumbled across a quite informative article about the Office 2007 Document Inspector API.

http://msdn2.microsoft.com/en-us/library/aa338203.aspx

Basically, it’s a new API for removing metadata from Office Documents. It’s extensable, which is cool.

But it’s woefully underpowered as is, and it’s only applicable to Office 2007 installations.

At any rate, a decent article on the subject.