dougmarkham
Active Member
- Joined
- Jul 19, 2016
- Messages
- 252
- Office Version
- 365
- Platform
- Windows
Hi Folks,
I'm using Microsoft 365, so a current version of excel.
I'm having issues with the following code to refresh all the queries I have in my workbook:
I have 3 normal queries and 3 connection only queries. The code works fine when it's refreshing the normal connections; however, when it comes to refresh the queries with load status of 'connection only', then the VB editor returns the error message: Run-time error '1004': Application-defined or ojbect-defined error i.e., despite their being error handling in the code.
Weirdly, this has just started happening after I've returned from holiday---i.e., that excel / outlook is ignoring instructions to move to error control or to resume next etc., indicating something has changed with Excel.
Does anyone know what's happening here and how to fix this? I have already unticked "Enable background refresh" in the properties of each query.
Kind regards,
Doug.
I'm using Microsoft 365, so a current version of excel.
I'm having issues with the following code to refresh all the queries I have in my workbook:
VBA Code:
Sub Refresh_All_Data_Connections()
Call TurnOffStuff
Dim objConnection As Object
Dim bBackground As Boolean
For Each objConnection In ThisWorkbook.Connections
'Get current background-refresh value
On Error GoTo Err_Control
bBackground = objConnection.OLEDBConnection.BackgroundQuery
'Temporarily disable background-refresh
On Error GoTo Err_Control
objConnection.OLEDBConnection.BackgroundQuery = False
'Refresh this connection
On Error GoTo Err_Control
objConnection.Refresh
'Set background-refresh value back to original value
On Error GoTo Err_Control
objConnection.OLEDBConnection.BackgroundQuery = bBackground
Next
'MsgBox "Finished refreshing all data connections"
Err_Control:
If Err.Number <> 0 Then
If Err.Number = 1004 Then
Resume Next
Else
MsgBox Err.Number & " - " & Err.Description
End If
End If
Call TurnOnStuff
End Sub
I have 3 normal queries and 3 connection only queries. The code works fine when it's refreshing the normal connections; however, when it comes to refresh the queries with load status of 'connection only', then the VB editor returns the error message: Run-time error '1004': Application-defined or ojbect-defined error i.e., despite their being error handling in the code.
Weirdly, this has just started happening after I've returned from holiday---i.e., that excel / outlook is ignoring instructions to move to error control or to resume next etc., indicating something has changed with Excel.
Does anyone know what's happening here and how to fix this? I have already unticked "Enable background refresh" in the properties of each query.
Kind regards,
Doug.