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:
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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Excel keeps track of a range of stats about each workbook you use. If you want to take a look at those stats, it's easy; ...
Discover MoreDo you work with a group of workbooks all the time in Excel? Windows and Excel both provide a plethora of ways you can ...
Discover MoreWouldn't it be nice to have a single command that would save each of your open workbooks, all at once? It's easy to do ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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!
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments