Results 1 to 4 of 4
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Change File Name (VBA/Excel/XP)

    Is it possible to change a file name within Excel VBA? I cannot find the syntax.

    In my procedure, I create a TEMP text file to hold the results of an extract. At the end, I want to change the name of the file from ??.tmp to ??.txt. I cannot use the final name; because that is the name of the file I am originally pulling from.

    Is this a call to the Windows API?
    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change File Name (VBA/Excel/XP)

    No need to use API functions. VBA has a Name instruction:

    Name oldpathname As newpathname

    It can be used to rename files, but also to move files (by specifiying a different path in newpathname than in oldpathname). You can't use wildcards in the arguments. Examples:

    Rename only:
    Name "C:ExcelTest.tmp" As "Test.txt"

    Rename and move to another folder:
    Name "C:ExcelTest.tmp" As "C:OtherTest.txt"

  3. #3
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Change File Name (VBA/Excel/XP)

    One minor change that I found that I needed. If the file already existed, I have to get rid of it:
    <pre> If fS.fileexists("C:OtherTest.bak") Then
    Kill "C:OtherTest.bak"
    End If</pre>

    Alan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change File Name (VBA/Excel/XP)

    You didn't mention that you're using a FileSystemObject. It has methods MoveFile and DeleteFile that accomplish the same as Name and Kill.

Posting Permissions

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