Formula Updating Changes to Manual

[babuthomas23]

Sir,

For the below code, If i open the source file from i extract data while the macro run excel is open the formula updating changes from automatic to manual. After that every time I open the source excel it is in manual mode.

But if I close the macro run excel and open the source files its formula calculation is automatic.

I need the formula to be updated when both the files (source and destination are open)

Code is as below

Regards
Prof. Babu Thomas

Does this do what you want?

Code: Select all

Sub MergeAllWorkbooks()
    Dim MyPath As String, FilesInPath As String
    Dim MyFiles() As String
    Dim FNum As Long
    Dim myBook As Workbook, BaseWks As Worksheet
    Dim wks As Worksheet
    Dim rNum As Long, CalcMode As Long

    ' Change this to the path\folder location of your files.
    MyPath = "D:\test"

    ' Add a slash at the end of the path if needed.
    If Right(MyPath, 1) <> "\" Then
        MyPath = MyPath & "\"
    End If

    ' If there are no Excel files in the folder, exit.
    FilesInPath = Dir(MyPath & "*.xl*")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If

    ' Fill the myFiles array with the list of Excel files
    ' in the search folder.
    FNum = 0
    Do While FilesInPath <> ""
        FNum = FNum + 1
        ReDim Preserve MyFiles(1 To FNum)
        MyFiles(FNum) = FilesInPath
        FilesInPath = Dir()
    Loop

    ' Set various application properties.
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    ' Add a new workbook with one sheet.
    Set BaseWks = ActiveSheet
    'Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    rNum = 2

    ' Loop through all files in the myFiles array.
    If FNum > 0 Then
        For FNum = LBound(MyFiles) To UBound(MyFiles)
            Set myBook = Nothing
            On Error Resume Next
            Set myBook = Workbooks.Open(MyPath & MyFiles(FNum))
            On Error GoTo 0

            If Not myBook Is Nothing Then
                Set wks = myBook.Worksheets(1)
                BaseWks.Range("A" & rNum).Resize(6).Value = MyFiles(FNum)
                BaseWks.Range("B" & rNum).Value = wks.Range("D7").Value
                BaseWks.Range("C" & rNum).Value = wks.Range("D6").Value
                BaseWks.Range("D" & rNum).Value = wks.Range("K8").Value
                BaseWks.Range("E" & rNum).Value = wks.Range("K9").Value
                BaseWks.Range("F" & rNum).Value = wks.Range("N9").Value
                BaseWks.Range("G" & rNum).Resize(6).Value = wks.Range("C8").Resize(6).Value
                BaseWks.Range("H" & rNum).Resize(6).Value = wks.Range("L84").Resize(6).Value
                myBook.Close SaveChanges:=False
                rNum = rNum + 6
            End If

        Next FNum
        BaseWks.Columns.AutoFit
    End If

ExitTheSub:
    ' Restore the application properties.
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With
End Sub

[HansV]

Does it work better if you change the line

Code: Select all

            On Error GoTo 0

to

Code: Select all

            On Error GoTo ExitTheSub

[Rudi]

Hello from me....

Han's has provided something to check in order to resolve your query, but let me just add a learning point to take note of regarding Calculation mode in general.

See this Microsoft article that describes how Calculation more is applied to the application as different workbooks are opened and closed.

How to control the mode of calculation

All open documents use the same mode of calculation. You must follow special procedures to work with documents that use different calculation modes. For example, if you are working with Auto1.xlsx and you want to open Manual1.xlsx in manual calculation mode, take one of the following actions:

• Set Auto1.xlsx to manual calculation mode before you open Manual1.xlsx.
• Close Auto1.xlsx (and any other open documents) before you open Manual1.xlsx.

[babuthomas23]

Sir

It didnt work.

Regards
Prof. Babu Thomas

Does it work better if you change the line

Code: Select all

            On Error GoTo 0

to

Code: Select all

            On Error GoTo ExitTheSub

[HansV]

Try changing the line

Code: Select all

        .Calculation = CalcMode

near the end of the macro to

Code: Select all

        .Calculation = True

[babuthomas23]

Yes it worked. Thanks

I changed the previous mentioned change to the original one also. On Error GoTo 0 Thanks

But the excel run macro changed its status to manual. Can we have that one also set as automatic. So if i run it again it says Overflow

[HansV]

Sorry, I don't understand. Can you explain more clearly?

[babuthomas23]

Sir

Firstly my issue was that the source files from where I extracted data has its formula updation status changing to Manual when I run Attainment.xlsm (VBA code).

After the change in calculation = true, the file from where I run the macro (ie Attainement.xlsm) has its formula updation changing to Manual, while the source file status is automatic . So if I run the macro run again, it will not populate the values and shows as error Overflow.

Regards
Prof. Babu Thomas

[HansV]

Sorry, I cannot explain why that happens.

[babuthomas23]

ok Thanks