Workbooks Open after running code

[judgejustin]

I have been beating my head against the wall for days, I need to see if anyone has an idea. I am running a code that copies multiple workbooks into a single location. It loops through all workbooks that are in the same folder as the master sheet and copies the data and pasts it into the master sheet then closes each workbook individually. (This process happens one at a time, I can see it working and see the workbooks open and subsequently close.) When the process is complete I have a dialog box that tells the user it is finished. Everything works perfectly and it all processes correctly and the dialog box opens. However, the cursor still spins blue when the message box appears. As soon as clicking OK all of the workbooks open up, again. I feel pretty confident there is nothing triggering it in the code and everything is closed out/ended, it seems like there is something running in the background of excel causing it. I tried disabling the auto recovery and disabled keeping last autosaved version if closing without saving. I just can't seem to figure anything out so any recommendations or thoughts would be very helpful.

 

[6StringJazzer]

We have no hope of understanding your problem without seeing your code. Can you paste all of your code into a post? I highly recommend applying code tags by pasting the code, then selecting it, then clicking the VBA button in the edit controls.

I have never heard of anything in Excel that spontaneously opens files, except in the case where the Excel process crashes, automatically restarts, and tries to re-open any files that were open at the time of the crash. If Excel were crashing I would think you would notice. And if you disable autorecovery it probably won't do it.

I would consider stepping through your code with F8 starting just before the call to MsgBox to try to trace exactly what the code is doing and at what point these files are reopened.

 

[RoryA]

Do the workbooks in question have any Workbook_Open event code? Particularly anything that uses Application.OnTime.

 

[judgejustin]

Do the workbooks in question have any Workbook_Open event code? Particularly anything that uses Application.OnTime.

There are workbook open events in the workbooks that it opens to pull the data from.

We have no hope of understanding your problem without seeing your code. Can you paste all of your code into a post? I highly recommend applying code tags by pasting the code, then selecting it, then clicking the VBA button in the edit controls.

I have never heard of anything in Excel that spontaneously opens files, except in the case where the Excel process crashes, automatically restarts, and tries to re-open any files that were open at the time of the crash. If Excel were crashing I would think you would notice. And if you disable autorecovery it probably won't do it.

I would consider stepping through your code with F8 starting just before the call to MsgBox to try to trace exactly what the code is doing and at what point these files are reopened.

Below is the code, if I go through it using F8 everything behaves as expected and it ends without the workbooks opening again. That is what leads me to believe it is not the code itself.

