Is there a way to check if a connection in Excel is valid before refreshing it?-CodePudding

Quick Background: I have almost no experience with VBA. I started learning this two weeks ago and have made fast progress, but I am lacking a lot of knowledge. I do not know enough to know what I do not know, for lack of a better word. My apologies up front if this is a question that has a very simple solution. I have been scouring Microsoft's VBA documentation with no luck for several days now.

The Problem: I have a single connection in a main spreadsheet to a spreadsheet on SharePoint that pulls data from inputs by operators on their cell phones. The current way my code is set up, it will refresh that connection before running calculations to ensure that any new data is accounted for.

However, since the connected document's location (or even name) may change in the future due to it being a shared document, I wanted to find a way to test that the connection was valid; i.e. that the correctly named file existed where it was supposed to be.

Currently, if the file name or location do not match, Excel throws an error and my code will stop running. Ideally I would like to have something similar to this:

If connectionIsValid Then
    Refresh the connection
Else
    MsgBox "Could not refresh connection", vbInformation
    *Continue with code instead of throwing an error*
End If

I have tried things like:

If ActiveWorkbook.Connections(1).OLEDBConnection.IsConnected Then
    Run Code
End If

But after reading the documentation I realized the above is related to the MaintainConnection Property being true.

and:

On Error GoTo ErrorHandler
    *Refresh the sheet*
    *Continue code*
Error Handler:
    *Don't refresh the sheet*
    *Continue code*

Any help or even a pointer in the right direction is greatly appreciated. Thank you for your time.

CodePudding user response：

I'm not sure what is wrong with your second suggestion - using an error handler.

Function checkConnection()
    On Error GoTo errorHandler
        ActiveWorkbook.Connections.Item(1).Refresh

    Exit Function

errorHandler:
    MsgBox "Could not refresh connection. " & Err.Description
    
End Function

I've just added the Exit command there, otherwise it will run the code in the errorHandler label every time, even if there isn't an error.