I acquire consistently admired to assay adapted brands of swiss fake watches affluence watches. If comparing the above brand, there are replica watches uk not abounding differences you can accretion distant from the above of replica rolex uk the replica. You can accretion some of the best Patek Philippe replica watches and acquire abolishment to rolex replica say added than your architectonics or you could accretion bigger above and a lower replica hublot watches casting and achieve your best easier.

Currently browsing Office posts — Visual Basic Feng Shui

Category Archives: Office

Phantom Header Changes in Word

0
Filed under Office, Troubleshooting

For today’s blog entry, I’m going to delve into a bit of Word esoterica that, if you’re into this sort of thing, you might find fascinating, and if you’re not, well, you have been warned<g>.

If a document is changed in Word, you’d expect to get a prompt when you close the document, wouldn’t you?

Well, that’s apparently just expecting too much, my friend.

And if you were to load a document programmatically and simply iterate through it, without changing anything, you’d expect that the document wouldn’t actually change, wouldn’t you?

Well, that’s also… oh, you get the picture.

It appears to be possible in Word, without programming, to create a header that doesn’t actually exist, or rather, that exists but is completely empty.

If you then iterate through the headers of the document, retrieving the Range of each, the act of simply “retrieving” the range, not actually modifying it, will cause the header to be modified, inserting a paragraph mark where there was none before.

This can effectively shift the entire document down, since, in the original document there was no “paragraph” in the header, but now there is.

Even more interesting, this modification isn’t logged internally by Word, so if you were to close the document, you get no prompt that a change has been made, and from what I can tell, the change isn’t actually saved, although it’s very visible on-screen.

You can test this by creating a document with a null header, then, adding this VBA code and running it:

Sub Test()
   Dim sct As Section
   Dim r As Range
   Dim hdr As HeaderFooter

   For Each sct In Me.Sections
      For Each hdr In sct.Headers
         Set r = hdr.Range
      Next
   Next
End Sub

How do you create a document with a null header? To be perfectly honest, neither I nor a Microsoft Tech could figure that one out. Please let me know if you can.

But what I did discover was that the situation yielded something you could detect.

Essentially, the problem is this:

Each SECTION object in Word has a PAGESETUP object associated with it.

And each PAGESETUP object has an associated HEADERDISTANCE and TOPMARGIN property. It also has FOOTERDISTANCE and BOTTOMMARGIN properties, but I’ll only deal with headers here. Footers are handled in exactly the same way.

In a document’s section, if the HEADERDISTANCE is ever >= the TOPMARGIN, any changes in the size of the header have the potential for “spilling out” over the limit of the margin and pushing the content of the section body down.

I say potential, because if the header doesn’t contain anything, or if what it contains is not enough to push the bottom of the header past the TOPMARGIN, everything will stay put and all will be well.

In any case, right before you save the document, you simply need to iterate through all the headers in all the sections of the document and test if they’re empty. If they are, you force the HeaderDistance back to half of the TopMargin.

Section.PageSetup.HeaderDistance = Section.PageSetup.TopMargin / 2

Why half? It essentially doesn’t matter what it is, as long as it’s less than the TopMargin.

But what makes up an “empty” header?

Once you get the Range of the header

set Range = Header.Range

Make sure it’s not a linked header

If Range.LinkToPrevious then {it's linked so don't bother with it}

and then check the character and word counts

If Range.Characters.Count > 1 and Range.Words.Count > 0 and Range.Characters.Count > Range.Words.Count Then
   {this is NOT an empty header}
End If

If it’s empty, adjust the HeaderDistance as noted above.

Fun stuff.

Digitally Signing a Word 2007 Document

3
Filed under Office, Security

I originally wrote about digital signatures in Office documents way back here, so check there for more information. But I just stumbled across something with Word 2007 and document signing (including signing templates) that had me scratching my head for a minute.

Word, in it’s shiny new 2007 skin, now has a nifty little feature to add a signature to a document (or template) right on the…um… what they hell you do call this button?

image

Anyway, under “Prepare”, you’ll see this:

