Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Making Changes in a Group of Workbooks.

Making Changes in a Group of Workbooks

by Allen Wyatt
(last updated July 25, 2020)

3

Over time, it is very easy to create and collect a huge number of Excel workbooks. Suppose that you had a whole bunch of workbooks in which you needed to make the same change. For instance, you might need to change the value stored in cell A10 of each of the worksheets in each of the workbooks.

If you had only a few workbooks to change, the task is pretty easy: Load each workbook and, in turn, make the change to each of them. If you have a couple hundred workbooks in which the change needs to be made, then the task becomes more formidable.

If you anticipate only needing to do this task once, then the easiest solution is to create a text file that contains the path and filename of each of the workbooks, one workbook per line. For instance, you might end up with a file that had entries such as this:

c:\myfiles\first workbook.xlsx
c:\myfiles\second workbook.xlsx
c:\myfiles\third workbook.xlsx

The file could have as many lines in it as necessary; it doesn't really matter. The important thing is that each line be a valid path and file name, and that there be no blank lines in the file.

You could most easily create such a file by displaying a command-prompt window, navigating to the directory containing the workbooks, and issuing the following command:

dir /b > myfilelist.txt

Each file in the directory ends up in the myfilelist.txt file. You will need to load the text file into a text editor and check it out so you can delete extraneous entries. (For instance, myfilelist.txt will end up in the listing.) You will also need to add the path name to the beginning of each line in the file.

Once the file is complete, you can start Excel and use a macro to read the text file, load each workbook listed in the text file, step through each worksheet in that workbook, make the appropriate change, and save the workbook. The following macro will perform these tasks nicely.

Sub ChangeFiles1()
    Dim sFilename As String
    Dim wks As Worksheet

    Open "c:\myfiles\myfilelist.txt" For Input As #1
    Do While Not EOF(1)
        Input #1, sFilename  ' Get workbook path and name
        Workbooks.Open sFilename

        With ActiveWorkbook
            For Each wks In .Worksheets
                ' Specify the change to make
                wks.Range("A1").Value = "A1 Changed"
            Next
        End With

        ActiveWorkbook.Close SaveChanges:=True
    Loop
    Close #1
End Sub

While this approach works great if you only have to process a single batch of workbook files, it can be made much more flexible if you anticipate needing to make such changes in the future. The biggest hassle, of course, is putting together the myfilelist.txt file each time you want to process a batch of files. Flexibility is added if the macro could simply use a directory and then load each workbook from that directory.

Sub ChangeFiles2()
    Dim MyPath As String
    Dim MyFile As String
    Dim dirName As String
    Dim wks As Worksheet

    ' Change directory path as desired
    dirName = "c:\myfiles\"

    MyPath = dirName & "*.xlsx"
    MyFile = Dir(MyPath)
    If MyFile > "" Then MyFile = dirName & MyFile

    Do While MyFile <> ""
        If Len(MyFile) = 0 Then Exit Do 

        Workbooks.Open MyFile

        With ActiveWorkbook
            For Each wks In .Worksheets
                ' Specify the change to make
                wks.Range("A1").Value = "A1 Changed"
            Next
        End With

        ActiveWorkbook.Close SaveChanges:=True

        MyFile = Dir
        If MyFile > "" Then MyFile = dirName & MyFile
    Loop
End Sub 

This macro uses whatever directory you specify for the dirName variable. Any workbook file (ending with the .Xlsx extension) is loaded and processed.

Another approach is to have the macro ask the user which directory should be processed. You can use the standard Excel File dialog box to do this, in the manner shown in the following macro.

