Need help getting hyperlinks to allow Word Add-in to run. (Access 2013)

[davis1118]

Hopefully I am posting this is the correct discussion, but I am creating a database to link to the production files that we use at work. These files are both Excel and Word. I have a BeforeSave macro that runs as an Add-in on both of the Excel and Word files that I am linking to. The problem is that the when I hyperlink to the Word documents from Access, the BeforeSave event in the Word add-in does not function. When I open the document directly, the BeforeSave event works fine. The Excel files work fine from the Access hyperlinks, it's just the Word documents that I am having an issue with. I don't have any Access Code at this point as I just started working on this database, but I don't have any experience working with VBA in Access anyway. I mainly work with Excel and Word VBA.


If it helps, I have put the Word add-in code below.

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):

Option Explicit
Public WithEvents App As Word.Application
Private Sub App_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)
'Insert long frustrating code here
End Sub

Again, I don't have any experience with Access VBA, so I am at a lose right now with what I need to do. Any help would be appreciated! Thank you - David

[Bulzie]

So you just want to open a Word document from within an Access form? Can't you just create a hyperlink to the word document? If there is more to your process can you explain what you are trying to do?

[davis1118]

So I am just simply trying to open a Word document from Access, and I am using a hyperlink currently. But I am having an issue with the Word add-in not working when the Word document is opened through the Access hyperlink. The Word add-in is a macro that I created. It runs on the BeforeSave event from the code above. The Word add-in works great if I open the Word document directly, but if I open the Word document with the Access hyperlink the macro add-in does not run.

I did have this same issue when opening a Word document from an Excel hyperlink, but I was given a macro (from a different forum) to put into Excel that would let the Word add-in run if the Word document was opened from an Excel hyperlink. Here is the code I was given to use with Excel:

ThisWorkbook:

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

Sorry if I'm not explaining this very well. I feel like I am rambling and not using the correct verbiage. Let me know if this still doesn't make sense. Thanks again for the help. -David

[Micron]

I get this from your posts: opening the word doc directly runs the code. Opening the same doc from Access doesn't. Opening the same doc from Excel does.

If that's it, I have no experience with that problem, but I notice that the code you say works from Excel uses Automation. What doesn't make sense about it (to me) is that it doesn't seem to make use of the Sh or Target parameters. It's simply creating an instance of Word if there isn't one running already, then makes it visible, then runs Register_Event_Handler. I can't tell from what you've posted where the parameters come from, why they don't seem to be used, and how it can find the event if there's no document open. Or is one opened first, in which case the Get is valid for the Word app, but the CreateObject seems pointless.

I thought it not possible to have an event automatically run at the application level but then recalled Word has AutoExec and AutoOpen. So your Word add in code runs at the application level? To quote MS: AutoExec macros are also suppressed when Word is started from an Automation client. If that's true for Excel as well, then I get why the code in Excel runs the event. You might be able to copy that code to Access. It looks like it should work, except as I said, I don't know what the parameters are or why they're not used in that procedure. You'd have to give it a name Access would accept. It won't like "Workbook_SheetFollowHyperlink".

[Micron]

I've looked at this a bit more and get what the Sh and Target parameters are, but they don't seem necessary for running the event.
Access code would need a way to pass the hyperlink target to a procedure and use the Application.FollowHyperlink method. I presume you have that on some form control. There is also a procedure that you might be interested in that's designed to overcome the usual hyperlink issues.
http://allenbrowne.com/func-GoHyperlink.html

EDIT:
I backed up my normal.dotm then added your procedures. I inserted msgbox "I think the event handler ran" in that procedure, then ran this from an Access module:

Private Sub AccessFollowHyperlink()
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

and got the message.
Hope that helps. Still baffled as to where the hyperlink fits in, but perhaps you know what to do from here.

[Bulzie]

What about using Shell command to open the word document? Or what if you opened from Access say a batch file or excel file that then opens the Word document. Not that efficient but if it runs the code in Word might work. But look at what Micron posted, might be what you need.

[davis1118]

Sorry for the slow reply back. I have been too busy at work today to try any of these suggestions. But I appreciate all the different options that you have given me to try. I should have time later today to give them a try, and I will reply back with what I find.

Thank you - David

[davis1118]

I apologize for not stating how my form is setup earlier. I have a hard time knowing what info is relevant and which is not. But I noticed Micron asked a few questions about it. My hyperlinks to the external files (Word & Excel) are placed in a subform. This is because I have multiple documents that are related to the same entry on the main form.