image

The Add a Digital Signature lets you sign the document right there. Which is great.

Except for one thing.  That signature is not the same as this one:

image

The former actually signs the document, whereas the latter signs the VBA code contained in the document.

If you don’t believe me, sign a document using the Prepare menu item, then check the signature using the VBA/Tools/Digital Signature menu item. Then sigh and weep.

So, what does that matter, you ask?

Well, in terms of checking the validity of macro code in a document, from what I can tell so far, the signature on the document isn’t checked, only the signature on the VBA code. From a macro/VBA standpoint, signing the document is pretty useless.

I’m still hunting for a way to automate the signing of DOC and DOT files (such as the SIGNTOOL.EXE utility for signing DLL’s and EXE’s). That would make the whole process much more convenient, not to mention enabling it to be built into a normal build process.

Digital Signatures in Word 2007 Templates

1
Filed under Office

Encryption is an unbelievably complicated topic and I’m only now beginning to get comfortable with digital signatures in Office documents.

The Good News is that in Office 2007, other than the fact that the menu locations for checking signatures has changed, most everything about digital signatures has remained the same as in Office 2003, and, from what I can tell, things are compatible all the way back to Office 2000.

The Bad News is that it’s still complicated as hell to deal with digital signatures.

I’ll try to cover a few of the bigger bits I’ve come up against while digging through digital signatures in Office (and in particular, in Word).

Signing a DOT file

The first thing you must do when getting ready to sign a template is to manually enable timestamping. Ok, technically, you don’t have to do this, but if you don’t timestamp the signature, when the certificate used to sign the template expires, so will the signature, and poof, your template will no longer be signed. Depending on how the user’s Word Is configured, that may mean that your template now fails to load.

You enable timestamping by adding a few entries to the registry. Office doesn’t provide a way to do this via the UI (even in 2007), so it’s all regedit (or run the REG script below).

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\VBA\Security]
“TimeStampURL”=”http://timestamp.verisign.com/scripts/timstamp.dll
“TimeStampRetryCount”=dword:00000005
“TimeStampRetryDelay”=dword:00000005

Once you’ve turned timestamping on, you need to verify that you have a digital certificate loaded that can be used to sign templates.

Click the Start button and RUN, enter MMC and press Enter. This should bring up an empty Management Console.

image

Click FILE, Add/Remove Snap-in and find the Certificates snap in, then load it up.

When it asks for what type of certificates to manage, select the computer account.

image

Next, it should ask about managing the local computer account or a remote computer account. You’ll normally want to only deal with the Local Computer.

Once you get the certificate manager loaded, expand the personal certificates. Generally speaking, this is where certificates purchased through online vendors (like Verisign) will end up when you download them.

image

Now, right click the certificate and choose Properties.

image

You should see code signing in the box as shown. I’m not an expert on the different types of certificates, so it may be possible to sign documents WITHOUT a code signing certificate. Best to talk with someone at your vendor for details on that. One definite benefit of a code signing cert is that you can use it to sign DLLs, and EXEs, as well as templates.

Anyway, at this point, you can be pretty sure the cert is appropriate for signing templates.

One final note: It is often a good idea to export the signature so you can use it via the command line etc. The PFX (Personal Information Exchange) file format is good for this, because it contains both the public and private keys for the certificate. The bad news is you want to protect the PFX file as much as possible, because if someone obtains it and the password used to create it, they would be able to sign documents as if they were you.

You can export to a PFX file by right clicking the certificate entry (in the middle list), selecting All Tasks, then Export.
Indicate that you want to export the private key (which will require a password), and then make sure you select the PFX output option.

Verifying the Signature

Verifying that you actually got the Digital Signature correct is one of the more frustrating parts of working with digital signatures in Office.

You’d think you’d be able to verify the signature via the Tools/Digital Signatures menu in the VBA editor screen:

image

image

image

image

If you get this far, you know that the signature was signed. You can check the valid dates on the certificate as well.

