Dealing with VBA error 1004 when refreshing all data connections due to connection only queries

dougmarkham

Active Member
Joined
Jul 19, 2016
Messages
252
Office Version
  1. 365
Platform
  1. 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:

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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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:

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.
Greetings, What I do for those error codes I don't want to see and have no impact:
On Error GoTo 100

Then put 100 at bottom of your code just before end
 
Upvote 0
Greetings, What I do for those error codes I don't want to see and have no impact:
On Error GoTo 100

Then put 100 at bottom of your code just before end

Hi Rileys98,

Yeah, I sometimes use a similar thing, and I get the same issue using that method too.

To clarify what's occurring here: the For loop goes around fine for 3 queries that are writing the data to a worksheet table; however, when the For loop gets to the connection only queries, no matter which error code is used (On Error Resume Next, On Error GoTo 100, etc.), the 1004 error is coming up. Also, I think that the error code works for the first connection only query, then subsequently fails on the second and third connection only query! That would seem to be a key issue.

I'm looking for a way to overcome this issue where the second occurence of an error within a loop causes the error regardless.

I assume it has something to do with the fact that the first instance of "On Error....do this", the code works once, but on the next For loop cycle, it's not able to refer to previous instruction.
The honest truth is, I'm not that knowledeable using error code. I tend to try and code to get around errors rather than handle them.

The only reason I'm asking this time is because I've had a number of recent examples of this where error code in an 'While-' or 'Do-' or 'For-loop' stop working after the first pass in which an error is generated.

Kind regards,

Doug.
 
Upvote 0
Hi Rileys98,

I think I've found out what my problem has been caused by: something I'd never come across before which I found using a different Google search term.

Erorc.jpg


Here, in the Tools>>Options-->Error Trapping, my IT dept. had set the default to Break on All Errors, when it should be set to Break on Unhandled Errors.

This answer was by shahkalpesh found here: VBA Error Handling not working in Excel
 
Upvote 0
If an unhandled error occurs when loading a userform it will show you where the error occurs when you click debug. If you set it on Unhandled errors it will just take you the line of code that launches the userform.
 
Upvote 0
If an unhandled error occurs when loading a userform it will show you where the error occurs when you click debug. If you set it on Unhandled errors it will just take you the line of code that launches the userform.

Thanks for explaining, much appreciated! :)
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top