changin file name/location or deleting file

[I heart Excel]

Hello,

I have created a Sickness Form that is filled out by our office PA. She partially fills out the form and clicks on the 'Saves As' button, this saves the form to another location and changes the name to the staff members name (located in a specific cell).

She then continuously accesses the form and once completed clicks on the 'Submit' button, which prints the form to our internal image storage system.

There is a couple of things I am having problems trying to acheive, which I want to run behind the scenes when the Submit button is selected...

• I need the file name to change, just before the print, back to Sickness Form, this is to ensure the script works for the printing to our internal system.
• Once Printed the form then needs to either move location (into a file for me to delete at a later date) or delete!

Many thanks in advance

 

[wigi]

From reading your post, I think that the command "Name ... As ..." does what you need. Can you check it out in the documentation please?

 

[jasmith4]

The "Submit" button should begin by doing what "SaveAs" does, except it should save the file as "Sickness Form" rather than the staff member's name.

Alternately, the "SaveAs" button should do two saves, once as the staff member's name, and again as "Sickness Form".

 

[I heart Excel]

I have spoken to the guys who deal with the scripts and they have told me that they can amend their system to read the script no matter what file name!!

So this is sorted... HOWEVER!! I really want the file to Submit and then delete! Is there anyway I can Kill Active.Workbook on Close?

I have seen that I can run Kill on the file once it is closed, but two things which this don't seem to work for me -

1. The file name varies everytime depending on what it has 'saved as' originally.
2. Where/How do I go about getting the Kill function to work on the file once it has closed?

Thanks

 

[wigi]

Does the code reside in another file? Because when the file to be deleted, is closed, VBA-code will not run anymore.