I was able to try out the different options that you guys suggested above, and I was able to get the "Private Sub AccessFollowHyperlink()" code from Micron above to work. I called the code from the "Click" event on the hyperlink text box. But now it runs the code and opens a blank Word application if the hyperlink is Excel or even if there isn't a hyperlink. Would there be a way around this? How did you run the AccessFollowHyperlink Micron?

I can't believe I am having such a hard time with this. I tried the GoHyperlink() function and it did not trigger the Word add-in. Also what is the Shell command that Bulzie mentioned?

[Micron]

Sure the code I wrote opens a Word application. I thought that's what you were after because you had something working for you in Excel. If you don't put parameters around what I wrote, then it will do what your post requested when responding to a form control event regardless of what the hyperlink is - or even if there isn't one. Now my understanding is that a subform is involved, and one record may contain a Word hyperlink, another an Excel hyperlink and still another has neither. If the textbox contains true hyperlinks, this may be a problem. I know of no way to get at the built in event that follows them. Based on your prior post, I guess the table field is not hyperlink data type although your form records show a working hyperlink via form control property settings. That would explain why you seem to be using the control's Click event. You need a way to differentiate between the 3 situations but I'd need clarification on my many new assumptions. Another one would be that the textbox control would never end in a command line switch and we could test that the hyperlink ends in xls, xlsm, doc, etc. and never just a folder name or slash.

I tried the GoHyperlink() function and it did not trigger the Word add-in

As I mentioned, Word AutoExec doesn't run when the application is opened via Automation, so perhaps the same is true when other applications use built in events or methods to start Word. If so, then it makes sense since that code doesn't concern itself with your AutoExec macro in Word.

Also, perhaps somewhat lacking as an explanation, but I'd describe a Shell environment as a way to "grab" a portion of an operating system so that you can input commands and have that system execute them. I believe it was originally designed so that users could remotely manipulate UNIX systems using CLI's before the advent of GUI's. Currently, Windows Shell commands are what's commonly used when programming between Office (and perhaps other) applications, and sometimes when you need to get at the Kernel of your own OS. You can Google it and see lots of code examples for doing stuff.

EDIT: also wondering if the event handler code can run after the document has opened via the hyperlink. If not, then I think you have to abandon any hope of using hyperlink methods (ones that work like a single click with the pointer hand) and resort strictly to Automation (or maybe Shell) by opening the application, causing the event handler code to run, then opening the document. Not sure what's up with spreadsheets since I have no idea if your Excel AutoExec runs when another application opens Excel. Wondering what your event handler stuff is for anyway??

Then there is the question of what happens if user clicks on hyperlink before closing that file?

[davis1118]

My hyperlinks are true working hyperlinks in the subform. I didn't realize there was another option with Access, which is why there was confusion, sorry. I wanted to attach a screenshot of the subform but the insert image prompt box isn't working for me.

I like the idea of having a conditional FollowHyperlink like you suggested. Is it possible in Access to only run the FollowHyperlink if the text box has the extension of .doc*?? I found a slightly similar example of it done in Excel but it wasn't looking for the extension type. But I feel like this could solve my issue. I'm just not sure how to write the code to have it look at the extension type.

Thanks for the explanation on the Shell function. I'll have to do a lot more reading on that, because most of it is over my head right now.

From the different tutorials I read about Word add-ins, it's my understanding that they are loaded not ran. So I don't think that they can be loaded after Word is opened. But I'm no expert so maybe there is a way around that.

Thanks again for taking the time to help me out with this issue.

[Micron]

My hyperlinks are true working hyperlinks in the subform.

It would help if you address the questions or guesses as specifically as you can because that doesn't help. Either way, they look and behave like hyperlinks, but I do believe one way poses a different and perhaps insurmountable problem given your goal. Is the table field a hyperlink field, or have you only used the control properties to make them hyperlinks?

Is it possible in Access to only run the FollowHyperlink

At the risk of repeating myself, Application.FollowHyperlink is a built in method which I believe will take precedence if the table field is the Hyperlink data type. In that case, there's no way to interact with it as previously noted. You would have to change the table field data type to text and set the character limit to something appropriate (assuming the max of 255 is good enough). Then the click event of the control could be used to discern the file extension in play.

I'm just not sure how to write the code to have it look at the extension type.

