CODEWITHSUNDEEP
excel
Kevin Anthony Oppegaard Rose
Kevin Anthony Oppegaard Rose

Reputation: 1003

Bulk updating the same cell(s) in multiple excel files

I have 200 excel sheets that all need the same 5 cells updated from their current content to new content. All will have the same new content.

I am wondering if there is anyway to do this in bulk instead of opening each individual document and doing the updates one by one.

Example:

Document 1, cell B/C/D7 (merged cell) contains a date in DD.MM.YYYY format. I need that date in YYYY.MM.DD format in all 200 documents.

Can this be done using a macro or something?

Upvotes: 1

Views: 7392

Answers (1)

Romain
Romain

Reputation: 1302

The easiest way would indeed be to do it with VBA. You can put all your files in a single directory, and then use as this to cycle through all of them, applying your changes.

For the case you are mentioning, supposing you need to make the change on the worksheet Sheet1 of each workbook you would need to run wbResults.Worksheets("Sheet1").Range("B7").NumberFormat = "YYYY.MM.DD"

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
    Set wbCodeBook = ThisWorkbook
        With Application.FileSearch
            .NewSearch
            'Change path to suit
            .LookIn = "C:\MyDocuments\TestResults"
            .FileType = msoFileTypeExcelWorkbooks
            'Optional filter with wildcard
            '.Filename = "Book*.xls"
                If .Execute > 0 Then 'Workbooks in folder
                    For lCount = 1 To .FoundFiles.Count 'Loop through all
                        'Open Workbook x and Set a Workbook variable to it
                        Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

                        'DO YOUR CODE HERE
                         wbResults.Worksheets("Sheet1").Range("B7").NumberFormat = "YYYY.MM.DD"
                        wbResults.Close SaveChanges:=True
                    Next lCount
                End If
        End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
MsoFileType can be one

Upvotes: 3

Related Questions