Vba to rename file then move old file

[scoth]

Hi, need help on a VBA that Ian working on. What I want it to do is when a cell value changes save the file under a new name then move the old file to another folder. So far I have been able to do the file save portion but when I start adding the move portion it hangs up. Also I don't want the template file to be moved. Only files saved from the template going forward. Any help would be appreciated.

[HaHoBe]

Hi, scoth,

Only files saved from the template going forward.

Has the original file being saved as a template (*.xlt) or is it a normal workbook?

You could save the name of ActiveWorkbook.Name or ActiveWorkbook.FullName in a variable (here: BookName), use SaveAs for the getting the new file and then use

Please Login or Register  to view this content.

to move the workbook (at least in the same drive).

HTH,
Holger

[AlphaFrog]

Can you post your code? That would provide the details that your description does not e.g. path, file naming conventions. Include comments and pseudo-code where you are having difficulties.

Take note to surround the code with code tags (see my signature block below).

[scoth]

Template file is a regular Excel file. Idea is that associate will use template to start a part number specific file. Once create the part number specific file will be used, when ever the file Revision is changed (cell E7) a new file is to be saved with a file with the new revision at end of file name, then move the old part number file to an obsolete folder. Code has provisions to create this obsolete folder if the obsolete folder does not exist. Below is the code I have so far, code worked fine until I added the portion to create obsolete folder and move old file. Don't laugh to hard, I am new at this VBA coding, , Thanks again for your help.

Please Login or Register  to view this content.

[AlphaFrog]

The code isn't that bad. Nice fix with the code tags.

Can I ask why you prompt the user for a file name? Isn't the file name determined by cell values? Are you meaning to prompt the user for a destination folder?

What is the file name of the template file before it is saved as something else?

Can you give some examples of the file names and the naming convention you are using? What is in cell E7; numbers, text?

[HaHoBe]

Hi, scoth,

everythin gyou have so far letīs the workbook opened from the basic folder back in place as you canīt move an open workbook with Name (AFAIK only copy it) which would lead you to a twistthe opened workbook is still active in the folder from which is was opened.

I had to disable process (whatever that may be) in order to test the code:

Please Login or Register  to view this content.

This code will check if the name of the given directory is part of the FullName of the workbook and should act accordingly.

Ciao,
Holger

[scoth]

The prompt is so they can move the file to its correct location, the template file is stored in one location where as the part number files are stored by part number/operation number. The code auto populates the name in the save as popup, they just need to verify the location is correct.

There is 2 templates, depending on machine type (this is aerospace manufacturing documents) template name is _lathe_rev_tool_set-up shts_7-2-2013, the other is the same except first name is Mill

Saved files for part number file would be part number_op number_program rev. program rev populated by cell E7 and is text.

To you see anything that stands out as to why it hangs up, also any cell change triggers it, not just when E7 is changed.

[scoth]

Process was an attempt to run a sub process to get the file move to work, thought I had removed before posting.

[scoth]

Holger,
Thanks for the code, I tried and found it to be close to what I was looking for. What it is doing is saving the new file in the obsolete folder and the reqular folder. What is intended would be to have the old file move to the obsolete and only have the new file in the reqular folder.

[scoth]

Got it to work. Thanks to all who offered their advise