Results 1 to 3 of 3

Thread: File Extensions

  1. #1
    ralph_davies
    Guest

    File Extensions

    Does anyone know a simple way to rename an open Workbooks member and strip the file extension off using VBA.
    This would be useful if you want to open an existing file with data so that it looks like a file that has been opened using a template ie File New.
    I have a macro which references the file opened via File New and I want to use the same macro with a file that has already been saved as FFF.xls FFF being the filename.
    I need to rename this file to YYY1 with no extension so the Macro can reference Workbooks("YYY1") as it would if the file is created by opening File New YYY.xlt on the first instance.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Extensions

    As far as I know, there is no way to rename an open file. The only way I can think of to do this would be to do a File SaveAs to the new name, and then delete to original file.
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Extensions

    In Excel 2000 there exists a way to rename a file when it is closed (I don't know if this is the case for Excel 97). When it is open, the only way I know is the one proposed by legare. To rename a closed file, use the Name ... As ... command.

    Dim OldName, NewName
    OldName = "OLDFILE": NewName = "NEWFILE" ' Define file names.
    Name OldName As NewName ' Rename file.

    OldName = "C:MYDIROLDFILE": NewName = "C:YOURDIRNEWFILE"
    Name OldName As NewName ' Move and rename file.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •