Excel 2007 : Autosave

[scott41]

Good Morning all,

I have a shared workbook that may be used simultaneously by 2 or three people. Although i am constantly harping on about saving before and after every entry, occasionally there is a lapse and we loose data.

Is it possible to automatically save changes every minute or so?

Scott

[sauerj]

Hello, Yes, there are a couple different ways to do this.
1) Question: Are you loosing data because the PC or Excel crashes? Or, are people closing the file WITHOUT saving the file? ... If the latter, then they must be pretty dense (sorry to be so blunt), because, as you know, Excel WARNS you of loosing data if you quit w/o saving. With this warning, seems like it would be hard for anyone to loose data in this way (???).
2) If the PC is crashing, then this next idea should help. ... Under the menu bar selection (Tools / Options / Save), you can set the TIME between each Auto-Recover save. Set it to 5 or 10 minutes. This will at least save your working version of your file in case the system crashes. ... However, if crashes occur, then you will have to work with Recovery questions in order to retrieve the latest data, and this might throw your folks for a loop. So, this may not be a slam dunk solution.
3) You could get a macro added to the file(s). This macro would Auto-Save your file anytime a CHANGE is made AND the time since the file was LAST SAVED is greater than 'X' minutes. Does this sound like a better solution? Do you know how to work with macros? If someone showed you the code on this forum, could you add it to your file? ... There are incredibly smart people on this forum (it is truly amazing), and if you can manage the macro idea and request it, you will probably get an answer faster than you can say "LOST DATA"!

[scott41]

Good Morning SauerJ,

The smartness of forum members certainally has not passed me by.

We are loosing Data because a workbook is being used on 2 or 3 workstations at once and people are putting Data in the same place as other people, loosing one set of data.

The easy answer is for them to hit "save" before and after they enter anything, but that is proving difficult.

your suggestion (3) would be the answer, but i need to learn about Macros first i think :-)

Scott

[sauerj]

Hello Scott, I googled the following text: "excel shared workbook problems" and there are a number of good articles to read on this subject. I haven't read all of them, but many start off with what sounds like good information. I am not experienced enough on shared workbooks to give you "tried and tested" advice. But, I'm sure that the forum gurus are. An AutoSave macro may NOT be the best answer, as it might delete data if not designed correctly. I would suggest reading these articles; maybe this will get some ideas flowing. Sorry, not much more help.

[Genoel]

Hi Scott,


I think what you would want to do is go into the " Review" tab and click on " share workbook" under the advance options click "Ask me which changes win" this way everytime someone makes a change to the same workbook on a different computer, it will ask the users before they save if they want to save their changes on a specific cell, or accept changes made by other users in that same cell.

Let me know if that works, I might not be understanding your question here, but it's a start I guess.


Regards,

[sauerj]

Scott, Here is a simple AutoSave macro that MIGHT help you. WARNING: Use this with caution, as it might hurt more than it helps. But, I thought I'd post just in case with this WARNING. Following is instructions on moving macro from this attachment to your file.
1) Make a TEST copy of your file. Don't insert this macro into your MAIN file.
2) Open your new TEST file. Open attached file (AutoSave_Example ... ASE here on).
3) Open VB Editor. See Project pane on left. You should see a tree structure for both these open files. Click on +'s to expand both trees fully.
4) DOUBLE-Click on the module "ThisWorkbook" under the ASE file. You should see code appear in the code window (see CODE insert below for same). Using your mouse, "SELECT" all of this code, so that it is all back-lit in blue. Hit Cntrl+C to copy to PC buffer.
5) Now DOUBLE-Click on module "ThisWorkbook" under your new TEST file. You should see BLANK window for the code here. Put your mouse in this window, and hit Cntrl+V to paste the new code there.
6) Go back to the EXCEL window for this TEST file and SAVE it. The code should now be LIVE. It is setup to AutoSave the workbook if ANY CHANGE occurs AND it has been more than 3 minutes since the workbook was last saved.
7) If you don't like this code and want to stop it. Then go back to the ThisWorkbook module and change the line "Enable_Code = True" to "Enable_Code = False". ... OR, If you don't like 3 minutes and want, for example, 5 minutes, then change the (3 / 1440) to (5 / 1440)
... Good Luck and proceed with CAUTION on this one, sauerj

Please Login or Register  to view this content.

[martindwilson]

not a good idea ever to share workbooks where more than one user can enter data in the same cell/cells especially if you are all using it more or less at the same time. better would be to give them their own worksheet each in the shared workbook and consolidate the data at the end of the day(probably just copy paste onto a new sheet and sort would do that)

[scott41]

Evening all,

Firstly, i am already sharing the workbook Genol, but thanks anyway. I appreciate it is not best to share, but it makes the operation easier if we do.

I will have a play with the macro in the morning

Thanks

Scott

[walco johnson]

Hi Scott,

You might not even be working on your Excel workbook any more (as it has been two years since the last thread), but would like to find out if you managed to combine this macro together with sharing the file ? I've managed to enter the macro and make use of the Autosave when I'n not sharing the file. I need to find out how to combine the two (sharing and autosaving), as I receive the Error &H80004005 (-2147467259) and 'Project not Viewable' once I share the workbook and try entering the 'view code'. We are roughly 6 people on the shared excel workbook and you can imagine how saving arrangements have to be made, in order to avoid disaster !

Please could someone assis ?!

Thank you & Kind regards,
Walco Johnson

[arlu1201]

Walco,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.