How can I check for an ODBC error after a ....Refresh statement RRS feed

  • Question

  • I have some [simple] VBA code in Excel which attempts to refresh the data in a worksheet using an ODBC connection and SQL statement.

    The VBA code constructs the Connection string and also the SQL statement to run based on parameters entered by the user.

    This much is working perfectly:   and provided the file name which has been input by the user actually exists on the server, then the VBA refresh statement runs without any error.

    The problem is that when it fails (for example:  the file name input by the user does NOT exist on the server) then an ODBC error dialogue box appears.

    I read elsewhere the the dialogue boxes can be suppressed by the statement {Application.DisplayAlerts = False}.

    But then the error disappears completely and the ErrorHandler: is not triggered.

    What should I code following the Refresh statement in order to trap any error?

    Many thanks in advance.

    On Error GoTo ErrorHandler:
    ' Run the SQL statements as now established
    Dim Conn As Variant
    Dim ConnString As String
    For Each Conn In ThisWorkbook.Connections
        ConnString = Conn
        Application.StatusBar = ("Please wait while the system imports data into tab " & ConnString & "  ...")
        Application.DisplayAlerts = False
    ' when the above statement fails after {Application.DisplayAlerts = False} then if the refresh is cancelled after an error the VBA code is unaware of this and continues on to the next statement - despite the {On Error....}.
    ' without the {Application.DisplayAlerts = False} then an error in the Refresh is signalled in a pop-up widnow.

    Wednesday, February 15, 2017 1:08 PM

All replies

  • Why not use the Dir function to check if the file exists before executing the Refresh method?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, February 15, 2017 1:58 PM
  • in this line 


    your ConnString should be a number. So loop by ActiveWorkbook.Connections.Count


    So try this one

    Dim x%
    with ActiveWorkbook.Connections
        For x = 1 To .Count
    end with

    Oskar Shon, Office System MVP -
    if Helpful; Answer when a problem solved

    Thursday, February 16, 2017 1:32 PM