Shoutbox

Hi,

I have to work with a shared excel file and I need to update it from time to time. I was wondering if it was possible to add a simple macro code that would allow me to simply save and close the file that is open on another computer on the network.

thx

Sounds like Google Docs could be of use?

http://www.logmein.com does that I believe.

Otherwise there's some related stuff on Google about it.

If it's another computer on the same network then just using Remote Desktop should be enough, as long as you can attach to the existing session and don't have to re-log into it (as obviously the document wouldn't be open!)

Or just VNC to it (which definitely attaches to the existing session) and then you can control it that way.

If it is a shared excel document it should allow for multiple people to edit the document simultaneously though. Are you sure you enabled the sharing properly or do you mean it is just shared on a network drive?

Thx for the replies.

The file is on a shared network drive. So if anyone opens it others will only get the file to open as read only.
I already have put in a macro that saves and closes the file when no modification has been done for a period of 30 min.
But I have to do a quick update once in while so I was thinkin maybe to link the excel file with a button on another workbook that when pressed it just closes the file.

Remote desktop is kind of difficult to do here and sharing a workbook through excel limits the options of editing.

So for the button idea maybe: can a macro check another file on the network so when something changes the save/close macro in the original workbook gets enabled?

Why not use the Share Workbook feature which will allow multiple people to edit the document silmultaneously? When 2 people are editing the document simultaneously here is what happens

Person A Opens the Document
Person A Makes some Changes
Person B Opens the Document
Person A Saves Changes
Person B makes somce Changes
Person B saves changes
Person B's changes will be merged with Person A's changes

quote:

---

Originally posted by matty
Why not use the Share Workbook feature which will allow multiple people to edit the document silmultaneously? When 2 people are editing the document simultaneously here is what happens

---

Doing that limits the options of editing that workbook like adding charts and inserting images,creating new data tables.

I'm not quite sure what you want though.

I use such shared docs all the time at work (everything is on a network drive there) and I don't have any problems with opening/closing/saving/etc such 'shared' docs - we do it all the time.

Whenever user A opens the Excel file, he can edit it and save it as much as he likes. And indeed, user B will only be able to open it as read-only when the doc is already opened by person A.

(if you want person B to also edit the file at the same time you need to do what matty suggested - no need for Google Docs at all. In fact Google Docs can not do what isn't already possible in Excel nativly or Office Live itself)

I don't see any use for such a 'close and save when not edited for 30mins'-macro either. In fact, it would cause you more troubles in certain scenarios.

So, can you explain more about what the problem is and what you exactly want?
Do you want the user, who has the doc open, be informed if you want to change something? Or do you want the apply the change while the doc is opened by another user? If so, you are bound to bump against syncronization problems for sure!

The only safe solution would then be to do as Matty suggested. But if you don't want to loose functionality then you are only left with a system where the user is simply informed (and nothing more!) of a pending change and it should then be up to this user to close the workbook by himself when he is done. Never ever close the workbook automatically!!!!! This is a big no-no as it will cause a lot of inconsistant data if you do that. eg: the user might be in the middle of editing and when you force-close it the data might not be edited properly yet. Then you put more data in it and you both will be left with a mangled inconsistant document in which you don't know what data is edited/correct and what data is not.

So, only after the user has done the editing and closed the workbook, you can (and must) open this edited workbook to apply your chances. This is the only (!) safe way to do what you want (from what I understand) and to keep data integrity.

So, I very strongly suggest to remove that 'close and save when not edited for 30mins'-macro and replace it with a macro which checks periodically for a notification file on the network drive. This simple notification file is the one you put there to inform the user a change is pending. It can be a simple text file containing the text you want to be shown to the user.