Sub ConsolidateData()
    Dim FolderPath As String
    Dim Filename As String
    Dim MasterWB As Workbook
    Dim SourceWB As Workbook
    Dim MasterSheet As Worksheet
    Dim SourceSheet As Worksheet
    Dim lastRow As Long
    Dim SourceLastRow As Long
    Dim FirstFile As Boolean
    Dim Password As String
    Dim Identifier As String
    Dim i As Long
    Dim LastColumn As Integer
    Dim HeaderBGColor As Long

    FolderPath = ThisWorkbook.Path & "\"

    Password = "desotobocc"

    Debug.Print "Resolved Local Folder Path: " & FolderPath

    If Dir(FolderPath, vbDirectory) = "" Then
        MsgBox "Folder path does NOT exist: " & FolderPath, vbCritical
        Exit Sub
    End If

    Set MasterWB = ThisWorkbook
    Set MasterSheet = MasterWB.Sheets("MasterSheet")

    MasterSheet.Unprotect Password:="desotobocc"
    
    If Application.WorksheetFunction.CountA(MasterSheet.UsedRange) > 0 Then
        MasterSheet.Range("A3:Z" & MasterSheet.Rows.Count).ClearContents
    End If

    MasterSheet.Range("C1").Value = "Last Updated: " & Format(Date, "MM/DD/YYYY")
    With MasterSheet.Range("C1")
        .WrapText = True
        .Font.Name = "Cabri"
        .Font.Size = 12
        .Font.Bold = True
    End With

    Filename = Dir(FolderPath & "*.xlsm")

    FirstFile = True

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Do While Filename <> ""
     
        If Filename <> MasterWB.Name Then
            Debug.Print "Processing file: " & Filename

            On Error Resume Next
            Set SourceWB = Workbooks.Open(FolderPath & Filename, UpdateLinks:=False, ReadOnly:=True)
            If Err.Number <> 0 Then
                Debug.Print "Error opening file: " & Filename
                Err.Clear
                GoTo NextFile
            End If
            On Error GoTo 0

            On Error Resume Next
            Set SourceSheet = SourceWB.Sheets("Inventory Sheet")
            On Error GoTo 0
            If SourceSheet Is Nothing Then
                Debug.Print "Inventory Sheet not found in: " & Filename
                GoTo CloseWorkbook
            End If

            On Error Resume Next
            SourceSheet.Unprotect Password:=Password
            On Error GoTo 0

            LastColumn = SourceSheet.Cells(4, SourceSheet.Columns.Count).End(xlToLeft).Column

            Identifier = Trim(SourceSheet.Range("G2").MergeArea.Cells(1, 1).Value)

            SourceLastRow = SourceSheet.Cells(Rows.Count, 1).End(xlUp).Row

            lastRow = MasterSheet.Cells(Rows.Count, 2).End(xlUp).Row + 1

            If FirstFile Then
                SourceSheet.Range(SourceSheet.Cells(4, 1), SourceSheet.Cells(4, LastColumn)).Copy
                MasterSheet.Cells(2, 2).PasteSpecial Paste:=xlPasteValues
                Application.CutCopyMode = False

                For i = 1 To LastColumn
                    MasterSheet.Cells(2, i + 1).ColumnWidth = SourceSheet.Cells(4, i).ColumnWidth
                Next i

                HeaderBGColor = SourceSheet.Range("A4").Interior.Color

                With SourceSheet.Range(SourceSheet.Cells(4, 1), SourceSheet.Cells(4, LastColumn))
                    MasterSheet.Range("B2").Resize(, LastColumn).Font.Name = .Font.Name
                    MasterSheet.Range("B2").Resize(, LastColumn).Font.Size = .Font.Size
                    MasterSheet.Range("B2").Resize(, LastColumn).Font.Bold = .Font.Bold
                    MasterSheet.Range("B2").Resize(, LastColumn).Interior.Color = HeaderBGColor
                End With

                FirstFile = False 
            End If

            If lastRow < 3 Then lastRow = 3

            If SourceLastRow >= 5 Then
                MasterSheet.Range("A" & lastRow & ":A" & (lastRow + SourceLastRow - 5)).Value = Identifier
                Debug.Print "Writing " & Identifier & " to Column A, rows " & lastRow & " to " & (lastRow + SourceLastRow - 5)
            End If

            If SourceLastRow >= 5 Then
                SourceSheet.Range(SourceSheet.Cells(5, 1), SourceSheet.Cells(SourceLastRow, LastColumn)).Copy
                MasterSheet.Cells(lastRow, 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                Application.CutCopyMode = False
            End If

            Dim wrapCols As Variant, colLetter As Variant
            wrapCols = Array("A", "G", "H", "K", "Q")

            For Each colLetter In wrapCols
                MasterSheet.Range(colLetter & "3:" & colLetter & MasterSheet.Rows.Count).WrapText = True
            Next colLetter

            On Error Resume Next
            SourceSheet.Protect Password:=Password, UserInterfaceOnly:=True, AllowFiltering:=True
            SourceSheet.EnableSelection = xlUnlockedCells
            On Error GoTo 0

CloseWorkbook:

            SourceWB.Close False
        End If

NextFile:

        Filename = Dir
    Loop

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.CutCopyMode = False

   MasterSheet.Protect Password:="desotobocc", UserInterfaceOnly:=True, AllowFiltering:=True

    MsgBox "Data consolidation complete!", vbInformation
End Sub
[\CODE]

 

[judgejustin]

Do the workbooks in question have any Workbook_Open event code? Particularly anything that uses Application.OnTime.

Yes, they do have Workbook Open events. All of them do.

 

[RoryA]

Unless you need the code in the other workbooks to run, you should disable events, process the workbooks, then re-enable events.

application.enableevents = false
' your code here
application.enableevents = true

 

[judgejustin]

Unless you need the code in the other workbooks to run, you should disable events, process the workbooks, then re-enable events.

application.enableevents = false
' your code here
application.enableevents = true

That worked perfect, thank you!