4

I have a workbook that runs every x minutes with Application.OnTime.
The problem is that if I open a different workbook and get the yellow ribbon thing asking if I want to edit the file (or macro safety warning) the Application.OnTime stops working.

There is another question quite similar to mine here:
Application.ontime failes, when another workbook is open in "safe mode"
But the answer is not really an answer since that will just skip the line and make the code not run OnTime.

Is there anything that can be done to still allow Application.OnTime to run even if I have another file open in safe mode?

Improve this question
13
  • This is a shot in the dark, but do you get the same problem if you work in 2 different instances of Excel? Commented Sep 23, 2019 at 9:05
  • @FoxfireAndBurnsAndBurns As far as I know. That is what I get. I use Win 10 and it opens two windows down there. But if that translates to two different instances, not sure... How can I know? Commented Sep 23, 2019 at 9:07
  • 1
    @Andreas Thats a really interesting question, I upvoted your post and hope someone can help ! I am also interested by this answer Commented Sep 23, 2019 at 9:32
  • 1
    VBA works in each instance the same way. IF you open a file and block VBA, you are blocking VBA for all files opened in that instance. So the only option I see is that you need to open that conflictive workbook on a new instance, (and rest of files in default instance, not ALT key, just the conflictive one) so it won't be affected by other blocks of files. Commented Sep 23, 2019 at 9:39
  • 1
    @Andreas Let me try to get a workarounf. If i fail, I'll post my previous comment as answer Commented Sep 23, 2019 at 9:39

1 Answer 1

Reset to default
1

I think the solution here is opening that conflictive workbook in a different instance of Excel, so it won't be affected by other files.

To manually open a new instance of Excel, hold key ALT and click on the Excel icon. But in your case, if several people are using the file, then everybody needs to do that.

I've been looking around about creating a BAT file that opens that file in a new instance, but I'm totally noob about ms-dos commands so I'm posting this answer and hoping somebody can help.

To create a BAT FILE is really easy: just create a TXT, type the commands, save it and change file extension to BAT.

Be aware that I'm using my PC paths, so you need to adapt it to your paths. I've tried a lot of things but can't manage it to work it out:

  1. "C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE" will open Excel in a new instance.
  2. start "excel.exe" "C:\filepath\yourfile.xlsx" will open the file but in active instance. IF there is no instance, it will create a new one, but this does not work as you need it
  3. "filepath\yourfile.xlsm" will do the same than step 2

So I've been not able to find the exact commands. But I'm pretty sure there must be a way that a BAT file opens a new Excel instance (like in step 1) and opens the file in that instance.

About your macro, another shot in the dark, but you posted I have a workbook that runs every x minutes with Application.OnTime don't know how many times do you need to execute it, but maybe with Task Scheduler you could automate the process, because Task Scheduler got an option to open files in new instances.

Sorry, but could not find the exact commands. I'm able to open Excel in a new instance, (step 1) and I'm able to open a specific file, but I'm not able to combine both of them.

Hope somebody can help with that part, so you can create a BAT file to execute that conflictive workbook in a different instance just by double clicking on that BAT file (so everybody won't need to remember about the ALT thing)

WORKAROUND 2: Other way would be executing that Excel file but from a different APP, so you can create a new instance of Excel from VBA. I made a simple Word file that creates and opens an Excel file in a new instance, so you can work with rest of Excel files. The code must be in the event Document_Open.

Private Sub Document_Open()
Application.ScreenUpdating = False
Application.Visible = False
Dim ExcelAPP As Object
Dim ThisWB As Object
Set ExcelAPP = CreateObject("Excel.Application")
ExcelAPP.Visible = False
Set ThisWB = ExcelAPP.Workbooks.Open("filepath\yourfile.xlsm")
ThisWB.Close False
DoEvents
ExcelAPP.Quit
Application.ScreenUpdating = True
Application.Visible = True
Application.Quit False
End Sub

So just by opening this word file, your Excel File would be executed in a new instance and not visible.

KNOWN FAILS: This way is good if you already have an Excel instance opened, but if there is no Excel file opened (you close all of them), you execute this code, and then open an Excel File, the Excel file will be opened in that invisible instance, so it's not perfect.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Task scheduler will start Excel but not run the macro, unless the macro auto runs and saves it self and closes it self. Possible. But keeping the file open is probably easier. The macro runs between 2 and 8 minutes apart, it's random to make sure it's random who will do the slow task, update the database sheet. If I understnd your answer opening a new instance of this file is great. But what happens if I close all other files and only keep this file open. Then open a new file without ALT, Shouldn't that file open in the active instance (the problem file)?
Yes, if the only active instance of Excel is this one, and you open new normal excel file, it will be opened in this instance, due to being the only one available. If your file needs to be executed each 2-8 minutes, I'm afraid the best way is just keeping a PC turned on for that macro, and nobody uses that pc, because you are executing all the time the macro. Unless you dou the ALT thing or modify the registry, ofc.
Updated answer with another workaround, but not perfect >.<
Yes that would be the best option to have a computer dedicated to this task. But IT-department says no. All accounts must be personal, meaning no generic windows accounts and computers are auto locked after a few minutes. That is why we use a database sheet to gather what everyone has managed to get and then that data can be used.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.