none
Rename File Object 438 Error Message RRS feed

  • Question

  • Please help me understand why this code isn't working the way it's intended to work.

    I'm receiving error 438 on the BOLD, ITALIC, UNDERLINE code, and I don't understand why.  I found this code doing a google search for renaming files in VBA.

    Any help is appreciated.

    Thanks.

    JMData Consultant

    Sub RenameCopiedCloseFiles()
        Dim objFSO As Object
        Dim objFolder As Object
        Dim OBJFILE As Object
        
        Dim MONTHENDDATE As Date
        Dim PrevMonth As Date
        Dim NEXTMONTH As Date
        
        Dim STRINGNAME As String
        
        Dim oldname, newname As String
                        
        Dim Today As Date
        Today = Date
        PrevMonth = WorksheetFunction.EoMonth((Today), -1)
        NEXTMONTH = WorksheetFunction.EoMonth((Today), 0)
        
    'MsgBox Format(Today, "MM/DD/YYYY")
      '  Dim topath As String
      '  topath = "W:\test\October 2013"
        Dim ParentDirectory As String ' 1st level directory
        Dim ChildDirectory As String ' 2nd level directory
        Dim MonthlyDirectory As String ' 3rd Level Directory
     
        ParentDirectory = "W:\TEST MONTHLY CLOSE WORKPAPERS"
        ChildDirectory = ParentDirectory & "\FY" & Format(NEXTMONTH, "YYYY") & " Financial Close Workbook"
        MonthlyDirectory = ChildDirectory & "\FY" & Format(NEXTMONTH, "YYMM") & "_" & Format(NEXTMONTH, "MMM")
        
        MsgBox MonthlyDirectory, , "MONTHLY DIRECTORY IS:"
        
    
        MONTHENDDATE = WorksheetFunction.EoMonth((Date - 1), 0)
         
        Set objFSO = CreateObject("Scripting.FileSystemObject")
            
         'Get the folder object associated with the directory
        Set objFolder = objFSO.GetFolder(MonthlyDirectory)
        Enddate = Date
             
         'Loop through the Files collection
        For Each OBJFILE In objFolder.files
               
        ' Get New File Name for File *** WORKS BY ITSELF
            STRINGNAME = WorksheetFunction.Substitute(OBJFILE.Name, "FY" & Format(PrevMonth, "YYMM") & "_" & Format(PrevMonth, "MMM"), "FY" & Format(NEXTMONTH, "YYMM") & "_" & Format(NEXTMONTH, "MMM"))
            STRINGNAME = WorksheetFunction.Substitute(STRINGNAME, Format(PrevMonth, "MMMM"), Format(NEXTMONTH, "MMMM"))
            STRINGNAME = WorksheetFunction.Substitute(STRINGNAME, Format(PrevMonth, "MMM"), Format(NEXTMONTH, "MMM"))
            'MsgBox OBJFILE.Name & " is now " & STRINGNAME
           
        ' RENAME
            With OBJFILE.Name
                Name OBJFILE.Name As OBJFILE.STRINGNAME
            End With
        Next
    
    End Sub
    

    Tuesday, October 29, 2013 3:21 PM

Answers

  • Hi,

    I run your code to reproduce your issue, and I find you may make a mistake in the code “Name OBJFILE.Name As OBJFILE.STRINGNAME”.  The string "STRINGNAME" is not a property of the object "OBJFILE".

    I edit the code below and it works well.

    With OBJFILE.Name
         Name MonthlyDirectory & "\" & OBJFILE.Name As MonthlyDirectory & "\" & STRINGNAME
    End With



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 30, 2013 12:06 PM
    Moderator

All replies

  • Hi,

    I run your code to reproduce your issue, and I find you may make a mistake in the code “Name OBJFILE.Name As OBJFILE.STRINGNAME”.  The string "STRINGNAME" is not a property of the object "OBJFILE".

    I edit the code below and it works well.

    With OBJFILE.Name
         Name MonthlyDirectory & "\" & OBJFILE.Name As MonthlyDirectory & "\" & STRINGNAME
    End With



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 30, 2013 12:06 PM
    Moderator
  • Luna,

    THANK YOU SO MUCH. I really appreciate your help.

    JMData Consultant

    Wednesday, October 30, 2013 1:14 PM