Rename File Massal Super Cepat Menggunakan Powershell

Salah satu cara mengganti nama file / rename file dalam jumlah sangat besar dalam waktu yang super cepat adalah dengan menggunakan Powershell. Berikut langkah-langkah dan bahannya :

File excel dengan kolom / isi sebagai contoh berikut :

• Kolom A berisi Path folder file nya
• Kolom B berisi nama file awal
• Kolom C berisi nama file baru
• Simpan dengan nama misalnya : rename.xlsx
• Kemudian jalankan powershell : tekan keyboard tombol Windows + R lalu ketik powershell
• Copy paste script di bawah ini :

#==========================================================
# RENAME FILE BERDASARKAN EXCEL
#
# Kolom A = DocFolderPath
# Kolom B = DocFileName
# Kolom C = NewFileName
#
# Windows 11 + Office 2021
#==========================================================

Clear-Host

$excelFile = "E:\Trial Rename Bulk Powershell\rename.xlsx"

#==========================================================

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false

$workbook = $excel.Workbooks.Open($excelFile)
$sheet = $workbook.Worksheets.Item(1)

$row = 2
$berhasil = 0
$gagal = 0

Write-Host ""
Write-Host "==============================================="
Write-Host " MEMULAI RENAME FILE"
Write-Host "==============================================="
Write-Host ""

while($true)
{

    $folder = $sheet.Cells.Item($row,1).Value2
    $oldName = $sheet.Cells.Item($row,2).Value2
    $newName = $sheet.Cells.Item($row,3).Value2

    if([string]::IsNullOrWhiteSpace($folder))
    {
        break
    }

    $folder = $folder.ToString().Trim()
    $oldName = $oldName.ToString().Trim()
    $newName = $newName.ToString().Trim()

    $oldFile = Join-Path $folder $oldName

    Write-Host "-----------------------------------------------"
    Write-Host "Baris   : $row"
    Write-Host "Folder  : $folder"
    Write-Host "Lama    : $oldName"
    Write-Host "Baru    : $newName"
    Write-Host "Path    : $oldFile"

    if(Test-Path -LiteralPath $oldFile)
    {
        try
        {
            Rename-Item -LiteralPath $oldFile -NewName $newName -ErrorAction Stop

            Write-Host "STATUS  : BERHASIL" -ForegroundColor Green
            $berhasil++
        }
        catch
        {
            Write-Host "STATUS  : GAGAL" -ForegroundColor Red
            Write-Host $_.Exception.Message -ForegroundColor Yellow
            $gagal++
        }
    }
    else
    {
        Write-Host "STATUS  : FILE TIDAK DITEMUKAN" -ForegroundColor Red
        $gagal++
    }

    $row++
}

$workbook.Close($false)
$excel.Quit()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

[GC]::Collect()
[GC]::WaitForPendingFinalizers()

Write-Host ""
Write-Host "==============================================="
Write-Host "SELESAI"
Write-Host "==============================================="
Write-Host "Berhasil : $berhasil"
Write-Host "Gagal    : $gagal"
Write-Host ""

pause

Tunggu sampai proses rename selesai. Cek nama file hasil rename.

Menurut pengalaman penulis berhasil me-rename sebanyak 18.000 file dalam waktu 9 menit.

Rata-rata 33 file per detik !!!

Mail Merge Word ke Banyak File PDF Menggunakan Macro (2)

 Sub MailMergeToPdfBasic()

    Dim masterDoc As Document
    Dim singleDoc As Document
    Dim firstRow As Long
    Dim lastRow As Long
    Dim currentRow As Long
    Dim maxRecord As Long

    Set masterDoc = ActiveDocument

    'Cari jumlah record terakhir
    masterDoc.MailMerge.DataSource.ActiveRecord = wdLastRecord
    maxRecord = masterDoc.MailMerge.DataSource.ActiveRecord

    'Input Record Awal
    firstRow = CLng(InputBox("Masukkan Record Awal", "Mail Merge", 1))

    'Input Record Akhir
    lastRow = CLng(InputBox("Masukkan Record Akhir", "Mail Merge", maxRecord))

    'Validasi
    If firstRow < 1 Then firstRow = 1
    If lastRow > maxRecord Then lastRow = maxRecord

    If firstRow > lastRow Then
        MsgBox "Record awal tidak boleh lebih besar dari record akhir.", vbCritical
        Exit Sub
    End If

    currentRow = firstRow

    Do While currentRow <= lastRow

        masterDoc.MailMerge.DataSource.ActiveRecord = currentRow

        masterDoc.MailMerge.Destination = wdSendToNewDocument
        masterDoc.MailMerge.DataSource.FirstRecord = currentRow
        masterDoc.MailMerge.DataSource.LastRecord = currentRow
        masterDoc.MailMerge.Execute False

        Set singleDoc = ActiveDocument

        singleDoc.SaveAs2 _
            FileName:=masterDoc.MailMerge.DataSource.DataFields("DocFolderPath").Value & _
            Application.PathSeparator & _
            masterDoc.MailMerge.DataSource.DataFields("DocFileName").Value & ".docx", _
            FileFormat:=wdFormatXMLDocument

        singleDoc.ExportAsFixedFormat _
            OutputFileName:=masterDoc.MailMerge.DataSource.DataFields("PdfFolderPath").Value & _
            Application.PathSeparator & _
            masterDoc.MailMerge.DataSource.DataFields("PdfFileName").Value & ".pdf", _
            ExportFormat:=wdExportFormatPDF

        singleDoc.Close False

        currentRow = currentRow + 1

    Loop

    MsgBox "Selesai memproses record " & firstRow & " sampai " & lastRow

End Sub