"Save as" when file is closed

[kateridleycontini]

Dear all,

I would like to kindly ask your support for the creation of a script (in excel 2010), activated when the excel file is closed, that will prompt to the user the “Save As” pop up window pointing already to a pre-defined directory.

Thanks,
Kate

[tfurnivall]

It depends on whether you want to give the user the option of saving it somewhere other thanthe pre-defined directory.

If this latitude is OK, then the Application.GetFileDialog routines will be useful, if not, then you want to get a filename from somewhere, and just stash the file away in the directory of your choice. In this case, using Workbook.Close might be the way to go. (However, read the help for this routine very carefully - there are some possible ambiguities if you are sharing the use of an existing file, IOW the file is not being created New).

Could you clarify this for us, and then we'll see better how to help!

Tony

[kateridleycontini]

Hello,

I have an excel file that should be opened, changed by the users, and then stored (every time with a different name) into a share point folder. In order to achieve this I was thinking about a script that, as soon as the user will close the excel file, it will open the windows “Save as” dialog box pointing directly to the mentioned sharepoint folder (I’m looking for this direct pointing to avoid that the user will store the modified file into another folder).

Hope I’ve been more clear this time

Thanks,
Kate

[tfurnivall]

Much better (he said, in a somewhat professorial manner)!

The reason, seriously, is that you've just said that the file will always be an existing file, which brings up
the question of the shared access.
I'd try something along the lines of:

Please Login or Register  to view this content.

So now the only real problem is preventing a perverse user from changing the location (ie the Sharepoint folder). I'd suggest using an InputBox, asking for simply the filename that the user wants to use. (I'd also add a date and time component to prevent duplicates!).

If you can handle that, then we're done! If not, let me know, and we'll continue,

Tony