Public Sub ChangeFiles3()
    Dim MyPath As String
    Dim MyFile As String
    Dim dirName As String

    With Application.FileDialog(msoFileDialogFolderPicker)
        ' Optional: set folder to start in
        .InitialFileName = "C:\Excel\"
        .Title = "Select the folder to process"
        If .Show = True Then
            dirName = .SelectedItems(1)
        End If
    End With

    MyPath = dirName & "\*.xlsx"
    myFile = Dir(MyPath)
    If MyFile > "" Then MyFile = dirName & MyFile

    Do While MyFile <> ""
        If Len(MyFile) = 0 Then Exit Do 

        Workbooks.Open MyFile

        With ActiveWorkbook
            For Each wks In .Worksheets
                ' Specify the change to make
                wks.Range("A1").Value = "A1 Changed"
            Next
        End With

        ActiveWorkbook.Close SaveChanges:=True

        MyFile = Dir
        If MyFile > "" Then MyFile = dirName & MyFile
    Loop
End Sub

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8939) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Making Changes in a Group of Workbooks.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Changing the Way Endnotes Are Numbered

Word is flexible on how it numbers your endnotes. This tip shows how easy it is to make the changes to the numbering system.

Discover More

Keeping the Flash Drive Occupied

Working on a document stored on a flash drive can have some unintended consequences. Here's some help in understanding ...

Discover More

Determining a Paragraph's Style in VBA

When processing a document via a macro, it is often helpful to understand what style has been applied to a paragraph. You ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Sharing Your Workbook

Need to allow others to contribute to your Excel workbook? It's easy to do if you just share it. This tip provides an ...

Discover More

Opening Two Workbooks at Once

Do you work with a group of workbooks all the time in Excel? Windows and Excel both provide a plethora of ways you can ...

Discover More

Merging Many Workbooks

If you need to combine the contents of a bunch of workbooks into a single workbook, the process can get tedious. Here's a ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is five more than 0?

2021-07-09 18:25:02

David A Czuba

Using ChangeFile3 as a base, if you wish to change the DocProperty of a batch of files, declare the following variable:
Dim DocProp As DocumentProperty

I substituted the following code after:
' Specify the change to make
For Each DocProp In ActiveWorkbook.BuiltinDocumentProperties
If DocProp.Name = "Company" Then
DocProp.Value = "Your Company Name Here"
End If
Next

...that's for Excel. For Word, change WorkBook to Document (and .xlsx to .docx, or better yet .doc* to capture templates and files with macros):

For Each DocProp In ActiveDocument.BuiltInDocumentProperties
If DocProp.Name = "Company" Then
DocProp.Value = "Your Company Name Here"
End If
Next

End With
ActiveDocument.Close SaveChanges:=True

This subroutine will open and close each document in the folder you choose and change the Company property. The update date will be reflected as today. Some observers suggest it isn't copasetic to change files wholesale like this for legal reasons, but when name changes, mergers and acquisitions happen as frequently as they do, marketing needs tools for brand maintenance.


2021-07-09 14:55:46

David A Czuba

This tip appears to be a good starting point for changing a Workbook property for all workbooks in a directory, and not only data in worksheet ranges. Commonly, a user would want to change the Company property to reflect the new legal name of a business entity or organization, or add a tag/category or a custom property the organization uses.


2020-10-29 13:38:29

Parolia

Hello,

I tried to use the last code you have provided on this page "https://excelribbon.tips.net/T008939_Making_Changes_in_a_Group_of_Workbooks.html". I kept getting the error of the \ missing and would say file not found. I resolved it (I think) by removing the backslash but then the code does seem to work. I think it just stops and not continue.

The change I would like to make in this code is as follows:
With ActiveWorkbook
For Each wks In .Worksheets
' Specify the change to make
wks.Range("A1").Value = "A1 Changed"
Next
End With

With ActiveWorkbook
For Each wks In .Worksheets
' Specify the change to make
x = 5
Do While Cells(x, 3) <> ""
Cells(x, 3) = Cells(x, 3) * 10
x = x + 1
Loop
wks.Range("C4").String = "Radius [um]"
Next
End With
Do you have any suggestions on this? Thanks in advance!


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.