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!

Sub PublisherPrint()


' Must have activated MS Publisher Object Library via VBE > Tools > References
  '[Is there a way to do this programatically so I can force it to be activated if it isn't already?]
   '[I know it's possible with a GUID, but I don't know if there's a universal GUID for all Publisher installs]
	
Dim pbApp As New Publisher.Application
Dim pbDoc As Publisher.Document
Dim ReportDate As Range
Dim FileDate As String

Application.ScreenUpdating = False


'Save the workbook so Publisher can see current changes
ActiveWorkbook.Save


'Open Publisher
Set pbApp = New Publisher.Application

'Open file
Set pbDoc = pbApp.Application.Open("X:\[path]\[filename].pub")


'Update linked tables
 
    ' Insert 1 second pause because a google search said that helped someone with a related problem - I've no idea if it actually helps or not.
	  If iFirstTimeThrough <> "NO" Then Application.Wait (Now + TimeValue("0:00:01"))   
		iFirstTimeThrough = "NO"
			
	With pbApp.Application.ActiveDocument
		Dim shpShape As Shape
		Dim PgCnt As Long
		   				
		'Loop through all pages one at a time
		With .Pages
			For PgCnt = 1 To .Count 

			 'Loop through all shapes in each page
				For Each shpShape In ActiveDocument.Pages(PgCnt).Shapes
						If shpShape.Type = pbLinkedOLEObject Then
						   shpShape.LinkFormat.Update
						End If
				Next shpShape
				
			Next PgCnt
			
		End With
		
		'Do the same for shapes in the Master Page, as it's a separate entity from ActiveDocument.Pages
		For Each shpShape In ActiveDocument.MasterPages(1).Shapes
			If shpShape.Type = pbLinkedOLEObject Then
				shpShape.LinkFormat.Update
			End If
		Next shpShape
				
'=============================================================================================================	
'// I also tried iterating pages using variations of the following code block (found in a Google search), but they never quite worked. Including here for informational purposes.
	
	' For Each Pg In ActiveDocument.Pages
        
'//When you convert integer to string it adds a space in front, so need to remove the leading space
     '   PgNumber = Str(PgCnt)
     '   PgLen = Len(PgNumber)
       ' PgNumber = Right(PgNumber, PgLen - 1)

         '   For Each shpShape In ActiveDocument.Pages(PgNumber).Shapes
  '                  If shpShape.Type = pbLinkedOLEObject Then
  '                          shpShape.LinkFormat.Update
  '                  End If
  '          Next shpShape
  '      Next Pg

'=========================================================================================================


'Insert another pause to allow links to update, just in case it helps
 If iFirstTimeThrough <> "NOAGAIN" Then Application.Wait (Now + TimeValue("0:00:01"))   ' 1 second pause
    iFirstTimeThrough = "NOAGAIN"
    
   
   
'Print document
pbDoc.PrintOut


' Export as .pdf [already works as intended]
Const FileExtension As String = ".pdf"
    
    Dim FSO As Object
    Dim SupplierName As String
    Dim FolderPath As String
    Dim FileName As String
    Dim FullPath As String
    Dim Version As Long

    
     Application.DisplayAlerts = False
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
	
	'Set date variable based on named range from top of spreadsheet
    FileDate = Range("ReportDate") 
	
	'Set path and filename
    FilePath = "X:\[path]\"
    FileName = "filename_" & Format(FileDate, "MM.DD.YYYY")
    FullPath = FilePath & FileName & FileExtension
    
	'Check if file exists, append sequential numbers to filename if so
    Do While FSO.fileexists(FullPath)
        Version = Version + 1
        FullPath = FilePath & FileName & " (" & Version & ")" & FileExtension
    Loop
    
	'Save .pdf
    pbApp.ActiveDocument.ExportAsFixedFormat pbFixedFormatTypePDF, FullPath
    MsgBox "Your file has been saved in the folder: " & FullPath, vbInformation, "Tracker"
    
End With
    
	
   Application.DisplayAlerts = True
   
  
       
' Close and clear Publisher
pbDoc.Close
pbApp.Quit


Set pbDoc = Nothing
Set pbApp = Nothing


Application.ScreenUpdating = True

End Sub

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

In a Module:

Sub Auto_open()

      '...same as update code above
End Sub

In ThisDocument:

Private Sub DocumentOpen()

    
        '...same as update code above
End Sub

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.