Senin, 16 Januari 2023

Cara Edit Banyak File Excel dalam 1 Folder Menggunakan VBA Macro

 Sub RunOnAllFilesInFolder()  
   Dim folderName As String, eApp As Excel.Application, fileName As String  
   Dim wb As Workbook, ws As Worksheet, currWs As Worksheet, currWb As Workbook  
   Dim fDialog As Object: Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)  
   Set currWb = ActiveWorkbook: Set currWs = ActiveSheet  
   'Select folder in which all files are stored  
   fDialog.Title = "Select a folder"  
   fDialog.InitialFileName = currWb.Path  
   If fDialog.Show = -1 Then  
    folderName = fDialog.SelectedItems(1)  
   End If  
   'Create a separate Excel process that is invisibile  
   Set eApp = New Excel.Application: eApp.Visible = False  
   'Search for all files in folder [replace *.* with your pattern e.g. *.xlsx]  
   fileName = Dir(folderName & "\*.*")  
   Do While fileName <> ""  
     'Update status bar to indicate progress  
     Application.StatusBar = "Processing " & folderName & "\" & fileName  
     Set wb = Workbooks.Open(folderName & "\" & fileName)  
     '...  
     'YOUR CODE HERE  
     wb.Worksheets("NamaSheet").Activate  
     Range("A1:AA1000").Replace What:="Mei 2020", Replacement:="Desember 2022"  
     Range("A1:AA1000").Replace What:="Ronaldo", Replacement:="Messi"  
     Range("A1:AA1000").Replace What:="Portugal", Replacement:="Argentina"  
     '...  
     wb.Close SaveChanges:=True 'Close opened worbook saving, change as needed  
     Debug.Print "Processed " & folderName & "\" & fileName  
     fileName = Dir()  
   Loop  
   eApp.Quit  
   Set eApp = Nothing  
   'Clear statusbar and notify of macro completion  
   Application.StatusBar = ""  
   MsgBox "Completed executing macro on all workbooks"  
 End Sub  

Tidak ada komentar:

Posting Komentar