Auto-updating Power Query Connection via VBA when closing a file

Question

I have an excel workbook (which is updated daily) to which I have established a couple power query connections, they look like this:

IMG

When clicking the little button to update the connection it works fine, updates, new lines are added, changes in data happen. I've then set out to execute this update through VBA in an automatic fashion when the data source files finishes being worked on and is closed (In a manner that would make opening the file with the queries and manually updating them unnecessary), following are examples of what I've tried(All using the "Workbook_BeforeClose" event): Method 1 (Unsuccessful):

Sub Workbook_BeforeClose(cancel As Boolean)
    CarryOn = MsgBox("Update connections? (May take a minute)", vbYesNo, "Update connections")
    If CarryOn = vbYes Then
        Dim strFilename As String: strFilename = "R:\filepath\Querydestination.xlsm"
        Dim QD As Workbook 'QD = Query Destination
        Set QD = Workbooks.Open(Filename:=strFilename)
        QD.RefreshAll
        QD.Connections("Query - Database").Refresh
        QD.Connections("Query - Support$_FilterDatabase").Refresh
        QD.Save
        QD.Close
        ThisWorkbook.Save
        Beep
        MsgBox "Connections updated!"
    End If
End Sub

I've since found the code from the first answer of the following post:

Auto-updating Power Query Connection via VBA

The code currently looks like this:

Sub Workbook_BeforeClose(cancel As Boolean)
    CarryOn = MsgBox("Update connections? (May take a minute)", vbYesNo, "Update connections")
    If CarryOn = vbYes Then
      Application.DisplayAlerts = False
      ThisWorkbook.SaveAs Filename:= _
      "R:\filepath\Database.xlsb" _
      , FileFormat:=50
      Dim strFilename As String: strFilename = "R:\filepath\Querydestination.xlsm"
      Dim QD As Workbook 'QD = Query Destination
      Set QD = Workbooks.Open(Filename:=strFilename)
      Dim con As WorkbookConnection
      Dim Cname As String
      For Each con In ActiveWorkbook.Connections
          If Left(con.Name, 8) = "Query - " Then
          Cname = con.Name
              With ActiveWorkbook.Connections(Cname).OLEDBConnection
                  .BackgroundQuery = False
                  .Refresh
              End With
          End If
      Next
      QD.Save
      QD.Close
      ThisWorkbook.Save
      Beep
      MsgBox "Connections updated!"
      Application.DisplayAlerts = True
    End If
End Sub

Here's the problem: I've tried Refresh_all, Update connections, Updtade individual connection by name and a bunch of other stuff, only the method in the link and shown in the code above has worked. Sometimes it hiccups in the .Refresh line but thats it. I works on my machine, when opening the Query Destination file new lines and changes are there, without the need to manually open the file and hit the "Refresh" or "Refresh all" buttons. The only matter is: the code runs smoothly sometimes, but sometimes it doesn't and throws an error: [IMG] [IMG].

I really don't know what is causing it, the queries destination workbook is not open and nobody is using it, I've made sure of that. How to stop this error from occuring?