Macros open server file hyperlinks in 2nd instance

Your Excel macros, which usually work fine, are suddenly opening server file hyperlinks in a new Excel instance instead of the same one. This means the macro can't "see" or interact with the newly opened file, causing errors. Manually clicking the hyperlink opens the file in the same instance as expected. The issue started with one user, spread to others, but disappears when users log in fresh on a different machine.

Possible Causes…

Macro Code Issues:

The way your macro is handling hyperlinks or file openings might be flawed, especially if it's old recorded code.

Excel Environment/Settings:

Excel's settings or security updates might have changed how it handles hyperlinks or new file instances.

There could be a conflict with add-ins or other Excel customizations.

Server/File Permissions:

The server might be treating macro-initiated file openings differently from manual ones, possibly due to security settings.

File locks or permissions could be blocking the macro's access, even if the file is already open manually.

User Profile Corruption:

Since the issue follows users across machines but is fixed with a fresh login, there might be some corrupted user profile settings or cached data affecting Excel's behavior.

Server Admin's Stance:
The server admin says it's not their problem, which might be partially true if the issue doesn't occur with manual file openings and only affects macro-initiated ones. However, server-side settings could still be influencing how Excel handles these requests.

This is likely a combination of macro code quirks, Excel environment settings, and possibly some user profile corruption. While the server admin might not be directly at fault, server settings could still play a role in how Excel handles these requests. Start by reviewing and updating your macro code, checking Excel settings, and investigating user profiles before escalating further.

Temporary Workaround for Your Macro

This will bypass the environmental trigger that causes the second instance.

Instead of using the hyperlink, get the file path from the cell and use Workbooks.Open directly, with some error handling to deal with the "file already open" scenario.

Sub OpenWorkbookFromHyperlinkSafe()

    Dim hl As Hyperlink
    Dim FilePath As String
    Dim targetWB As Workbook
    Dim currentSheet As Worksheet
    
    Set currentSheet = ActiveSheet ' Or specify the sheet, e.g., Sheet1
    
    ' Assume the hyperlink is in the active cell.
    ' You might need to change this to a specific range like Range("A1")
    If TypeName(Selection) = "Range" Then
        If Selection.Hyperlinks.Count > 0 Then
            Set hl = Selection.Hyperlinks(1)
            FilePath = hl.Address
        Else
            MsgBox "The selected cell does not contain a hyperlink."
            Exit Sub
        End If
    Else
        MsgBox "Please select a cell with a hyperlink."
        Exit Sub
    End If

    ' --- The Robust Opening Logic ---
    On Error Resume Next ' We'll handle errors manually
    
    ' Check if the file is already open
    Set targetWB = Workbooks(Dir(FilePath)) ' Dir() extracts the filename from the path
    
    If targetWB Is Nothing Then
        ' File is not open. Attempt to open it.
        On Error GoTo OpenErrorHandler
        Set targetWB = Workbooks.Open(FileName:=FilePath, ReadOnly:=True) ' Or False, as needed
        On Error GoTo 0
    Else
        ' File is already open. Activate it.
        targetWB.Activate
    End If
    
    ' --- Now interact with the newly opened/activated workbook ---
    On Error GoTo 0 ' Turn off error handling
    
    If targetWB Is Nothing Then
        MsgBox "Could not open or find the workbook: " & FilePath
        Exit Sub
    End If
    
    ' Safely try to select a sheet.
    ' This part is crucial. You need to know the sheet name or index.
    Dim targetSheet As Object ' Use Object for flexibility
    On Error Resume Next
    Set targetSheet = targetWB.Sheets("YourSheetName") ' Replace with your actual sheet name
    On Error GoTo 0
    
    If targetSheet Is Nothing Then
        MsgBox "Could not find the sheet 'YourSheetName' in the target workbook."
        Exit Sub
    Else
        targetSheet.Select
    End If
    
    ' ... rest of your macro ...
    
    Exit Sub
    
OpenErrorHandler:
    MsgBox "Error opening file: " & Err.Description & vbCrLf & "Path: " & FilePath
    Exit Sub

End Sub

I hope this information has been helpful to you.