Suppressing Data Source errors RRS feed

  • Question

  • Hi, I have a couple of Append queries that append 33 connection only queries (from 33 Excel files that can be open or closed) and are set to show only the last row of each of the individual queries. I'm using this as a live status file on the 33 connection only files by showing the last used row.

    I have used the OnTime VBA routine to refresh the append queries every few minutes. This works fine most of the time until very occasionally I get a Data Source error from one of the 33 connection only queries. I think this may be when someone is editing the one of the Excel source files. I'm not particularly worried about the error as a couple of minutes later the query will refresh again and that error will not appear.

    What I'm trying to find is a method of suppressing the error pop-up which is pausing the subsequent refreshes similar to how Application.DisplayAlerts = False will do to other Excel alert pop-ups. Is there a way of getting the append query I have to 'ignore' or show an alternative value if a data source error occurs in one of the source queries?

    • Edited by Grizz_UK Friday, August 26, 2016 10:28 AM
    Friday, August 26, 2016 10:27 AM


  • 1) Use error-handler "try...otherwise":  add a last step to each of your 33 queries with the error-handler " try x otherwise y" and define y as a table with no rows

    2) If you don't want to edit that many queries, you can write a function with the error-handling and use that on a table with one query name in each row. Then the Table.ExpandTableColumn-command will be used instead of Table.Combine to create the consolidated table.

    Just let me know if you need any assistance on the realization here.

    Imke Feldmann

    Saturday, August 27, 2016 9:19 AM