Insert the current date and time once every day

[bobby1988]

I am looking to create a simple macro that will insert the current date and time once every day into a spreadsheet. This macro will need to be repeated for approximately 5 days. Your assistance is greatly appreciated!

[rberq]

Here's one way to do it:

 

Generate a random integer between zero and fifty-nine. Store it in an environment variable; or in a file if you want it to survive shutdowns and restarts of ME.

 

Schedule a macro to run every minute. The macro will:

1) Retrieve the stored number.

2) Compare the stored number to the current time (minute only).

3) If stored equals current, click the mouse, generate and store another random minute for the next click, exit.

4) If stored not equal current, exit without doing anything.

[Yehnfikm8Gq]

Hey rberg, isn't that your post for the random click every hour thread!

 

Save your spreadsheet in advance. Outline example below uses A1, B1 on day 1 and fills in downwards

 

Each day schedule macro to run, to:

Open spreadsheet

Type Ctrl+Home to get to A1

Type Ctrl+; to put date in A1

Type Right arrow

Type Ctrl+Shift+; to put time in B1

Type Enter

Type Ctrl+S to save the file

Close spreadsheet

 

On day 2, after "Type Ctrl+Home"

Type (1) Down arrow

Type Ctrl+; to put date in A2 etc

 

Each day increment number of down arrows by 1 (Increment an integer and use integer to repeat down arrow). If the scheduling of the operations is done within the macro (the macro runs continuously for 5 days) the value of the integer will be remembered. If it is desired to run the macro briefly each day or to cope with reboots etc, the integer will have to be saved before exiting the macro. There are several ways to do that (registry, save to file, save variable).

 

The cell navigation, including first cell selection, can be done in a number of different ways.

 

You can test in advance by running every 2 minutes (date will be same but time will increment)

[rberq]

Hey rberg, isn't that your post for the random click every hour thread!

Sure is. Don't know how it got here.

[Yehnfikm8Gq]

As it was late I didn't explore all the possibilities. Here's a few more ideas:

 

Ctrl+G opens the "Go To" dialog. You could construct the text string of the cell address to write the date into eg A3 and type that into the dialog.

 

Instead of remembering the daily incremented integer you could write it into a cell say C3. Read it next day for number of down arrow repeats, add one and write the incremented integer back into C3 for tomorrow. Avoids external variable storage.

 

To find the next available row you could run down the A column, reading each cell. When you get to one that is empty (retrieved text string is blank ""), put today's data on that row. This method needs no storing of variable.