Update excel sheet automatically every day

By now, I've created a macro that fetches a table from IE containing the NOK swap rates. After the table is stored in one sheet, the macro stores the values I need (3yr swap, 5yr swap and 10yr swap) in another sheet. This macro works (pretty) very vell, but now I need to make it a little more advanced and I can't figure out how to solve my problem.

2 problems:

• I would like the macro to only save one value each day, and I want that value to be the "close" value. By now, the macro create a new row and saves the values each time I run the macro.
• I would really like the macro to run by it self at a spesific time of the day. It really doesn't matter when, but maybe after 17.00 and before 24.00.

To summerize I would like to tweek my original macro to only create one row a day (3yr, 5yr, 10yr swap) and I would really like it to update automatically.

See attached workbook.

Best regards
Thomas

Hi there,

Take a look at the attached version of your workbook and see if it does what you need. It uses the following code in the VBA CodeModule of the "Swap" worksheet:

Please Login or Register  to view this content.

and the following code in the VBA CodeModule for "ThisWorkbook":

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.

These statements should be included for "normal" operation, and commented out for demonstration puropses.

These statements should be included for demonstration purposes, and commented out or deleted for "normal" operation.


I don't have access to your "Swap.xlsm" workbook, so I can't test the operation of your "Worksheet_Change" routine.


Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi again,

Sorry, the "CancelProcedure" routine should have included the following highlighted lines:

Please Login or Register  to view this content.

Regards and apologies,

Greg M

Thank you very much, Greg. I seems to work pretty fine, but I'm struggling to implement my original code to store the values I need (3yr swap, 5yr swap and 10yr swap) in another sheet.

Do you see what I'm doing wrong.

Please Login or Register  to view this content.

Hi again,

Many thanks for your feedback.

See if the attached workbook does what you need. It uses the following code in a standard VBA CodeModule:

Please Login or Register  to view this content.

and the following code in the VBA CodeModule for "ThisWorkbook":

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.

Include these lines for demonstration purposes and delete them for "normal" execution.

Comment-out this line for demonstration purposes and include it for "normal" execution.


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M