Seeking a solution to update multiple Excel workbooks in the same directory?

Question

Trying to automate updates to multiple Excel files.

(I might be running into incorrect file path calls)

My code:

Sub UpdateFiles()
    MyDir = Application.ThisWorkbook.Path
    DataDir = MyDir & "\folder1\"
    ChDir (DataDir)
    Nextfile = Dir("*.xlsx")
    While Nextfile <> ""
        Workbooks.Open (Nextfile)
        Workbooks(Nextfile).Sheets("sheet1").Range("F22") = "Major"
        Workbooks(Nextfile).Save
        Workbooks(Nextfile).Close
        Nextfile = Dir()
    Wend
End Sub

Would like data in cell F22, on sheet1, to read "Major"

Here re some answers I did recently [Open and Import File](https://stackoverflow.com/questions/53989467/how-to-open-multiple-workbooks-to-copy-the-data-from/53990323#53990323), [From Workbooks Sort](https://stackoverflow.com/questions/54019876/how-to-gather-data-from-all-rows-from-differenet-excel-workbooks-and-sort-them/54026784#54026784) to see the pattern. — VBasic2008, Jan 03 '19 at 21:20

VBasic2008 · Accepted Answer · 2019-01-03T21:42:56.587

* Instead of x

The pattern "*.xls*" enables the opening of both .xlsx and .xlsm files and additionally .xls files

The Code

Sub UpdateFiles()
    MyDir = Application.ThisWorkbook.Path
    DataDir = MyDir & "\folder1\"

Nextfile = Dir("*.xls*")

    While Nextfile <> ""
        With Workbooks.Open (Nextfile)
            .Sheets("sheet1").Range("F22") = "Major"
            .Save
            .Close
        End With
        Nextfile = Dir()
    Wend
End Sub

Seeking a solution to update multiple Excel workbooks in the same directory?

1 Answers1

* Instead of x

The Code