4

I have a workbook with several PowerQuery queries to other workbooks.

This workbook is updated annually at each FY - meaning the queries need to point to new workbooks every year. This can of course be done manually, but there are a lot of queries and the average user does not know how (and also, much of the PowerQuery functionality is intentionally hidden from the user).

So I have written some code to allow the user to select a new workbook to point to, and then this code updates the PQ formula with the new source workbook. This all works fine.

The problem is, I need to ensure the user has selected a valid workbook so that the queries still work. My plan was to simply update the formula with the new workbook name, and then run a Refresh, and capture any errors (if the refresh works fine then my assumption is the workbook selected is a valid one).

But here's the problem. When I use the .Refresh method in VBA, no error is reported. There IS a user dialog reporting an error, but nothing is captured by VBA - the code continues running as if all was OK, and no error number is raised.

And moreover, the dialog does not react to Application.DisplayAlerts = False. It still shows, then the VBA code continues as if nothing has happened.

So does anyone know if there is any way I can capture this error? FYI the code I am using for the reresh is as follows (simplified):

Application.DisplayAlerts = False ' This does not stop the user dialog!
Dim conn as WorkbookConnection

Set conn = ThisWorkbook.Connections("MyQueryName")
conn.Refresh 'No error raised here, even if the Refresh fails
(there is however a user error dialog)

(NB the issue is the same if I use conn.OLEDBConnection.Refresh)

FYI according the MS documentation, I SHOULD be able to stop the dialog boxes, but it is not working for me (see https://msdn.microsoft.com/VBA/Excel-VBA/articles/workbookconnection-refresh-method-excel)

Thanks!

Jaspos
  • 365
  • 1
  • 4
  • 17
  • Just check if it is a valid workbook. Plenty of info about that around here. You could even open the sheet and check if it has the right columns for example. – Luuklag Sep 06 '17 at 07:35
  • @Luuklag - fully appreciate that's possible (that is in fact the workaround I am using) but if I can find an answer with the .Refresh method the code will be easier to maintain, as I won't need to update it if any new queries are added, which is very likely to happen. – Jaspos Sep 06 '17 at 12:19
  • Perhaps there is an event handler for this kind of error message? – Luuklag Sep 06 '17 at 14:04
  • What is the error message in the dialog box that appears? This would be a bug in Power Query. – Alejandro Lopez-Lago - MSFT Sep 06 '17 at 18:20
  • 1
    An example of the error in the dialog box is: [Expression.Error] The key didn't match any rows in the table. But the error is correct - if the newly selected workbook does not contain the correct data structure/tables then it will of course error. But I can't capture the error in VBA which is the problem. – Jaspos Sep 07 '17 at 07:18

1 Answers1

1

Two options:

  1. Check for the error in your Power Query M code. If you detect an error, output a different data table, perhaps a 1x1 table containing just an error code. When this error occurs, the PQ refresh completes successfully (as far as PQ is concerned), the user gets no error message, and your VBA code can check whether PQ produced the error table.

  2. Use an AfterRefresh event for the QueryTable, as described here: How to check whether Connection Refresh was successful

I've used #1 in the past. I've just discovered #2 and tried it. It works for me, but it doesn't prevent the PQ error message.

Richard Wein
  • 11
  • 2