However, this screen does NOT show the date the signature was timestamped and that, unfortunately, is a key piece of information you need to verify the signature. In fact, although this screen purports to be showing the digital signature, this is really just information on the certificate used to create the signature.

Verifying the TimeStamp

To actually get information about the signature, and specifically the timestamp, you have to jump through a few hoops.
First, make sure that the template IS NOT in any of the autoload places (most commonly the Word STARTUP folder or the Excel XLSTART folder).

Next, make sure that security is set such that unsigned templates won’t automatically load without prompting.

image

image

image

Once you’ve done that, if you put the signed template, say, on the desktop and load it by holding down the SHIFT key, right clicking, and selecting OPEN, you should get the message bar indicating that there are security warnings.

image

Click the Options button and you should get a Security Alerts Window:

image

Click the Show Signature Details link to see the actual details of the signature itself:

image

Notice the title of this dialog is actually “Digital Signature Details”, not “Certificate Details”. And the Signing Time is indicated here, showing that, in fact, this digital signature was timestamped.

One final note. The Office 2007 and 2003 object model (and XP, but not Office 2000), contains properties for enumerating and retrieving information on document and template signatures. Specifically, the DOCUMENT object now sports a SignatureSet property, which is a collection of signatures on the document. From there, you can navigate through the object model to retrieve all the signatures on the document, as well as details (including the signing time) of those signatures.

However, there are significant differences between the models in 2007 vs 2003, so any code written to utilize those objects would need to accommodate those differences.

The above steps work similarly in all versions of Office from 2000 to 2007. I’ll try to capture screenshots of the process in Word 2000 at some point, to illustrate the differences.

Related links

Office 2000 Macro Security

Outlook and Attachment Strangeness

0
Filed under Office, Troubleshooting

Dealing with attachments in Outlook is a bit like entering an Ultimate Fighting match against a professional half-octopus, half-eel. It really doesn’t matter what style of fighting you choose, the octo-eel is guaranteed to take you down more than once in the match, and there’s really no telling whether you have a chance at winning.

An app that I’m working on deals with attachments in Outlook, more specifically, it deals with Office format attachments, so we’re talking about DOC, XLS, and PPT files (and about 2 dozen other *X and *M formats introduced in Office 07).

Anything else (like BMPs, TXT files, etc) are supposed to pass on through like yesterday’s iceberg salad.

However, astute customers noticed that, in some cases, the order of the attachments could change at times. If you had numerous attachments to your email, and you intended on them being in a particular order, that might not go over well.

Now, you might think, with such a nicely laid out object model as Outlook’s, that preserving the order of attachments in a message would be trivial. Hell, you might even thing you could simply replace one attachment with another, simply by using a replace method or somesuch.

And you’d be wrong.

There’s no replace method, unlike GroupWise. And the ATTACHMENTS.ADD method doesn’t allow you to specify an ordinal. It does allow you to specify a position, but that value is only used to indicate the character position of the attachment in an RTF format email. If the email format is plain text, or HTML, the position isn’t used at all.

Then there’s this jewel from the MSDN documentation on the add method.

To ensure consistent results, always save an item before adding or removing objects in the Attachments collection of the item.

Special.

Add to all of that the fact that Word can also be used as the email editor (albeit in approximately the same capacity as the built-in Outlook RTF editor from what I can tell), and you have a whole lot of arms to fight with on this octo-eel.

The bottom line however, turns out to be relatively simple.

  1. First, you have to make a single pass through all the attachments in a message and for every one you plan on removing, you need to add the new one. In addition, for those that you don’t plan on removing, you have to extract them, then add them again anyway, in the same order as they already exist.
    This will cause the email to end up with a double set of attachments; the originals, and all the new ones, plus a second copy of any original attachments you didn’t necessarily want to mess with but have to anyway.
  2. Next, make another pass through all the attachments, removing all of the original attachments, but leaving everything else. Now, the email will contain the proper list of attachments, both new and untouched originals, in the original order, at least for plain text and HTML formats.
  3. For Rich Text, there’s one more trick….
  4. When you add the new attachments (or re-add those attachments you intend to pass through, in step 1 above), retrieve the POSITION of the attachment first.
    If it’s 0, make sure you add the new attachment WITHOUT specifying a position argument. Specifying a 0 for the position will only mess things up.
    If it’s NOT 0, however, add the new element with a position argument of the original POSITION + 1

