I have an Excel file which checks its local version with the current version from a database. The code to check the version isn't important to the question.

If there's a new version I want to download it, close the old file (because I can't change/modify it while opened), replace it with the new downloaded version and open the downloaded version.

What I have is something like this:

file.xlsm

Private Sub Workbook_Open()
  Workbooks.Open ThisWorkbook.Path & "\update.xlsm"
End Sub

update.xlsm

Private Sub Workbook_Open()
  Workbooks("file.xlsm").Close
  Dim num As Byte
  Dim WHTTP As Object
  On Error Resume Next
  Set WHTTP = CreateObject("WinHTTPrequest.5")
  If Err.Number <> 0 Then Set WHTTP = CreateObject("WinHTTPrequest.5.1")
  On Error GoTo 0
  WHTTP.Open "GET", "http://path/file.xlsm", False
  WHTTP.Send
  num = FreeFile
  On Error Resume Next
  Open ThisWorkbook.Path & "\file.xlsm" For Binary Access Write As num
  If Err.Number <> 0 Then
    Workbooks(ThisWorkbook.Path & "\file.xlsm").Close
    Open ThisWorkbook.Path & "\File.xlsm" For Binary Access Write As num
  End If
  On Error GoTo 0
  Put num, , WHTTP.ResponseBody
  Close num
  Workbooks.Open ThisWorkbook.Path & "\file.xlsm"
  ThisWorkbook.Close
End Sub

The issue is that since update.xlsm was opened from file.xlsm, once I close file.xlsm, the code from update.xlsm stops running.

I found this thread which is pretty much what I want to do but I couldn't figure out how to get the Application.OnTime working.

Here's where I got the code to download the file.

Edit:

Ok, so I got it almost fully working with the following:

server file.xlsm

Private Sub Workbook_Open()
  'Workbooks.Open ThisWorkbook.Path & "\update.xlsm"
End Sub

local file.xlsm

Private Sub Workbook_Open()
  Workbooks.Open ThisWorkbook.Path & "\update.xlsm"
End Sub

local update.xlsm

ThisWorkbook:

Private Sub Workbook_Open()
  Application.OnTime Now, "test"
End Sub

Module:

Sub test()
  Workbooks("file.xlsm").Close
  Dim num As Byte
  Dim WHTTP As Object
  On Error Resume Next
  Set WHTTP = CreateObject("WinHTTPrequest.5")
  If Err.Number <> 0 Then Set WHTTP = CreateObject("WinHTTPrequest.5.1")
  On Error GoTo 0
  WHTTP.Open "GET", "http://path/file.xlsm", False
  WHTTP.Send
  num = FreeFile
  On Error Resume Next
  Open ThisWorkbook.Path & "\file.xlsm" For Binary Access Write As num
  If Err.Number <> 0 Then
    Workbooks(ThisWorkbook.Path & "\file.xlsm").Close
    Open ThisWorkbook.Path & "\File.xlsm" For Binary Access Write As num
  End If
  On Error GoTo 0
  Put num, , WHTTP.ResponseBody
  Close num
  Workbooks.Open ThisWorkbook.Path & "\file.xlsm"
  If Workbooks.Count = 1 Then
    Application.Quit
  Else
    ThisWorkbook.Close
  End If
End Sub

The problem I'm getting now is the new downloaded file from the server gets corrupted in some way (it works after the message of Excel repairing the file).