The Instr function should work with IF's or Select Case. You will not be opening Excel files from Access? Not going to write anything for this at the moment since the issue of what your hyperlinks are is still an unknown. If they are table fields of the Hyperlink data type and you're not willing to make them text fields, then I don't see a solution as mentioned above. If you are making such changes, do so on copies of the table/form in case you don't like the end result. The form control properties of note are: Is Hyperlink (yes); Display As Hyperlink (probably "If Hyperlink" would suffice).

[davis1118]

Okay, I'm sorry it took this long to figure this out on my side. I don't use Access very much. But I finally understand what you asking. The table field WAS set to a hyperlink field. I say WAS, because I understand what you are getting at with the field needing to be a text field in order for the click event to be able to look at the extensions, so I NOW have the table field set to text. I can set the form controls either way depending on which way works best, because it doesn't really matter to me if they don't show as a hyperlink with blue text. But the table field that will hold the file paths is now set to text.

Because there will be multiple users inputting the file locations into the database I thought it would be the easiest method to make the table field a hyperlink field so that each user can edit the hyperlink and insert it. But now that it is a text field I will just have them search for the file location through windows explorer and then use the Shift+right click to copy and paste the file path into Access. The reason I am explaining this is because when you copy and paste the file path into Access using Shift+right click, it puts quotation marks around the text path. Would the quotation marks have to be deleted before assigning any code to the text box?
In the sub form there will be Word and Excel files listed in the text box "Document Location". There will most likely be a mix of format versions. So there will be .xls, .xlsx, .doc, .docx, and then I would also have to take make sure the click event doesn't run if there isn't anything in the textbox.

[Micron]

OK, give me a day or so to work something out. I may PM you if I have any questions regarding field names or such.

search for the file location through windows explorer and then use the Shift+right click to copy and paste the file path into Access

Why not use the msoFileDialogFilePicker? User navigates to file via the file dialog presented by Access (Office really), selects it, and the complete path can be put into the table. Research it and see if you think you can implement it yourself since I have no knowledge at present about how the file path text part of the process is working for you. One of the important considerations for its use would be the file types filter. If the file being "linked" is .doc, then you don't want to see .xls files and vice versa.

[Micron]

Here's a crack at something (attachment at end). Explaining at a high level, it should allow a user to
- navigate to a file using the file dialog (filtered to .doc* and .xls* types, which you can modify) when no hyperlink is in the control
- change an existing link same way
- delete one
- save a record while staying on the record
- run the Word event handler code. If any exists for Excel, you can modify/add to suit
You should be able to see how much logic is involved in facilitating those abilities as you read through the procedures. I may not have considered all possibilities either.

You will have to add values to the table as I deleted all my test ones (might as well use the form rather than enter directly into table). The records I was playing with would reveal aspects of my identity that I don't care to make public. Simply open the one form and play with it. Some notes in the code to explain some of the logic. There is also a module with 3 functions; the pause one may not be needed now but I leave it in for your future benefit. There is also a handy function for checking for null and "" in a control, which I used in the code rather than repeating a block of IF statements to do this. Again, I figure you will be able to make further/future use of it.

No matter what I tried (and I tried many things, including API) when a link is clicked on and the file opened, I just could not keep Word or Excel on top without resorting to a somewhat useless message box in Access. You can leave it or test removing it to see the effect. On that note, it's the main reason I was using the pause function. No idea how users would react when the called app stays on the screen for x seconds, only to see it disappear and Access take over again. The form is set to be a popup, which was supposed to help with the Dev Ashish (sp?) API code, but it didn't.

Remember, this is all based on the table field being text (I have it set to 255 characters) and the form control Is Hyperlink set to Yes. I was lazy and didn't give that control a meaningful name. Or maybe in the wee hours of one morning I was just too tired to care
Hope it helps.

WordAutoExec.accdb

[davis1118]

Wow, just wow! This all works amazing. That is a lot more code than I would have ever thought would be needed to accomplish this. I would have never been able to get that figured out. But I can't thank you enough for getting this all sorted out. You even added comments to explain the functions, which is a huge help to someone like me. I have tried to break it, and so far everything is working great. All I have changed so far, was put a 2 second timer on the message box when a file is opened. That way it notifies you that the operation was successful, keeps the application on top and then the message closes. It shouldn't be a big deal anyway, because we have a dual monitor setup at work. But I have to say, this by far exceeds what I was expecting!

Thank you very much Micron! - David