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.
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.