VBA code to merge workbooks quits unexpectedly

[Trebor8484]

Hi all,

I have the code below that loops through workbooks in a folder and copies data from a specific sheet in each file into a master workbook.

For some reason Excel will randomly close down while the Macro is running and leave me with a workbook in a recovered state so I have no idea at what point the the code stopped working. If I step through line by line it runs fine but that isnt feasible given the number of workbooks I need to merge each week.

Does anyone have any suggestions please, or possibly some alternate code I can try that will do the same thing?

Sub MultiFileImport()


    Dim fso As Scripting.FileSystemObject
    Dim MyFile As Scripting.File
    Dim MyFolder As Scripting.Folder
    Dim MyPath As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wsht As Worksheet
    Dim DstSht As Worksheet
    Dim DestFolder As String
    Dim lr As Long
    Dim Fileout As Object
    Dim MyTxtFile As Variant
    
    MyPath = "C:\Payment Queries\"
    DestFolder = "C:\Payment Queries\Archive\"
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
        .AskToUpdateLinks = False
        .CutCopyMode = False
    End With


    Set fso = New Scripting.FileSystemObject
    Set Fileout = fso.CreateTextFile("C\Temp\Files archived for payment.txt", True, True)
    Set MyFolder = fso.GetFolder(MyPath)
    Set DstSht = ThisWorkbook.Sheets("Master")


    DstSht.Rows("6:" & Rows.Count).ClearContents


    For Each MyFile In MyFolder.Files
        If InStr(MyFile.Name, "$") = 0 And MyFile.Name <> "debug.log" Then
            Set wb = Workbooks.Open(MyFile)
            For Each wsht In wb.Sheets
                If wsht.Name = "Query Log" Then
                    Set ws = wsht
                    ws.Cells.EntireColumn.Hidden = False
                    ws.Cells.EntireRow.Hidden = False
                    ws.AutoFilterMode = False
                    lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
                    ws.Range("A6:AJ" & lr).Copy
                    DstSht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormats
                    DstSht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                    DstSht.Columns.AutoFit
                End If
            Next wsht
            Application.CutCopyMode = False
            wb.Close False
        End If
    Next MyFile
	
    DstSht.Columns("A:AH").ColumnWidth = 35
    DstSht.Columns("AI:AI").ColumnWidth = 53
    DstSht.Columns("AJ:AJ").ColumnWidth = 35
    
    For Each MyFile In MyFolder.Files
        If InStr(MyFile.Name, "$") = 0 And MyFile.Name <> "debug.log" Then
            Fileout.Write MyFile & " moved from: " & MyFolder & " moved to: " & DestFolder
            fso.MoveFile Source:=MyFile, Destination:=DestFolder
        End If
    Next MyFile
    
    Fileout.Close


    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
        .AskToUpdateLinks = True
        .CutCopyMode = False
    End With
    
    MyTxtFile = Shell("notepad.exe ""C\Temp\Files archived for payment.txt""", vbNormalFocus)
    
End Sub

 

[NdNoviceHlp]

Random crashes... could be the clipboard. Maybe trial moving the Application.CutCopyMode = False to after the paste inside the wsht loop. You could also add some API code to clear the clipboard something like this...
Module code (32 bit installation)…

Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long
Public Declare Function CloseClipboard Lib "user32" () As Long

To operate (insert after the Application.CutCopyMode = False)...

OpenClipboard (0&)
EmptyClipboard
CloseClipboard

Another potential source of random crash may be the auto wb save thing. Trial turning it off or adding code to save ThisWorkbook after processing each MyFile. HTH. Dave

 

[Trebor8484]

Thanks for the suggestion but it didn't work unfortunately, the code to clear the clipboard efficiently is really useful though.

What I ended up doing was adding an application.wait for a few seconds just after the wb.close before it fetched the next file. When I was watching the code perform, it seemed to be running too quickly (if that's even possible?) but slowing it down a little seems to have done the trick now.

 

[NdNoviceHlp]

You are Welcome. Thanks for posting your outcome. Dave