It sounds more complicated than it is.

If you get any part of it wrong, you’ll know pretty quick. Usually, the attachments will end up in the sent email reversed in order. Worse case, some will end up missing.

One final trick. Testing all this can be a real pain, but you can make it a little easier.

Set up Outlook with POP accounts only (don’t use Exchange for this). Then set it to NOT send email on startup:

image 

Anything you send will end up in the outbox, where you can examine it, use it for debugging, drag it back to the inbox to attempt a resend, or delete it. And from what I can tell, whatever ends up in the outbox is what would go. There’s no additional processing done after that.

Plus, you don’t even have to use a legitimate TO address, anything that looks like an email address will work.

Believe it or not, I actually like Outlook, despite it’s, uh, quirks? It’s been my email system since about 97. I’ve never had a corrupted PST file, never lost an email (that I didn’t unintentionally delete with Shift-Del), and never gotten an email-born virus.

Now, excuse me, I have some serious knocking to attend to. 

The Running instance of Windows Media Player

13
Filed under Media, MP3s, Office, Utilities

I’m finishing up my little Signature Enhancement Utility for Outlook and had finally gotten the Media Center 12 “Currently listening to” functions operational (This is just a minor feature I’ve seen popular on website blogs and forums, where the tag line contains not only the author’s name but what they are currently listening to, if anything, nifty and fun, but not in the least practical).

I figured I’d go ahead and try to support Windows Media Player 11 (and hopefully earlier versions) as well.

Basically, the idea is to grab a reference to the running instance, interrogate it as to the “playing” state and, if it’s playing or paused, retrieve the name, album, artist, etc info on the playing track and make it available as replaceable fields in the signature.

With Media Center, it was almost trivial:

Set omc = GetObject(, "MediaJukebox Application")
If not omc is nothing Then
   '---- it's running
   ' if it's not running, they can't be playing any music
   With omc
   Select Case .GetPlayback.State
      Case PLAYSTATE_PAUSED, PLAYSTATE_PLAYING
      '---- Media center info is available
      ps = .GetCurPlaylist.Position
      CurTrackTitle$ = .GetCurPlaylist.GetFile(ps).Name
      etc...

Obviously, if the GETOBJECT fails to return anything, Media Center isn’t currently running so the user can’t be listening to anything.

Three hours of Googling later, plus tons of experimentation and I’m not even an inch closer to getting this working for Media Player.

Using ROTView(the Running Object Table viewer, comes with various installations of Visual Studio), it does appear that WMP registers “something” with the ROT, which I’d think would be accessible by VB’s GetObject().

Alas, “Windows Media Player”, “WindowsMediaPlayer”, “MediaPlayer.MediaPlayer”, and on and on, all came up empty.

I scoured the registry for anything that even remotely looked like the moniker of a WMP registration with the ROT and everything I tried also came up empty. I’m sure it’s another case of knowing the magic password, but so far, it appears to be a tad more involved than Speak, friend, and enter.

So, for now, looks like I’ll have to rely on the FunPack for support of a limited set of attributes of the currently playing song in WMP. Apparently, for ITunes, you can use this plugin to accomplish the same thing, though I don’t use ITunes and probably won’t bother with testing that.

If anyone’s ever had any success with accessing the running instance of Media Player, I’d love to hear about it!

Fun with the Word Object Model

0
Filed under Office

Ran into something very interesting with Word, Templates and the Word Object Model today.

I have a Word Addin that creates a Word Template on the fly and loads it when Word is loading up.

The template is used to save any modifications (via the Application.CustomizationContext property) to the Word Toolbar that I have to make while running, which is pretty standard stuff for a Word COM Addin.

