檔案編輯自動化

我只想使用宏重命名檔案夾中的兩個檔案，雖然該檔案夾中有很多檔案，但無法做到。我想將檔案名從“N2”重命名為“NORTH 2（UP/UK）”，將“N3”重命名為“NORTH 3（HR/PB）”。此外，我需要使用 first 的動態范圍將名為“zone”、“從“N2”到“NORTH 2 (UP/UK)”和“N3”的第一列中的所有值重命名為“NORTH 3 (HR/PB)”每個檔案中的列。

我只想點擊一個按鈕，這個任務想要自動化。編譯器自動重命名檔案，一個一個打開每個檔案，并根據上述條件替換現有區域值并保存更改。

問題：如何將動態范圍分配給以下代碼 .Worksheets(1).Cells(2, 1)

請看以下代碼：

嘗試重命名檔案時出現運行時錯誤 53

 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

uj5u.com熱心網友回復：

使用Range.Replace更新范圍內的所有單元格。

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

標籤：擅长 vba

上一篇：如果在比較Rng1和Rng2時沒有發現差異，如何避免顯示MsgBox？

下一篇：用于每小時交易量資料的BloombergExcelAPI