Updating links in publisher (or alternative solution for end-user-friendly printing?)

Given:
1. I have a spreadsheet with 30+ small-to-medium tables. Each table is 5 columns wide (4 of which are very narrow) and 10-50-ish rows long.
2. The table lengths are subject to change so they're in one long column with gaps in between. This lets me add/remove rows without affecting the other tables. They're small enough that printing one to a page like that is wasteful of paper and hard to read.
3. My end-users are not especially tech-savvy.

Find:
Print out the tables in a user-friendly format.

Current Solution:
I created a document in Publisher and linked each Table as an OLE object. That let me arrange them as a grid (usually 2x2, but variable depending on table length). That works fine so long as I manually open Publisher, update the links, and print. But point (3) above means I need a one-stop shop. So I've written a macro to open the .pub file, update the links, print it, and also save as a .pdf with a date-appended filename.

Buuuuuut... I can't get the links to update consistently. Sometimes they work fine sometimes they don't. Sometimes I get errors and sometimes I don't. If I open the .pub manually it asks if I want to update the links, and that usually does what I want. I haven't found a VBA method to automatically accepts those updates, however, so I'm updating each link using For Each... Next logic instead. But it only works inconsistently and I don't know why.

I'm relatively inexperienced with VBA and so I'm sure I've failed to Dim some variables properly or some-such but I don't know what I don't know. Also, the available information about VBA in publisher is woefully thin, so Google has been of limited help. Most of the code I do have is patchwork copy-paste from what Google did offer up for me.

If there's a more straightforward way to do this (with or without Publisher) I'm very much open to suggestions, too.


I've attached two files. The Publisher file was rejected as an invalid format, so change the name to 'Sample Publisher.pub' to get it to integrate with the code already in place.

Thanks in advance to anyone willing to help!

Please Login or Register  to view this content.

I also tried getting Publisher to update all the links by itself on open, but that's not working either:

In a Module:

Please Login or Register  to view this content.

In ThisDocument:

Please Login or Register  to view this content.

Bumping for visibility now that the weekend is over... help please?

Update: Cleaned it up some and swapped to late binding because my end users don't all have the same version of Excel.

Firewall still doesn't like the code, so here's a pastebin instead.