none
run time error 53 on NAME statement - trying in Excel VBA to rename files RRS feed

  • Question

  • Hi

    I have the macro below but try as I may the NAME statement fails with run time error 53 and file not found - all i want is to rename a buch of .csv files and add a date to the filename.

    Any help much appreciated and I am running on Windows 7 Enterprise plus Excel 2010

    Sub Renamethefiles()

    Dim RetVal As String
    Dim Chdir As String
    Dim Filename As String

    Chdir = "C:\Test\"

    RetVal = Dir("*.csv")

    Do While RetVal > ""
      Filename = Left(RetVal, Len(RetVal) - 4) & Date & ".csv" ' so filename now has what we want
      
      Name Chdir & RetVal As Chdir & Filename  
      RetVal = Dir()
    Loop

     
    End Sub


    Chris Ward

    Tuesday, January 8, 2013 5:38 PM

All replies

  • Ooops - overwrote my explanation - Date has illegal characters, so you need to format the date to not have illegal characters. Also, ChDir is a command, so you should not use that as a variable name. And you may not be getting the files from the folder you think unless you activate the folder prior to Dir (or be more specific in your dir command, like

    RetVal = Dir("C:\Test\*.csv")

    Try this:

    Sub Renamethefiles2()

    Dim RetVal As String
    Dim strDir As String
    Dim Filename As String

    ChDir "C:\Test"

    strDir = "C:\Test\"

    RetVal = Dir("*.csv")

    Do While RetVal > ""
      Filename = Left(RetVal, Len(RetVal) - 4) & Format(Date, " yyyy mm dd ") & ".csv"
      Name strDir & RetVal As strDir & Filename
      RetVal = Dir()
    Loop
    End Sub



    Tuesday, January 8, 2013 5:57 PM
  • Hi Chris

    Depending on the regional settings the Date() command will - converted to a string - generate a filename that contains characters that aren't acceptable in filenames like \ or /.
    You may use the Format() method to explicitly define how the date has to be formatted.
    In addition: Use meaningful naming conventions has it's reason. ChDir is a VBA function to change the current directory and shouldn't be used as variable name. If you prefix it by the abbrevation of the variable type, for example "str" you won't run into troubles here.

    Henry

    "Chris Waaaaaaaaaaaaa" schrieb im Newsbeitrag news:718cc802-efb9-40ad-92e1-54080dad0f7d@communitybridge.codeplex.com...

    I have the macro below but try as I may the NAME statement fails with
    run time error 53 and file not found - all i want is to rename a buch
    of .csv files and add a date to the filename.
    Dim Chdir As String

    --> reserved name, better use strChdir instead

    Dim Filename As String

    ..

    Filename = Left(RetVal, Len(RetVal) - 4) & Date & ".csv" ' so
    filename now has what we want

    --> I don't believe you or then what you want is not what your system accepts. / aren't acceptable in filenames and Date() converted to String may contain exactly this character. Use Format(Date(), "yyyymmdd") instead of Date only.

    Wednesday, January 9, 2013 1:13 AM