asked on

Excel and Vba: loop for/next, how to set an automatic save

Hello everybody.

I have figured out a loop "for-next" that works all night grasping datas from the web and writing them on Excel.
It works well and usually, the following morning, I can find the work correctly executed.

But sometimes problems can happens, for example, because of wrong act by humans (switching the pc off) or external variables, like breakdown of the power supply.

Now, to fix this snags, I need to implement an automatic save, for example, every hour, or I think it could be better every 100 cycles and at the end of the work.

How can I instruct my macro, considering the lr value is usually a number over 1000?

For Each cell In ThisWorkbook.Worksheets("Sheet1").Range("A2:A" & lr)
'''''do stuff
Next cell

Open in new window

Thank's.

ste5an

This is hard to tell, cause the correct place, where you can call a Save is crucial. And it maybe not in the for-each loop.
Can you post your macro(s) or at least outline the structure of processing?

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

You don't need any code to save your workbook automatically. Instead, go to File->Options, choose 'Save' from the left-hand column and set the 'Save AutoRecover information' value to whatever you want as in this picture.

Paolo Crossi

ASKER

@ Rgonzo1971: the first test has been positive, this morning the work was correctly saved until row 2200 of 2235.

Now, following the same principle in order to save all the 2235 rows, I'm going to try inserting the same instruction (without if) also out of the loop, at the bottom of the macro.

I'll let you know tomorrow.

Paolo Crossi

ASKER

@ Rgonzo1971 it works perfectly.
Thank's.