CODEWITHSUNDEEP
excelvba
MilaB
MilaB

Reputation: 13

VBA Application.Ontime error 438. Object doesn't support this property or method

I've got a macro that has been working absolutely fine in Excel 2010. I'm now trying to run this file using Excel 2016 / 365 and the macro pops up with a run time error 438: Object doesn't support this property or method. It basically closes and re-opens the current Excel file that I'm working on ignoring any messages to save along the way and regardless of whether I'm read-only or not. If anyone can help then that would be fantastic. The code is below. Thanks in advance.

Sub reopen2()

Application.DisplayAlerts = False
Dim wb As Excel.Workbook
Set wb = ThisWorkbook

Dim pth As String
pth = wb.FullName

Application.OnTime Now + TimeValue("00:00:01"), Application.Workbooks.Open(FILENAME:=pth, Notify _
        :=False)
wb.Close (False)
Application.DisplayAlerts = True

End Sub

Upvotes: 1

Views: 1202

Answers (1)

GSerg
GSerg

Reputation: 78155

Application.OnTime's second parameter is explicitly declared as string, so this:

Application.OnTime Now + TimeValue("00:00:01"), Application.Workbooks.Open(FILENAME:=pth, Notify _
        :=False)

is actually:

Application.OnTime Now + TimeValue("00:00:01"), Application.Workbooks.Open(FILENAME:=pth, Notify _
        :=False).DefaultPropertyOfWorkbook

A workbook does not have a default property, hence the "Object doesn't support this property or method" error.

You have to put the code into a method and provide its name to OnTime as a string.

You want to pass the already closed workbook's full path to that method, so normally you would have to register the OnTime call with parameters.

In your case, though, you don't have to do that because all you want to do is open the workbook, and Excel remembers in which workbook the registered macro resides, and if scheduled to be called on time, it will reopen the workbook automatically just to call the macro, which is already the effect that you want. So your complete code would be:

Option Explicit

Sub reopen2()
  Application.OnTime Now + TimeValue("00:00:01"), "DoNothing"

  ThisWorkbook.Close False
End Sub

Public Sub DoNothing()
End Sub

provided it's in a regular module.

Upvotes: 1

Related Questions