Under normal circumstances, this arrangement works quite well. But today, I had a client report a problem where when they’d close Word, they’d get a pop up message that Word failed to save my template, because some other process has it open. Further investigation showed that 2 or more instances of Word were being loaded (in the background by another application), and the multiple instances of Word appeared to be what was causing the issue.

Now, most anyone who’s had to mess with supporting Word as a programmability platform knows it’s not a good scene to encourage multiple instances of Word. It just doesn’t work out all that well. But here I was, and the other app was a necessary one for this client, so uninstalling it was not an option.

After quite a bit of poking around, it turns out that Word appears to have a problem, or at the very least, an oversight.

The Word Object Model supports an Application.DisplayAlerts property, that is supposed to turn off message boxes, but it fails to do so in the case when you obtain a Template object and then use the Save method on it.

So even if you’re trapping errors on the save, intending to handle them through your code, and even if you’ve turned off alerts, using the above property, Word will still pop up that warning box. Since my template is a completely background piece of functionality, the last thing I want users seeing is warnings about Word being unable to save it.

And here’s the kicker. Since you can’t necessarily know whether Word will be successful at saving the template beforehand, there is no way you can determine whether or not you should even attempt to save the template. A classic catch-22.

In the end, I was able to work around this, er, unique, behavior by:

  • during the Word/Addin startup process…
  • Check if my template exists
  • If it doesn’t, create it and set a flag indicating that it’s safe to save (usually during the Word shutdown process)
  • If the template does exist, check if the file is writable (by attempting to open it with write access)
  • If that fails, the template is already opened (likely by another copy of Word) so set the flag indicating to NOT attempt to save the template from this instance of Word.
  • If the file writable check succeeds, the template is writable, indicating that nothing else has it open, so set the flag to indicate that it should be safe to save the template.

It’s not ideal, because, under some very peculiar circumstances, it still might be possible for Word to fail to save the template, and subsequently pop up that warning box. But such is the joy of the Word Object Model!

Outlook Signatures

2
Filed under Office, Utilities

On a lark a few days ago, I decided I’d like to have random rotating quotes appended to my outgoing emails. I use GMail for mail preview (and access when I’m away from my desk), but usually, I use Outlook when I’m at my desk.

Supporting random email signatures in GMail is a topic for another day, and I’ve honestly not even investigated it at all.

But Outlook. Surely, I thought there must be a free little utility to do that out there.

Well there are a few, but most aren’t free, and the few that are, aren’t particularly well built from what I can tell.

The one free one I did see that seemed to hold promise was QLiner. After a quick install, and then converting some quotes to a plaintext file, I fired it up and set things up.

It works, but it seems finicky to me. For one, it doesn’t actually template out the signature, so much as it completely replaces the existing signature with a rebuilt version (with the new quote) on a configurable timeout. So if you sent 3 messages within 5 minutes, and the timeout was set to 10 minutes, it’s likely all 3 messages will have the same quote.

Plus, it seemed to just “stop working” after a time. I never could see a pattern to this, but, invariably, after a few days, I’d notice that the signature wasn’t changing anymore. Stopping and restarting the program fixed it each time, but that’s not the hallmark of a solid app.

In the end, I uninstalled it, and I believe I’m going to throw my hat in the ring on this one.

I’m calling it Sig-Licious. The idea is that it is a simple COM Outlook addin that monitors for new email creation events, intercepts it, and parses and replaces keywords in the signature with specific bits of info.

For instance, {Date}, {Quote}, {QuoteAuthor}, {CurrentTrack} (ie what piece of music you’re listening to right now, if any), etc, etc.

Any thoughts on what other variables might be nice? To totally geek it up, how bout any ol’ Environment Var? Or the content of a performance counter at the time? Or maybe your CPU fan speed? I’m thinking various Active Directory fields might be nice, if available. Or maybe info from arbitrary Text files? Or maybe info from arbitrary posts you store in specific folders within Outlook proper.

I’ll post something when I’ve got it a bit farther than just messageboxes<g>.

Or stop me before I get too far and tell me the URL of that nifty app you’ve found that can do all this. Please!

Update

