Automatically run macro before trying to open file

Can you post a sample of your existing code and note where it is located (personal macro workbook, specific workbook, add-in...)

Try this (I have not changed the IsVPNConnected function):

Option Explicit
Sub OpenVPNFiles()
    Dim intFilesSelected As Integer
    Dim strFilePath As String
    Dim i As Integer
    
    If IsVPNConnected Then
    
        'Get files to open
        With Application.FileDialog(msoFileDialogOpen)
            .AllowMultiSelect = True
            .InitialFileName = "L:"
            intFilesSelected = .Show
        
            'Open the selected files
            If intFilesSelected <> 0 Then
                For i = 1 To .SelectedItems.Count
                    strFilePath = .SelectedItems(i)
                    Workbooks.Open Filename:=strFilePath
                Next i
            End If
            
        End With
    
    Else
        MsgBox "The VPN is not currently connected."
    End If
     
End Sub


Function IsVPNConnected()
Dim objShell As Object
Dim objExecObject As Object
Dim strline As String
IsVPNConnected = True
Set objShell = CreateObject("WScript.Shell")
Set objExecObject = objShell.Exec("%comspec% /c ipconfig.exe /all")
Do Until objExecObject.StdOut.AtEndOfStream
    strline = objExecObject.StdOut.ReadLine()
If strline Like "*Description*fortissl" Then GoTo VPNconnected
Loop
IsVPNConnected = False
MsgBox "No VPN"
VPNconnected:
End Function

You should save all of it to your Personal Macro Workbook so that it is available every time you use Excel and then to run the macro you should assign it to a button which you can put on the Quick Access Toolbar. Then, any time you want to open a file on the L: drive, you just click that button instead of using Excel's usual open file dialog.

The macro first checks the VPN is connected and if it isn't, displays a warning and does nothing else. If it is connected, it launches the file open dialog at the L: drive so you can select which files there you want to open. This way it will work for any file on that drive that you want to open and only when the VPN is connected.

I must stress that I have not been able to test your IsVPNConnected function and I do not know enough about shell objects to see if the code seems correct. Providing it does what it is supposed to and only returns true if the VPN is connected then my code should work.

Originally Posted by GCW esq

...Sometimes I open files from the Recent Items list or from other shortcuts. I guess it wouldn't work with those.

I suspect the issue of your half minute lag while it realises the VPN is not active is down to the operating system. The other shortcuts and the recent items list you use effectively just hold the path of the file as a string and clicking them instructs Excel to open a file at that location without the need for the file open dialog. If the file isn't there, the operating system tells Excel and Excel warns you the file is no longer there, in most cases because it has been moved or renamed since the recent item or shortcut was created but in this case because the L: Drive is no longer mapped. Nevertheless, Excel doesn't know it isn't there until it tries to open the file.

It would be awesome if Excel had a 'BeforeOpen' event that could be monitored, but where would it go? In the workbook you are trying to open? Unless you open the file, Excel wouldn't be able to run any code within it, so it couldn't go there. In the Personal Macro workbook? In order for it to work here it would need to be an application level event which means you need to set up a class to monitor the application events first. There is even a WorkbookOpen event that seems to fire before a selected workbook opens but it doesn't have a cancel option so while you can run code on the open event, you can't stop the open event from happening if certain criteria are not met.

I think to do what you want is going to require moving outside Excel's functionality and monitoring the operating system events somehow and capturing any requests to open files on the L drive and processing them instead. I'm sure that's possible from within VBA by declaring functions in system libraries, but somebody else is going to need to talk you through that one.

Glad you approve! I love a good explain. Lol Thanks for the rep.