I am creating a table to place the information for the different files that we use at work. The files are both Excel and Word. The table will have hyperlinks to the files so any user can open the file directly. Both the Word and Excel files have a BeforeSave macro that runs as an add-in. The problem is that the BeforeSave event works fine when the Excel files are opened through the Access Hyperlinks but the Word add-in does not work. There are no errors when the save button is pressed, the event is not triggered and it does nothing.
I had the exact same issue when opening the Word documents through Excel hyperlinks and found the solution through this forum. But I have very little experience with Access vba, so I'm unsure how to adapt the Excel code to work in Access. Below is the code I am using.
This is the Excel code that I received through this forum (From gmayor) to allow the Word add-in macro to run when opened through an Excel hyperlink (Just need help adapting this to work with Access):
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Dim wdApp As Object
Dim wdDoc As Object
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
wdApp.Visible = True
wdApp.Run "Register_Event_Handler"
End Sub
Here is the code that runs in the Word Add-in:
Module (mdlEventConnect):
Option Explicit
Public EventHandler As New clsEventHandler
Public Sub Register_Event_Handler()
Set EventHandler.App = Word.Application
End Sub
Public Sub AutoExec()
Register_Event_Handler
End Sub
Class Module (clsEventHandler):
Public WithEvents App As Word.Application
Private Sub App_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)
'Lots of code goes in here
End Sub
Thank you very much for the help! -David