Issue with the Auto Run Macro under Workbook Open Declare

[kelvin-lkh]

Hi All,

I have create a macro under 'Thisworkbook' in personal.XLSB which it will auto recall another macro "autoRefreshNoetix" at specified timing whenever workbook is open.

The reason I create this is I cannot find any other way to auto run the macro even when workbook is not open.

However, the issue I face with this macro is I have to close all excel workbook and re open any excel workbook after the pre-set timing 10:15:00 in order to make this macro auto run on the next day.

If the macro has already been executed at today 10:15:00 but without all the excel being closed/windows restart, the macro will not be executed again in subsequent day at the pre-set timing 10:15:00.

Is there solution to resolve this issue? Appreciate for your input/advice.

 

[NARAYANK991]

Hi ,

I am not able to understand ; can you first explain what your requirement is ? What do you want to do ? Rather than understand why your macro is not doing what you want it to do , it may be better to know what your requirement is. This will help to know :

1. Whether what you want done can really be done ; it might very well be impossible.

2. Whether the method you have used to go about it is the right method or not.

Narayan

 

[kelvin-lkh]

Hi Narayan,

At first, I have setup a macro and wanted the system to auto run the macro at a pre-set timing everyday even when there is no any excel workbook is open at the time.

However, I realise that it is impossible as there should be at least any one of the excel workbook remain open so the macro will be run automatically at the pre-set timing.

Hence, I have put a auto run procedure(Private Sub workbook_Open()) at my personal workbook under workbook 'open' condition. Hence, the macro procedure(Sub Autorefreshnoetix()) will auto be run everyday at 10:15:00.

But, it is only working if I at least close all the excel file and reopen any again before 10:15:00.

If I any excel workbook at day 1 09:00:00 and do not ever close all the excel workbook in between until day 5. This Private Sub workbook_Open() will only run at day 1 10:15:00 but not for day 2-day5.

Hope my illustration above helps.

 

[NARAYANK991]

Hi ,

Somewhat , but not fully.

Can you post the macro that you have in your Personal workbook ?

Narayan

 

[kelvin-lkh]

Hi Narayan,

The macro has already been attached in my first post.

Here I attached again. Pls let me know if have any qns.

Thanks.

 

[NARAYANK991]

Hi ,

It is clear that for the Workbook_Open macro to fire , a workbook has to be closed and reopened ; if you are going to have a workbook open over 4 or 5 days , then you need to use a different mechanism to trigger the macro.

Can you go through this and see if it helps ?

http://stackoverflow.com/questions/...all-a-macro-at-a-set-time-everyday-without-ha

A similar question was asked and answered here :

http://www.mrexcel.com/forum/excel-...sic-applications-run-macro-specific-time.html

Narayan

 

[kelvin-lkh]

Hi Narayan,

This is what I am looking for. Thanks so much...