Monthly Archives: January 2008

Dynamically Enabling Access to the VBA Object Model in Office Apps

2
Filed under Uncategorized

Writing addins and macros for Office apps (specifically Word, but this includes Excel and Powerpoint) can be difficult enough as it is.

But when you throw security gotchas into the mix, things can get interesting quickly.

One thing that often hangs people up are the security settings.

There are several different security levels for macros, of course. And there’s also security that controls access to the internal VBA object model.

But what’s the “VBA Object Model”?

Essentially, it’s an object model (not unlike the Word object model that starts at the Word.Application object) that allows macro code (and COM add-in code as well) full access to all the macros written in VBA and stored within the actual document (or template) file.

For instance, you can actually print out all the VBA macro code in a document with a code snippet similar to the following:

For i = 1 to WordDoc.VBProject.VBComponents.Count
   Set vbcode = WordDoc.VBProject.VBComponents(i)

   C = vbcode.CodeModule.CountOfLines

   '---- retrieve the entire code block
   If C > 0 Then
      code$ = vbcode.CodeModule.Lines(1, vbcode.CodeModule.CountOfLines)
      Debug.print code$
   End if
Next

Depending on what you need to do with your code, this can be very very useful.

But…

It doesn’t work at all unless the user checks the “Trust Access to the VBA project object model” on the Macro Security screen (shown here from Word 2007, it’s essentially the same in Word 2000-2003).

image

Now, you might think that, hey, a potentially powerful security hole like that would be something that ONLY a user would be able to change. In other words, the setting wouldn’t be readily available directly through the operating system.

And you’d be right, and wrong.

That setting can be found, quite simply, in the HKEY CURRENT USER registry hive.

For Excel:

image

For PowerPoint:

image

and for Word:

image

However, that’s not the end of the story.

It is true that the setting is a PER USER setting, which means that your application would have to alter this setting for each user that logged onto a machine. But the current user generally has FULL RIGHTS to HKEY CURRENT USER hive, so why put a security setting like this there?

Because it’s not the only place you set it.

There are also keys like these (note the version of Office):

HKEY_LOCAL_MACHINE\Software\Microsoft\Office\12.0\Word\Security
HKEY_LOCAL_MACHINE\Software\Microsoft\Office\12.0\Excel\Security
HKEY_LOCAL_MACHINE\Software\Microsoft\Office\12.0\Powerpoint\Security

Each potentially contains a value named AccessVBOM as well.

If this value is defined and has a value of 0, the value in HKCU is essentially ignored, and the user cannot grant access to the VBOM, even if they manually enter a 1 for the value under the HKCU.

If the HKLM value doesn’t exist at all, or has a value of 1, then the user can set the HKCU value, or change the Trust Access checkbox manually in the application.

And since write access the HKLM is often restricted to local admins, this gives admins a way to lock down this setting on machines, which is a good thing.

Another important point; changing these settings while the Office app is already loaded, will not cause said app to change its behavior dynamically. Put another way, Word, Excel and Powerpoint appear to read these settings once, at load time, and then never read what’s in the registry again until all instances are unloaded and the app is loaded again.

Practically, this means that you can’t change these settings from within a true Office Addin (ie a COM addin or a VBA Macro).

But you CAN change them quite easily from other code. At least, it’s easy to change the HKCU value, and the HKLM value can be changed during your MSI based installation.

Now comes the ethical dilemma.

Your application won’t run (or won’t run in entirety) if access to the VBOM is not granted, and many people would find it difficult to change that setting, even through the UI. But that’s not the kind of setting that you want to change automatically  or surreptitiously without letting the user know you’re doing so. Or is it? Would typical users know what it meant anyway? And most Office macro viruses would be caught via other means now, right?

As a final note, googling “AccessVBOM” turns up plenty of pages describing these keys in various levels of detail, but interestingly, searching the Microsoft.com site comes up with very little on the topic.

