Automatic in calculation options

[Bomba]

Hi,
I have 12 folders with 31 workbooks in each folder. I noticed that the calculation option in these 12 folders is set to manual. I need to set it to automatic. Is there a way at least to change folder by folder to automatic at once instead of doing them workbook by workbook?

Thanks in advance

[HansV]

Are those 12 folders all subfolders of a single folder (and the only ones), or are they spread around?

[Bomba]

Yes, all 12 folders are subfolders of a single folder.

[HansV]

Here is a macro. Before running it, change the path in the constant strParent to the path of the folder that contains the 12 folders.
Running it will take some time, since it has to open, save and close some 365 workbooks.

Code: Select all

Sub Set2Automatic()
    ' Change this to the path of the folder that contains the 12 subfolders
    Const strParent = "C:\MyFolder"
    Dim fld As Object
    Dim sfl As Object
    Dim strPath As String
    Dim strFile As String
    Dim wbk As Workbook
    Application.ScreenUpdating = False
    Set fld = CreateObject(Class:="Scripting.FileSystemObject").GetFolder(strParent)
    For Each sfl In fld.SubFolders
        strPath = sfl.Path & "\"
        strFile = Dir(strPath & "*.xls*")
        Do While strFile <> ""
            Set wbk = Workbooks.Open(Filename:=strPath & strFile)
            Application.Calculation = xlCalculationAutomatic
            wbk.Close SaveChanges:=True
            strFile = Dir
        Loop
    Next sfl
    Application.ScreenUpdating = True
End Sub

[Bomba]

Hi,
Thanks a lot, Master.