VBA code to update query from a password protected excel workbook and then close workbook

I tried the code below as a timer, but all that happened is that, even though this was added after the refresh instruction, the blue circle spun for 5 minutes after the workbook open, then it closed and then it asked me for the passwords, reopened and then did the refresh after which I had to close the workbook manually.

newHour = Hour(Now())
newMinute = Minute(Now()) + 5
newSecond = Second(Now())
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True

I did wonder whether it had something to do with running the query in background, but when I changed this, the refresh didn't complete.

Originally Posted by MrChipper

I tried the code below as a timer, but all that happened is that, even though this was added after the refresh instruction, the blue circle spun for 5 minutes after the workbook open, then it closed and then it asked me for the passwords, reopened and then did the refresh after which I had to close the workbook manually.

newHour = Hour(Now())
newMinute = Minute(Now()) + 5
newSecond = Second(Now())
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True

I did wonder whether it had something to do with running the query in background, but when I changed this, the refresh didn't complete.

Ok. The following code worked. I used this Loop to delay the close of the workbook:

Sub WasteTime(Finish As Long)
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + Finish
waitTime = TimeSerial(newHour, newMinute, newSecond)
     
    Do
        DoEvents
    Loop Until waitTime <= Time()
 
End Sub