Making Tivo Server Run as a Service

8
Filed under Tivo

I love Tivo. If you have a cable company DVR, my heart goes out to you. My 4 year old daughter can operate our Tivo to watch her favorite show. And she can easily pause the action to take those all important potty breaks! That’s not to say she watches a lot of TV, but hey, Mr. Roger’s rocks!

I don’t even mind the 13$ a month for the service (although now, I SOOOO wish I’d taken the blue pill gone with that lifetime subscription.

But one thing that has annoyed me since I first got the thing networked is the fact that the dang server software has to be run from a logged-in user account. Tivo sets up its “Beacon” service as a legitimate Windows Service, but there are three other programs that get run via the RUN key in HKCU, and, of course, those only get fired off after a user logs in.

Since my Tivo server software is actually running on a server, it’s a tad annoying to actually HAVE to login just to get the Tivo stuff running.

It hasn’t been a huge pain, but I finally got some time to experiment, and I’m happy to report, there does appear to be a solution.

The Goal

I should reiterate that the goal of all this is to get things set up such that powering up the “server” machine is enough to get the Tivo Server running and able to communicate with the Tivo. If you want to actually use the Tivo Desktop from the PC, then you’ll still have to login.

The Disclaimer

Before you start, be warned that mucking with your registry and with services can be dicey, especially when your setting up programs to run as services that weren’t originally intended for that purpose.

However, in reality, there’s really no need to directly fiddle with the registry using this technique. And converting an app to a service can be done for free, or done much more easily for the reasonably low cost of a simple utility.

What’s needed

  • A Tivo, and it really has to be networked for there to be much of a point in all this.
  • A “server” PC that is already running the Tivo Desktop and Server software. You absolutely MUST already have the Tivo software running and verify that it can connect to your Tivo, download shows, share music etc before even attempting any of this process.
  • A way to convert a standard app into a Windows Service. I use FireDaemon for this, simply because I already bought it for other purposes, and it’s a bargain at 40$, but there are free alternatives out there. One in particular is SRVANY.EXE. A good article about that approach is here, but just google for more.

First things First

As mentioned above, make sure that your Tivo Desktop software is already installed, running and can connect to your Tivo and at least download the list of recording available.

image

You’ll also want to share at least one MP3 track or one photo, so you can see them from the Tivo box. Using Tivo Desktop, setup music or photo sharing here:

image

You do this so that, once everything’s reset and running as a service, you can easily check, from the Tivo, whether it can still see your PC and the Tivo Server running on it.

Stopping the autoRun of the Tivo Background Apps

Technically, this step isn’t really necessary, but I’ll detail it anyway, for those interested parties out there.

As I said earlier, the Tivo Beacon is already configured as a service, so we don’t have to do anything to it.

However, a quick glance through the registry will reveal these keys:

image

To be technically correct, you should probably remove these keys from the registry (backing them up first!). Actually, you wouldn’t really want to remove the TivoNotify entry, since it really should be run when a user logs in. But the TivoTransfer and TivoServer entries could be removed.

However, from experiments I’ve done, it doesn’t seem hurt to leave them in place. If these programs are started as services, then running them again when you actually do login doesn’t appear to have any effect.

Setting Up the Tivo Transfer app as a Service

Open FireDaemon and select Services – New to create a new service definition. This one will be for the Tivo Transfer Service.

You’ll get the Edit Service Definition Dialog as below. Fill it in appropriately. Typically, if you perform a standard Tivo Desktop install, these paths will be the same for you.

image

Pay particular attention to the Executable name and the Parameters.

On the Setting page, make sure the Logon Account is set to a user with appropriate permissions and that you will normally logon to the machine as (usually, it’s the user under which you installed and setup the Tivo Desktop).

Also, make sure the Startup Mode is Automatic and disable program exit monitoring.

image

And finally, on the Dependencies Page, add a dependency to the “Tivo Beacon” service. Mine just happens to be named “Tivo Beacon2”. This guarantees that the Windows Service Manager won’t start this service until after the Tivo Beacon is running.

image

Setting Up the Tivo Server app as a Service

Create another Service Definition in FireDaemon.

image

Make sure the logon account, Startup Mode and program exit monitoring are set as in the Tivo Transfer Service above.

And finally, set the dependencies as above, but in this case, include both the TivoBeacon and the Tivo Transfer service (that you just created).

What About That Tivo Notify App?

Good question. It handles the little system tray icon, and as such, doesn’t need to be started as a service.

Restart

At this point, you should have created 2 services, one for the Transfer app, one for the Server app.

Use the TaskManager to kill off the two processes (TivoServer.exe and TivoTransfer.exe) if they’re running.

Then, use FireDaemon or the Windows Service Control panel to start the two services you just created. If everything went right, you should see them running in the Task Manager.

image

Restart your machine and DO NOT LOGIN.

The Wrap Up

Once your PC has restarted, use your Tivo to check that it can see your shared Music or Photos (from the Now Playing screen, select Music, Photos and More, and scroll down to find your PC’s shared music in the list (in my case, My Tivo Server runs as the Administrator and my server’s name is Olympus).

 image

If your Tivo can see those, and can browse through your music or photos, you know that the Tivo Server and Transfer services started properly without you actually logging into your PC, which is the whole point of this twisted exercise!

If not, go back and check to verify that everything is setup as above and that there are no errors logged in the Application Event Log.

Things to Watch Out For

From what I can tell, the Transfer Process, and to a lesser extent, the Server process, do some peculiar things when they start up (I believe they actually start up a second instance, then terminate the initially loaded instance).

Because of this, FireDaemon’s “Process Exit Monitoring” function really made a mess of things. Basically, the idea is that FireDaemon can monitor the process that it starts as a service. If that process terminate for some reason, FireDaemon can automatically restart it.

Under normal circumstances, this is a good thing because you’d want to service to be constantly available. This won’t work with the Tivo processes though. That’s why I disable this feature when setting these services up (see above).

It might be possible instead to increase the load delay or otherwise tweak FireDaemon’s settings, but I haven’t tried that yet.

I do know that if you don’t do something, though, the Tivo processes will just enter an endless cycle of starting, stopping, and FireDaemon restarting them, only for them to stop again, ad nauseam.

And yeah, my TV’s a 36″ tube set, hence the old school sexy curves in the Tivo screen shots<G>

Fire and Brimstone! Office 2000, XP, 2003 and 2007 living together!

0
Filed under Uncategorized

This is truly old testament stuff…. Ok, enough with the Ghostbusters….

If you’ve ever had to support multiple Office environments, you’ve probably already discovered the sheer joy and wonderment that is VMWare.

But, if you have to develop against them, sometimes, it’s awfully nice to have the Office version you need right there on your main box.

It’s not terribly common knowledge, but you can actually install various versions of Office side by side (with the sole exception of Outlook, which only tolerates a single version on a machine, for dark and unholy reasons).

However, if you’ve tried, you’ve most likely snagged yourself on a rather irritating burr.

You start Word 2000, say, and you get an MSI (Windows Installer) prompt saying it’s got to repair something. You click OK, and let it do it’s thing. But then, later, when you start Word 2007, you get a similar prompt, this time for the newer version. Back in Word 2000, same prompt again. Grrr.

There’s a few KB articles out there about this annoyance, but nothing that really resolves it completely satisfactorily.

After some experimenting, however, it looks like it is possible to make various Office versions coexist more or less peacefully, without bashing each other over the head with a virtual tire iron every time one or the other fires up.

The key is registration, esp with Word. Each time it starts, Word checks certain registry entries to make sure they point to that version of Word. If not, you get that “repairing” prompt and the long delay while Word resets everything.

This is great for Joe User who accidentally scrambled his registry and just needs Word to work right again, but it sucks for Pauline Power User who has 3 versions of Office on her machine, and switches back and forth between them constantly.

The trick is to prevent Word from reregistering itself when it starts.

Details of how to do just that are in one KB article on the Microsoft site, but they only go so far as to detail how to do this for Word 2007.

The problem is, what if you want DOC files to normally open with Word 2007, but still have Word 2000 on the “Open With” list via the right click menu? I’d imagine this is a pretty typical scenario.

Take the following REG script, save it to a REG file, and dbl click it to merge it into your registry (it shouldn’t require any special privileges because it only deals with the user hive).

Basically, creating the NoReReg value for each version of Word will prevent the reregistration from happening when you start Word.

Windows Registry Editor Version 5.00

;This will keep Word 2007 from reregistering itself each time you run it
[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Word\Options]
"NoReReg"=dword:00000001

;The same trick works for Word 2000
[HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Word\Options]
"NoReReg"=dword:00000001

;for Word xp
[HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Word\Options]
"NoReReg"=dword:00000001

;and for Word 2003
[HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Word\Options]
"NoReReg"=dword:00000001

Be sure to have run the version of Word you want to use actively as the LAST THING YOU DO before running this script; ie, if you really want Word 2000 to be the default editor for DOC files, run it, let it register itself fully, and THEN run this script.

Of course, setting the NoRegReg value back to 0 (via REGEDIT) will revert things back to the way they were and allow that specific version of Word to reregister itself.

Furthermore, the newer versions of Word support a “repair” option on the HELP menu that will perform the reregistration as well.

One note; the Word 2000 and 2007 versions I’ve tested explicitly. The XP and 2003 versions I haven’t checked explicitly yet, so YMMV. However, I was utterly surprised that this trick (which was documented for 2007), actually worked with Word 2000.

And finally, neither Excel nor Powerpoint appear to need any of these shenanigans, hence their MIA status in the above script.

Properly reading the EDITTIME structure in an OLE Structured Storage file (ie just about any Office Document)

0
Filed under Uncategorized

There’s lots of articles out there about reading the properties of an OLE Structured Storage file (which is the format used for all Office apps before 2007 and with continued support in Office 2007).

Eduardo Morcillo probably has about the best implementation for VB6 that I’ve come across. Check here for tons of great VB6 code for opening/reading structured storage files, and lots of other very advanced stuff.

.NET likely makes much of this dramatically easier, but I haven’t delved into that yet so I can’t say for sure.

What I do know, however, is that Eduardo’s code doesn’t quite handle a few elements of OLE document properties. One in particular is the EDITTIME property.

EDITTIME normally tracks how long a particular file has been “edited” within a given application, be it Word, Excel, or PowerPoint.

The field itself is stored as a 64bit FILETIME structure, but it’s not actually a FILETIME structure. Instead, the EDITTIME is stored as a direct 64bit number of milliseconds that the document has been edited.

Check out here for all the details, and a good sampling of some C code to convert the EDITTIME element to a number of seconds and back again.

It’s not terribly difficult code, but it’s also virtually undocumented as to the layout of that structure.

I won’t repeat the code here, but I have to give credit to Rolandas Rudomanskis and the Shareaza team for researching the issue and making the code available.

Just kick it

2
Filed under Uncategorized

imageHopefully, this might save someone else from trading in a “dead” machine.

I picked up an AMD based generic box about 6 months ago for my wife. It’s got an ABIT-NF8 mobo, 2 gb ram, and several HD’s, plus a Vista capable graphics card. It worked great for about 3 months till the powersupply went bits up.

So, a trip to Frys, 40$ and all was well, at least for another 3 months.

Then, just a few days ago, it died again. There was definitely power to the motherboard, but the thing wouldn’t power up.

Suspecting the el-cheapo PS I’d picked up, I yanked it and trekked back down to Frys. I’ve got to get one of those PowerSupply tester units (they sell them there but they are perpetually sold out<sigh>).

At any rate, yep, the new PS had gone south too.

Hrmm. So I picked up an decent supply (this time an Antec), come home and wired it all up.

Hit the on button and up she came, for all of about 30 seconds. Grrr…

Lather, rinse, repeat, same thing.

Doesn’t seem like the power supply, but what the heck?

I disconnected everything, all the way down to the fans inside the box, still the same behavior.

But the unit would boot up, it’d just immediately power down. I started suspecting a possible short on the motherboard, but then that’d usually result in an almost instance crowbar.

Without any other ideas, I googled the ABIT NF8 motherboard for any interesting bits.

Lo and behold, I turn up at least three stories of similar behavior.

Basically, with these motherboards, if the CPU fan/heatsink gets jostled just right, it can result in a “bad connection” between the two. The end game is that the MOBO believes the CPU is overheating almost immediately and shuts down to prevent any damage.

Of course the CPU isn’t  overheating but no matter.

I unhitch the heatsink, dab on a little extra thermal grease and reseat it. Power up… Shutdown. Arrrgggghhh!

Ok. Grab the heatsink and shove it down with a good thwack.

image

Power up. Golden. Been running solid for 3 days now.

Sledgehammers aren’t just for railroads anymore.

Getting Consistent Results with Find.Execute in Word

1
Filed under Uncategorized

If you’ve ever written a Word addin, at some point you’ve probably had to use the Find object. It’s quite powerful, allowing you to find (and replace) text based on wildcards, formatting, etc.

However, it can also be an exercise in frustration getting consistent results from Find, especially when “finding” in the same document across several versions of Word (in my case, Word 2000, XP, 2003, and 2007, surely we can ignore ’97 by now<g>).

The problem comes from at least two places that I know of.

The first is that to really execute the find everywhere,  you need to execute it against the range objects of all the storyranges in the document, NOT just the main body.

Fortunately, that’s relatively easy to do:

For each Story in Document.StoryRanges    'Handle the Story here
Next

However, that doesn’t quite get you there. This is because you won’t necessarily enumerate through all the ranges for all the headers and footers in the document if you just use the above loop.

To see the section headers and footers, you have to do something like:

   For Each Section In Document.Sections
      For Each Header In Section.Headers
         'Handle this Header range
      Next
      For Each Footer In Section.Footers
         'Handle this Footer range
      Next
   Next

And finally, simply enumerating through the headers and footers, you may end up with multiple references to the same header/footer. Gah! How? There’s an unobtrusive little Word feature for headers and footers called “Link to Previous”. Here it is lurking in Word 2007.

image

When that’s set, a single header or footer might show up in several different sections, causing you to search the same place twice. Just filter those with the “LinkToPrevious” property set and you’re gold.

The bigger problem is that Word has a way of fouling its internal search state such that when your code executes its find, it simply doesn’t work.

This is a much nastier problem because it’s not consistent. Things will work fine at one point, then fail inexplicably later.

Fortunately, I happened to stumble across an article by MVP Bill Coan that addressed this issue directly. The article is about “Flushing the bad karma from Word’s search“, and how he came up with the solution is nothing short of bayou voodoo, but it’s exactly what I needed to get my search consistent across all Word versions.

Essentially, you perform a dummy search to clear out whatever internal state information Word is holding onto. This allows your real search to execute properly.

The dummy search is simply:

WordDocObject.Content.Find.Execute "^p", MatchWildcards:=False

Even better, it doesn’t appear to negatively impact performance of my search to any perceptible degree.

And finally, even though it looks like this article was written for Word 2000 (and maybe XP), I found that it’s still very much valid for the latest SP releases of 2000, XP, and Word 2003.

The good news is that Word 2007 doesn’t appear to be effected by the bug anymore, but using the dummy search doesn’t have any negative impact here either.