I put in code to close active workbook and now I can't open the workbook

[Brennivin]

I have VBA in a workbook that executes a large amount of code when the workbook is opened, under Private Sub Workbook_Open()

After executing the code, the workbook stayed open. I run a scheduled task every night that opens the workbook in a new instance of excel, so the code gets executed. The problem arose if the workbook was open when the scheduled task ran -- since the code was set to execute ON OPEN, none of it executed since the workbook was already open.

So, I set forth to put in an ActiveWorkbook.Close True statement at the bottom of the Private Sub Workbook_Open() subroutine. That does the task too well -- I can't open the workbook anymore.

I'd really appreciate any help. Ideally, I would like for the workbook to be closed if it were opened in a new instance of excel, but to stay open if opened in an already existing excel instance.

Thank you

 

[RoryA]

You can hold down the shift key while opening the file to disable the open event so that you can edit the file.

You might try checking the count of visible workbooks when you open the file - if there are any, then it was probably an existing instance of Excel so don't quit at the end.

 

[Brennivin]

Ok, I was able to keep the workbook open by hitting Ctrl+Break to stop the VBA execution. And I have commented out the ActiveWorkbook.Close True statement at the end of the Private Sub Workbook_Open() routine.

I would still like to know though-- how do I make the workbook closure conditional on whether or not it is opened in a new or old instance of Excel?

 

[Andrew Poulsom]

Maybe you can create another workbook whose Workbook_Open event procedure opens the original workbook and closes it when everything's done. Then open that new workbook in your scheduled task.

 

[par60056]

2 suggestions:
1) capture Now() when the open starts and check the time it was opened to decide if it should close. You might also only want to run the process if it is opened at the scheduled time (between 1A and 1:10A
2) Check workbooks.count and see if there is another workbook in the instance and decide if it should close.

However, neither of these will help if you left it open when the task was scheduled.

 

[Brennivin]

2 suggestions:
1) capture Now() when the open starts and check the time it was opened to decide if it should close. You might also only want to run the process if it is opened at the scheduled time (between 1A and 1:10A
2) Check workbooks.count and see if there is another workbook in the instance and decide if it should close.

However, neither of these will help if you left it open when the task was scheduled.

I used a combination of the two, thank you very much!