-2

I wanna rename only two files from a folder using macro, Although there are lots files in that folder, but unable to do it. I wanna rename file name from "N2" to "NORTH 2 (UP/UK)" and "N3" to "NORTH 3 (HR/PB)". Also, I need to rename all values in first column named "zone", "from "N2" to "NORTH 2 (UP/UK)" and "N3" to "NORTH 3 (HR/PB)" using dynamic range of first column in each file.

I wish to just click on a button, and this task want to automate. Compiler automatically renames files, open each file one by one and replace existing zone values as per above condition and save the changes.

Question: How to assign dynamic range to below code .Worksheets(1).Cells(2, 1)

Please see following code:

Getting run-time error 53 while trying to rename file

 Option Explicit

Sub FileOpen_Macro()
    Dim FileName(0 To 1) As String
    Dim ReplaceName(0 To 1) As String
    Dim ReplaceZoneName(0 To 1) As String
    
    'Dim FileName As String
    
    
    Const MyPath As String = "D:\iWork\Dunning Report\Dec'21\Result\"
    Dim strNewName As String
    Dim i As Long
    
    FileName(0) = "N2"
    FileName(1) = "N3"
    
    ReplaceName(0) = "North-2(UPUK).xlsx"
    ReplaceName(1) = "North-3(HRPB).xlsx"
    
    ReplaceZoneName(0) = "NORTH 2 (UP/UK)"
    ReplaceZoneName(1) = "NORTH 3 (HR/PB)"
    
    For i = 0 To 1
       
       'strNewName = Replace(FileName(i), "N", ReplaceName(i))
       strNewName = Replace(FileName(i) & ".xlsx", FileName(i) & ".xlsx", ReplaceName(i))
       Name MyPath & FileName(i) & ".xlsx" As MyPath & ReplaceName(i)
      With Workbooks.Open(FileName:=MyPath & strNewName)
          ' Replace the cell A1 of the first sheet.
          .Worksheets(1).Cells(2, 1) = Replace(.Worksheets(1).Cells(2, 1), FileName(i), ReplaceZoneName(i))
          'and Save & close
          .Close SaveChanges:=True
       End With
       MsgBox strNewName
       
    Next i


End Sub
Ravindra Bisht
  • 65
  • 1
  • 8
  • What is the specific problem you're having with your code? FYI if you want to rename a file you can use `Name` - see https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/name-statement – Tim Williams Dec 25 '21 at 06:27
  • Getting run-time error 53 – Ravindra Bisht Dec 25 '21 at 07:22
  • 1
    Is `/` valid in a file Name? https://stackoverflow.com/questions/1976007/what-characters-are-forbidden-in-windows-and-linux-directory-names – Tim Williams Dec 25 '21 at 07:30
  • As far as I concern by your shared post that it is not feasible to rename file as per the value specified in my code. But, dear I am having to stuck only on this... Because our IT team specified this name in their database and my seniors are not ready to change it. Thus, in-order to routine activity easier, I am seeking help of Macro. Although, this just a minor part of my project. I have already made my project automated by maximum extent. Please see if it is possible to achieve this as well , else I will have to do with manual approach only. Merry Christmas! – Ravindra Bisht Dec 25 '21 at 07:41
  • _I will have to do with manual approach only._ Even manually you cannot use a `/ ` in your filename – Storax Dec 25 '21 at 08:17
  • How to assign dynamic range in . worksheet(1).cells(2,1), I want to assign row number dynamically following loop.... – Ravindra Bisht Dec 25 '21 at 13:49
  • FYI your IT group should be aware of restrictions on characters in file names... – Tim Williams Dec 25 '21 at 21:24
  • Yes, Tim you are correct. – Ravindra Bisht Dec 27 '21 at 12:05

1 Answers1

1

Use Range.Replace to update all cells in a range.

Sub Replace_Macro()

    Const MyPath As String = "D:\iWork\Dunning Report\Dec'21\Result\"
    Dim sOld as string, sNew as string, sZone As String
    Dim lastrow As Long, i As Long, ar(1)
    
    ar(0) = Array("N2", "North-2(UPUK)", "NORTH 2 (UP/UK)")
    ar(1) = Array("N3", "North-3(HRPB)", "NORTH 3 (HR/PB)")
    
    For i = 0 To UBound(ar)
    
        sOld = ar(i)(0) & ".xlsx"
        sNew = ar(i)(1) & ".xlsx"
        sZone = ar(i)(2)
    
        ' check file exists
        If Dir(MyPath & sOld) <> "" Then
            With Workbooks.Open(MyPath & sOld)
                With .Sheets(1)
                    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
                    ' update column A
                    .Range("A2:A" & lastrow).Replace _
                      What:=ar(i)(0), Replacement:=sZone, _
                      LookAt:=xlWhole, SearchOrder:=xlByColumns, _
                      MatchCase:=True
                End With
                ' save as new name
               .SaveAs FileName:=MyPath & sNew
               .Close False
            End With
             ' delete old file
            If Dir(MyPath & sNew) <> "" Then
                Kill MyPath & sOld
            End If
        Else
            MsgBox MyPath & sOld & " does not exist", vbCritical
        End If
    Next i
    MsgBox "Done"
End Sub
CDP1802
  • 13,871
  • 2
  • 7
  • 17