Refreshing Excel Queries with Task Scheduler

Question

I am trying to automate Query refresh in MS Office Professional Plus 2016.

I have a cmd script which runs vbs script which runs Excel macro. Everything works if I run it manually. The problem occurs when I set up Windows Task Scheduler and select the option "run whether user is logged on or not".

My macro is saving query result log to text file so I can determine where the code breaks. Looks to me that Excel displays an alert box (or something similar) when running with Task Scheduler. I can not determine what is expected from user since the scheduler hides all alerts. There are no alerts/prompts if I run the cmd script manually or via Task Scheduler with option "run only if user is logged on".

Here is my RefreshQueries() sub. I tried commenting the code and confirmed that line that breaks the whole automation is .Refresh inside With iTable.QueryTable .

Private Sub RefreshQueries()
    AddToLogFile ("Hello from subroutine RefreshQueries().")

    Dim iWorksheet As Excel.Worksheet
    Dim iTable As Excel.ListObject

    'Check each worksheet.
    For Each iWorksheet In Excel.ActiveWorkbook.Worksheets
        AddToLogFile ("For-loop for iWorksheet " & iWorksheet.Name)

        'Check all Objects if it is a query object.
        For Each iTable In iWorksheet.ListObjects
            If iTable.SourceType = Excel.XlListObjectSourceType.xlSrcQuery Then
                AddToLogFile ("Trying to refresh iTable: " & iTable.Name)
                QueryTimeStart = Timer

                On Error Resume Next
                With iTable.QueryTable 'Refresh the query data.
                    .BackgroundQuery = False 
                    .EnableRefresh = True
                    .Refresh
                End With

                If Err.Number <> 0 Then
                    QueryRunTime = CalculateRunTime("QueryRunTime") 'Stop timer and get the duration.
                    Call AddToHtmlErrorTable(iTable.Name, Err.Number, Err.Description, QueryRunTime) 'Add entry to error table.
                    AddToLogFile ("Query in iTable " & iTable.Name & " failed. Description: " & Err.Description)
                    NumberOfFailedQueries = NumberOfFailedQueries + 1 'IMPORTANT: increment must be after updating html error table!
                    Err.Clear 'Clear errors between for loops.
                Else
                    NumberOfSuccessfulQueries = NumberOfSuccessfulQueries + 1
                    AddToLogFile ("Query in iTable " & iTable.Name & " successfully refreshed.")
                End If

            End If
        Next iTable
    Next iWorksheet

    AddToLogFile ("Exiting subroutine RefreshQueries().")
End Sub

I guess my question is as follows:

1. can we somehow catch what prompt Excel is showing in the background (nothing pops up if I run it manually), or
2. can we confirm any shown message in Excel automatically (without knowing what it is), or
3. are there any known settings which would execute the connection without any confirmation.

Does anyone have an idea, experience, or suggestion regarding this issue?

Answer 1

You need to add error catcher to your VBA routine like described here

Private Sub RefreshQueries()
On Error Goto MyError

' .... All your code

Exit sub
MyError: 
'Do your magic here with Err.object to log the event or whatever
AddToLogFile ("#Error in RefreshQueries().:" & Err.Discription)
Resume Next
End Sub