2

Suppose I have two hyperlinks (on excel sheet) referring to two documents:
e.g ( A.doc and B.doc ) on my local intranet.
I will open the first document "A.doc" then I will open the second one "B.doc"
The problem:
If there is already an opened word document and then I clicked hyperlink (Word Document on my local intranet), The later file is not opened automatically and I have to click on the flashing taskbar button to open the cited second file.
This issue occurs only with Microsoft word documents found on my local intranet.
If there is no open document and I clicked on any word hyperlink, It opens normally without any issue.
Please watch this short video to understand my problem.
I need to utilize FollowHyperlink event in excel or any other method to:
bring the previous opened window A.doc to front and then bring the second one B.doc to front.
you may find it a strange question!
But I have to do it manually each time to show and bring the second one to front.
I have used this API code (in a Word document) on Normal-ThisDocument: 

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
                   (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
Dim LHwnd As Long

Private Sub Document_Open()
    If Application.Documents.Count > 1 Then
       LHwnd = FindWindow("rctrl_renwnd32", Application.ActiveWindow.Caption)
       SetForegroundWindow (LHwnd)
    End If 
End Sub

And used that code on my excel sheet itself: 

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    On Error Resume Next
      Dim objWd As Object
       Set objWd = GetObject(, "Word.Application")
      AppActivate objWd.ActiveWindow.Caption
    Set objWd = Nothing
End Sub

Finally, I found this helpful page Bring an external application window to the foreground
But I could not adapted it to my need.

Waleed
  • 847
  • 1
  • 4
  • 18
  • I can't reproduce it - could it be some security stuff ... does it happen for local files (e.g. c:\[user]\documents) as well? – Ike Jan 16 '23 at 08:31
  • @Ike ,it happens only with Word_Document on my local intranet ,there was a security warning if I opened any word file from my local intranet, I have used a registry setting to suppress this message. – Waleed Jan 16 '23 at 08:44
  • I would question why you think it necessary to have either document active - or even visible for whatever you're doing. For a simple demonstration of how one can manipulate a document without it being active, see: https://www.msofficeforums.com/word-vba/21403-copy-table-content-between-documents.html – macropod Jan 16 '23 at 22:52

1 Answers1

1

Please, try the next BeforeDoubleClick event. If the problem is related only to hyperlinks, it should work...

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.column = 1 And Target.Value <> "" Then 'limit this behavior to the first column
        If LCase(left(Target.Value, 5)) = "http:" Then
            Cancel = True
             Dim objWd As Object, d As Object, arrD: arrD = Split(Target.Value, ".")
             If LCase(left(arrD(UBound(arrD)), 3)) <> "doc" Then Exit Sub
            On Error Resume Next
                 Set objWd = GetObject(, "Word.Application")    'find the Word open session, if any
            On Error GoTo 0
            If objWd Is Nothing Then
                Set objWd = CreateObject("Word.Application")
            End If
            
            With objWd
                .Visible = True
                Set d = .Documents.Open(Target.Value)
             End With
             
             'force somehow the new open document window expose its handler...
             Dim i As Long
             Do Until objWd.ActiveWindow.Caption = d.name Or _
                   objWd.ActiveWindow.Caption = left(d.name, InstRev(d.name, ".")-1) & " [Read-Only] [Compatibility Mode]"
                    i = i + 1: Debug.Print objWd.ActiveWindow.Caption, left(d.name, InstRev(d.name, ".")-1) & " [Read-Only] [Compatibility Mode]"
                    DoEvents: If i >= 10 Then Exit Do 'just in case, if something unexpected happens...
             Loop
             SetForegroundWindow CLngPtr(objWd.ActiveWindow.hWnd)
        End If
    End If
End Sub

It should work in 64 bit, but it is easy to be adapted for both cases, supposing that it works as you need.

FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • Unfortunately the same issue – Waleed Jan 16 '23 at 13:15
  • @Waleed Try placing a break point on the line `Set objWd = GetObject(, "Word.Application")` and run the code line by line (`F8`) and be atentive at what's happening. Do you see for a moment the newly open Word document? Does the code return a correct handler in the line `objWd.ActiveWindow.hWnd`? If you cannot see anything eloquent, try making a testing sub (in the same sheet code module) copying the code from `Dim objWd As Object` to `SetForegroundWindow CLngPtr(objWd.ActiveWindow.hWnd)` and run it? Doesn't it activate the respective Word window, too? – FaneDuru Jan 16 '23 at 13:50
  • I have placed a break point on the line `Set objWd = GetObject(, "Word.Application")`, but (F8) has no effect (as it is an event code). Also I tried as a testing sub ,then I clicked the first hyperlink and then then clicked the second one(`B.doc`) ,But again the second hyperlink are not opened and I cannot interact with any office interface (excel ,word or even vba window) until I click on the flashing taskbar of the first hyperlink (A.doc) (as you saw on my video). – Waleed Jan 17 '23 at 07:59
  • @Waleed 1. `F8` for running the code line by line works in any event! Does the code stop on the break point? If not, the event is not triggered... 2. It maybe would be good to tell us what kind of error did you use to receive before modifying something in Registry. It may be eloquent related to what's happening. Yo maybe suppressed only the message... 3. Hyperlink is firstly executed and only after that the event is triggered. So, you cannot open (in code, or in a different way) the same document. You need to set the Word window in foreground. – FaneDuru Jan 17 '23 at 09:00
  • @Waleed 3 bis. If convenient, it is not complicated to configure `DoubleClick` event for cells containing the full document path to simulate inside `Open` command. I can post such a code. But I am afraid of the same security issue which bothered and made you to make that change in Registry... – FaneDuru Jan 17 '23 at 09:03
  • I opened the cited Hyperlink on another PC (it is not have my registry setting), and this is the message that appears when opening document from our local intranet https://ibb.co/dLkdbfc – Waleed Jan 17 '23 at 09:23
  • @Waleed Do the documents in discussion contain VBA code? It looks that the Office consider them being dangerous. Do you also mean that **if you open such a document first time** such a message/warning is not raised? Or, after modifying a key in Registry, you can open it and show the Word window **if no any document has already been open before it**? – FaneDuru Jan 17 '23 at 09:40
  • 1.the documents in discussion have not any VBA code. 2. I have modified this registry key `DisableHyperlinkWarning`. without that modification , the warning message will appear every time I open hyperlink referring to a document on my local intranet (regardless there is already open document or not) – Waleed Jan 17 '23 at 11:45
  • 1
    @Waleed I cannot understand how Office works, from this point of view. I do not have any special settings and I cannot reproduce your problem. So, I will edit my answer and post an adapted `BeforeDoubleClick` event. I will test it on my computer (even if not so relevant in such circumstances)... – FaneDuru Jan 17 '23 at 13:01
  • @Waleed I didn't say anything about the need to eliminate the hyperlink, **I thought it should be obvious**... In order to do that, you should select all hyperlinks (more then one) and after right click `Remove Hyperlink` option appears as the last option in the context menu... – FaneDuru Jan 18 '23 at 08:17
  • Kindly see this question https://stackoverflow.com/questions/75156357/adding-a-custom-right-click-menu-to-open-hyperlinks-doc-and-pdf-extension – Waleed Jan 18 '23 at 08:21
  • @Waleed Did you try the above suggested solution **after hyperlinks removing**? – FaneDuru Jan 18 '23 at 09:31
  • @Waleed If if doesn't work in this way, it will not work independent of the calling method. – FaneDuru Jan 18 '23 at 09:43
  • I tried, and got **Run-time error 52: Bad file name or number** at this line `If Dir(Target.Value) <> "" Then`. If I commented this line (and it’s End IF) then the word document opens normally, But I cannot interact with excel interface until close the word file ,after closing the word document I got another **Run-time error 5825: Object has been deleted** at this line `Do Until objWd.ActiveWindow.Caption = d.Name` – Waleed Jan 18 '23 at 11:39
  • Please note that the hyperlinks starts with **Http:/** – Waleed Jan 18 '23 at 12:06
  • 1
    @Waleed `Dir(Target.Value)` may raise such an error for full names starting with `http`. This aspect can be handled in a different way, but for now let us try something else. I forgot iterating i inside the loop. What name does `d` show. In fact, please try the adapted code. – FaneDuru Jan 18 '23 at 12:08
  • 1
    @Waleed Besides all that, please try `Shell "WinWord.exe """ & pathToYourfile & """", vbNormalFocus`. Doesn't it open the document? What does `debug.print d.name` return in `Immediate Window` in the adapted event code? – FaneDuru Jan 18 '23 at 12:13
  • This time the adapted code works as it should, `debug.print d.name` returns the name of document (But repeated the same name many times). Using `Shell "WinWord.exe` is fantastic and even more faster than the adapted code , I even doubled the line of `Shell "WinWord.exe` to open more file at the same time and it works perfectly without any warning messages. – Waleed Jan 18 '23 at 12:50
  • 1
    @Waleed I tried the code answer at your request, based on your experience that opening it in its application (Word Application) it works as it should. I could not check the best solution in your specific environment, because your company does not allow a connection... The newly proposed version was only to check if **only opening the documents through hyperlink** the mentioned problem appears... So, I can answer your new question placing this code in the context menu new added control if you want. And for pdf case, the code needs to determine which is the associated application for this ext... – FaneDuru Jan 18 '23 at 13:10
  • @Waleed `debug.print d.name` was just to see if the correct name of the document is displayed. But now it works now as a small delay, since you can see it many times... Apropos, does it appear for 10 times? Just for the sake of research, place there `Debug.Print d.name, objWd.ActiveWindow.Caption` and see which is which. Theoretically, the window caption/title should be the document name. But it looks that it is not. So, what does it return in `Immediate Window`? Anyhow, this is only a temporary way of working,, to understand. Finally, the `Debug.Print` code line should be deleted/commented. – FaneDuru Jan 18 '23 at 13:17
  • the associated application for opening PDF files is `msedge.exe` and so easy to adapt it in `shell` line. – Waleed Jan 18 '23 at 13:26
  • I found out that I need to put the full path to **msedge.exe** inside the shell command `C:\Program Files (x86)\Microsoft\Edge\Application\msedge.exe` – Waleed Jan 18 '23 at 13:33
  • @Waleed If only you will use the code, I can agree. But many people use Acrobat Reader, Acrobat Reader DC, Acrobat Pro etc... – FaneDuru Jan 18 '23 at 13:33
  • I agree with you, But this will need an additional VBA step to determine the default application used to open *.PDF extension – Waleed Jan 18 '23 at 13:37
  • @Waleed This is what I was talking about... – FaneDuru Jan 18 '23 at 13:40
  • As you asked, `Debug.Print d.Name, objWd.ActiveWindow.Caption` will returen many lines (10) of `A.doc A.doc [Read-Only] [Compatibility Mode]` – Waleed Jan 18 '23 at 13:44
  • @Waleed That's why it shows it 10 times... I will try adapting to cover this aspect, too. – FaneDuru Jan 18 '23 at 14:21
  • @Waleed Adapted. Please, test the updated code and send some feedback related to `Debug.Print` number of returns. – FaneDuru Jan 18 '23 at 14:28
  • It works, but `Debug.Print` returned many lines as previous. I am sorry if i could not reply today as I will leave office after few minutes. – Waleed Jan 18 '23 at 14:43
  • @Waleed I only needed you to show how **exactly** a returned line in `Immediate Window` shows. Copied from there... If many, this means that you show previously was not exactly correct. Maybe, some more spaces, or something else... When you can and if you want. I do not care too much for myself. I just wanted to understand what's happening and how to be solved. – FaneDuru Jan 18 '23 at 14:57
  • The output of **Debug.Print** is 10 lines of this string exactly (without any space before and after) `A [Read-Only] [Compatibility Mode] A.doc [Read-Only] [Compatibility Mode]` , Also please see this screenshot https://ibb.co/Vj9NKYv – Waleed Jan 19 '23 at 06:01
  • @Waleed So, strangely the document extension is not shown in the window caption (as it should, in majority cases I know). I will adapt the code in a minute... Adapted. It should not even enter the loop, or it will show maximum 2 cases, I think. – FaneDuru Jan 19 '23 at 07:56
  • Now it works as expected ,and `immediate window` is blank which means code did not enter the loop as you wrote. I admire your insistence of fixing every little issue – Waleed Jan 19 '23 at 08:33
  • Just FYI, document extension is not shown in window caption **Because** the OS windows setting `File name extension` is not active (unticked) – Waleed Jan 19 '23 at 09:00
  • @Waleed I supposed that... I always set the folder option to show all extensions. I need to see them in some cases. – FaneDuru Jan 19 '23 at 09:04