How to close all the hyperlinked files automatically?

[arunpoulose]

Hi friends,

Is there any way to close all the hyperlinked files/documents when the excel workbook is closed using VBA?

Any help would be very much appreciated. Thanks in advance.

-Arun

[arnelgp]

you need to find a way to Add to an Array/Collection/dictionary all Links you have opened.
on the Workbook close event of your workbook, add code to close them (probably by API call).

[arunpoulose]

Thank you so much for your reply arnelgp. I am a newbie to VBA. I will search more on what u said. Thanks for the lead.

[arunpoulose]

Hi,

From 2 days of learning VBA, i came up with a code to add all my Hyperlinks to a collection as a first step to achieve this task. But somehow the hyperlinks are not added to the collection I made. I am posting the code here below:

Sub hyperlinksaccess()
Dim Hyperlinkscollection As New Collection
Dim lnk As Variant
For Each lnk In Worksheets("Prozesslandkarte2").Hyperlinks
Hyperlinkscollection.Add (Item)
Next lnk
For Each Item In Hyperlinkscollection
MsgBox Item
Next Item
End Sub

The messagebox is not showing the Hyperlinks. Its just a blank Messagebox. But when I used the COUNT method it perfectly counted all the hyperlinks from my collection. Any help would be much appreciated.
Thanks in advance

[georgiboy]

You can try the below in the ThisWorkbook module:

Option Explicit

Dim OpenedFiles() As Variant
Dim cnt As Long


Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    ReDim Preserve OpenedFiles(cnt)
    OpenedFiles(cnt) = Target.Address
    cnt = cnt + 1
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim x As Long
    
    For x = 0 To UBound(OpenedFiles)
        Workbooks(OpenedFiles(x)).Close False
    Next x
End Sub

It adds the linked files to an array as they are opened via the link, on workbook close it will close all of the opened files with the active workbook.

Could have errors if you are opening anything other than workbooks, may also error if you close one of the opened files before the code has a chance to close it. Should be able to get around the second issue with error handling.

[snb]

VBA is a programming language aimed at the programs (Applications) in the MS office suite.
Since hyperlinks can refer to programs other than MS office programs (e.g. PDF's) the closing of those files, opened by hyperlinks can only be done on the next higher level: Windows.
I'd suggest to learn VBA in the Office Suite first before digressing to the Windows level.

[arunpoulose]

Thank you for the suggestion snb

[arunpoulose]

You can try the below in the ThisWorkbook module:

Option Explicit

Dim OpenedFiles() As Variant
Dim cnt As Long


Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    ReDim Preserve OpenedFiles(cnt)
    OpenedFiles(cnt) = Target.Address
    cnt = cnt + 1
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim x As Long
    
    For x = 0 To UBound(OpenedFiles)
        Workbooks(OpenedFiles(x)).Close False
    Next x
End Sub

It adds the linked files to an array as they are opened via the link, on workbook close it will close all of the opened files with the active workbook.

Could have errors if you are opening anything other than workbooks, may also error if you close one of the opened files before the code has a chance to close it. Should be able to get around the second issue with error handling.

Thank you so much for your help. I will try this next time when I am at the office and let you know.

[arunpoulose]

Hi georgiboy,

So I tried the code you suggested. I got a runtime error '9' on the line :

For x = 0 To UBound(OpenedFiles)

Any idea why it is so? Thank you.

[georgiboy]

Had you opened any of the hyperlinks at the point of closing the file with the code in?

Was it an excel file you opened with the hyperlink?

[arunpoulose]

Yes a hyperlinked excel file was open before closing the workbbook.

[georgiboy]

Do you have any other code in the workbook? Any code that could be disabling events?

If you want to close all instances of Excel when closing a specific workbook then maybe you could just close the application all together.

[arunpoulose]

No, there is no other code in the workbook.

Actually I have some other hyperlinks too. They are Excel and Word files. But this time when I was using your code i opened only one hyperlinked excel file. What I would like to have is that the user open the main master file which is an excel file, navigate through all hyperlinks and at the end there will be so many excel files and word files open in the background. So in one click while closing the master file all other linked files should be closed.

[snb]

To that purpose MS has invented 'workspaces'.
Or you might use Querytables ('connections').
Hyperlinks are not meant to be opened and closed simultaneously to the workbook.

[georgiboy]

You can try the below although in the long run I don't think it will fully accomplish your needs.

Option Explicit

Dim OpenedFiles() As Variant
Dim cnt As Long

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    Dim tmp As Variant
    
    tmp = Split(Target.Name, "\")
    ReDim Preserve OpenedFiles(cnt)
    OpenedFiles(cnt) = tmp(UBound(tmp))
    Debug.Print tmp(UBound(tmp))
    cnt = cnt + 1
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim x As Long
    
    On Error Resume Next
        For x = 0 To UBound(OpenedFiles)
            Workbooks(OpenedFiles(x)).Close False
        Next x
    On Error GoTo 0
End Sub

It may help though

[arunpoulose]

Thank you georgiboy.

[arunpoulose]

Hi snb,

Thank you for your reply. You told that VBA is only for MS Office applications. If my hyperlinks consists of only excel and word files, is it achievable?

[snb]

Did you look into workspaces and querytables ?

[macropod]

Cross-posted at: vba - How to automatically close all open hyperlinked files (excel and word files) when the masterfile (Excel file) is closed? - Stack Overflow
Please read VBA Express' policy on Cross-Posting in Rule 3: http://www.vbaexpress.com/forum/faq...._new_faq_item3

[georgiboy]

I have cobbled the below together which seems to work with Excel, Txt & Word files. It kinds shows that things can get a bit tricky when dealing with windows. It's far from perfect and would need some further error handling etc...

Option Explicit

Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Long) As Long


Dim OpenedFiles() As Variant
Dim cnt As Long

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    Dim tmp As Variant
    Dim hWnd As Long
    Dim lnghWnd As Long

    tmp = Split(Target.Name, "\")
    ReDim Preserve OpenedFiles(cnt)
    OpenedFiles(cnt) = GetAllWindowHandles(CStr(tmp(UBound(tmp)))) & "|" & tmp(UBound(tmp))
    cnt = cnt + 1
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim x As Long
    Const WM_CLOSE = &H10
    
    On Error Resume Next
    For x = 0 To UBound(OpenedFiles)
        If Right(Split(OpenedFiles(x), "|")(1), 4) Like "xls?" Then
            Workbooks(Split(OpenedFiles(x), "|")(1)).Close False
        Else
            SendMessage Split(OpenedFiles(x), "|")(0), WM_CLOSE, 0, 0
        End If
    Next x
    On Error GoTo 0
End Sub


Function GetAllWindowHandles(partialName As String)
    Dim hWnd As Long, lngRet As Long
    Dim strText As String

    hWnd = FindWindowEx(0&, 0&, vbNullString, vbNullString)
    While hWnd <> 0
        strText = String$(100, Chr$(0))
        lngRet = GetWindowText(hWnd, strText, 100)

        If InStr(1, strText, partialName, vbTextCompare) > 0 Then
            GetAllWindowHandles = hWnd
        End If
        hWnd = FindWindowEx(0&, hWnd, vbNullString, vbNullString)
    Wend
End Function