Name command

The Jumping Off Point

In this week's WordTips (Menu Interface) from Allen Wyatt, he provided a tip on renaming files using the Name command.  Looking further, the same tip applies to Word 207/2010/2013, Excel 97-2003, and Excel 2007/2010/2013.

            Name <oldname> As <NewName>

First Thoughts, Errors, and Lessons Relearned

This looked like a winner, to me.  I knew it took me at least two lines of code to do this, so I tried it in a macro

            Workbooks.Add

            Name ActiveWorkbook.Name As <newworkbookname>

Ooops...up popped  Run time Error '75'

After a bit of thinking (as well as unthinking trial-and-error), I remembered the old code included the Kill command, so I searched for "Kill" in a project where I new I had used it.

            FileCopy <olddir\oldfilename>, <newdir\newfilename>

            Kill <olddir\oldfilename>

So, if I could figure out the run time error I could eliminate one line of code.  Not a huge savings, but one less thing to remember.

Research

Looking at the help on the run-time error 75, I found that it referred to the Open, MkDir, ChDir, or RmDir commands.  Knowing that I wasn't using any of these DOS-old commands, I surmised that Name was related in that it could not perform its actions for the same reason on or more of these might generate the same error.

Solution

RMDir was the key!  You cannot remove a directory that is in use, e.g. there is an open file in it.

Looking further into the old project's code I realized the files were closed before executing the FileCopy and Kill commands.  

Now it made sense run-time error was caused because the files I was trying to reName were open, and the file management system rejected the attempts.

Application

If the need is to change the name of a currently open file, then include the following

            <file>.SaveAs  FileName:=<newdir\newfilename>

            Kill <olddir\oldfilename>

My testing shows this works in Excel & Word 2010, and I expect it to work in versions from 93 to 2013.