I found a few more apps out there for this. First, ADOLSign is 129$! For what is essentially a search and replace tool. Jeez.

Then there’s Exclaimer. It’s even more expensive, but does a lot more. For an enterprise setup, I could see spending a few bucks for this sort of functionality, but for your average joe, like me? Um, no.

I also just ran across Symprex Mail Signature Manager. It’s freakin’ expensive and you can only get a min 10 user license, and it appears to require Echange, but if you need something like that, it looks pretty complete.

And finally, Bells and Whistles. It’s also basically a glorified macro search and replace. 29$. Better, but still, 29$ for this?

So far, nothing for the loan gunman looking for nifty sigs. I think I’ll continue on with my little addin.

McAfee and Word Automation

4
Filed under Installations, Office

Had an issue today where some automation code (basically VB that instantiates Word, Excel, or PowerPoint objects), started failing inexplicably.

The only thing the user could think of is that he’d just installed the lastest McAfee AntiVirus.

So, I tried a little test, opened Excel, opened the VB editor in it, then entered:

Sub Main
   Dim X as object
   Set x = CreateObject("Word.Application")
End sub

And that failed too!

After a few googles, I came up with this.

Appearently, McAfee includes a feature called HAWK (Hostle Activity Watch Kernel), that monitors things like CreateObject.

And the fun part is that even if you disable McAfee, HAWK still runs in the background. You have to turn it off seperately in the McAfee UI.

Yet another reason to use NOD32.

Office 2007 and RibbonIDs

0
Filed under Office, VB Feng Shui

By now, I suspect the Office 2007 Ribbon is a bit old news.

From a developer’s point of view, it’s relatively easy to customize the ribbon via add-in code, which is really nice. And since there seems to be no dearth of examples on doing so, I won’t be wandering down that path.

For Word, Excel, and PowerPoint, things are pretty straightforward, because, as far as I can tell, there’s only one Ribbon for them.

But in Outlook, things are more complicated. The main Outlook window doesn’t even have a ribbon. But the sole parameter of the main entry callback for customizing the ribbon is:

Public function GetCustomUI(ByVal RibbonID as string) as string

so what are the possible RibbonIDs?

After a good bit of searching, I found the following table in the VSTO3CTPTutorial.doc file that identifies them all:

RibbonID MessageClass
Microsoft.Outlook.Mail.Read IPM.Note.*
Microsoft.Outlook.Mail.Compose IPM.Note.*
Microsoft.Outlook.MeetingRequest.Read IPM.Schedule.Meeting.Request or IPM.Schedule.Meeting.Canceled
Microsoft.Outlook.MeetingRequest.Send IPM.Schedule.Meeting.Request
Microsoft.Outlook.Appointment IPM.Appointment.*
Microsoft.Outlook.Contact IPM.Contact.*
Microsoft.Outlook.Journal IPM.Activity.*
Microsoft.Outlook.Task IPM.Task.* and IPM.TaskRequest.*
Microsoft.Outlook.DistributionList IPM.DistList.*
Microsoft.Outlook.Report IPM.Report.*
Microsoft.Outlook.Resend IPM.Resend.*
Microsoft.Outlook.Response.Read IPM.Schedule.Meeting.Resp.*
Microsoft.Outlook.Response.Compose IPM.Schedule.Meeting.Resp.*
Microsoft.Outlook.Response.CounterPropose IPM.Schedule.Meeting.Resp.*
Microsoft.Outlook.RSS IPM.Post.Rss
Microsoft.Outlook.Post.Read IPM.Post.*
Microsoft.Outlook.Post.Compose IPM.Post.*
Microsoft.Outlook.Sharing.Read IPM.Sharing.*
Microsoft.Outlook.Sharing.Compose IPM.Sharing.*

BTW, for those that are interested, in Word, the RibbonID is always Microsoft.Word.Document

Similiarly, in Excel, it’s Microsoft.Excel.Workbook

and in PowerPoint, it’s Microsoft.PowerPoint.Presentation

I wasn’t able to find that info published anywhere. I suppose it’s technically irrelevant, but still…