Skip to main content
added 39 characters in body
Source Link
AcsErno
  • 253
  • 3
  • 7

I use the following sub to speed up processing:

Public Sub AppSpeed(Optional iWhat As Long = xlDown)

    Dim bDir As Boolean

    bDir = True
    If iWhat = xlUp Then bDir = False

    On Error Resume Next

    With Application
         .Calculation = IIf(bDir, xlCalculationAutomatic, xlCalculationManual)
         .ScreenUpdating = bDir
         .DisplayStatusBar = bDir
         .EnableEvents = bDir
         .DisplayPageBreaks = bDir
         .PrintCommunication = bDir
   End With
End Sub

and I call it like

AppSpeed xlUp ' at the beginning of the app

and

AppSpeed xlDown  ' az the end of processing

The inconvenience of this solution is that you can't monitor what's happening so Dim a counter like filecounter and put the following snippet somewhere in the loop e.g. after wb.SaveAs to see that something is happening

    filecounter = filecounter + 1
    If 100 * (filecounter \ 100) = filecounter Then
        AppSpeed xlDown
        Application.StatusBar = folderpath & " " & CStr(filecounter)
        DoEvents
        AppSpeed xlUp
    End If

Next advice is to reduce the number of files in one folder. The documenations do not really help in this matter. My experience is that it is worth to keep the number of files under 1000 in one folder because access time increases enormously over that. You have a flexible structure so can quickly test it by dividing the files to multiple folders.

I use the following sub to speed up processing:

Public Sub AppSpeed(Optional iWhat As Long = xlDown)

    Dim bDir As Boolean

    bDir = True
    If iWhat = xlUp Then bDir = False

    On Error Resume Next

    With Application
         .Calculation = IIf(bDir, xlCalculationAutomatic, xlCalculationManual)
         .ScreenUpdating = bDir
         .DisplayStatusBar = bDir
         .EnableEvents = bDir
         .DisplayPageBreaks = bDir
         .PrintCommunication = bDir
   End With
End Sub

and I call it like

AppSpeed xlUp ' at the beginning of the app

and

AppSpeed xlDown  ' az the end of processing

The inconvenience of this solution is that you can't monitor what's happening so Dim a counter like filecounter and put the following snippet somewhere in the loop e.g. after wb.SaveAs to see that something is happening

    If 100 * (filecounter \ 100) = filecounter Then
        AppSpeed xlDown
        Application.StatusBar = folderpath & " " & CStr(filecounter)
        DoEvents
        AppSpeed xlUp
    End If

Next advice is to reduce the number of files in one folder. The documenations do not really help in this matter. My experience is that it is worth to keep the number of files under 1000 in one folder because access time increases enormously over that. You have a flexible structure so can quickly test it by dividing the files to multiple folders.

I use the following sub to speed up processing:

Public Sub AppSpeed(Optional iWhat As Long = xlDown)

    Dim bDir As Boolean

    bDir = True
    If iWhat = xlUp Then bDir = False

    On Error Resume Next

    With Application
         .Calculation = IIf(bDir, xlCalculationAutomatic, xlCalculationManual)
         .ScreenUpdating = bDir
         .DisplayStatusBar = bDir
         .EnableEvents = bDir
         .DisplayPageBreaks = bDir
         .PrintCommunication = bDir
   End With
End Sub

and I call it like

AppSpeed xlUp ' at the beginning of the app

and

AppSpeed xlDown  ' az the end of processing

The inconvenience of this solution is that you can't monitor what's happening so Dim a counter like filecounter and put the following snippet somewhere in the loop e.g. after wb.SaveAs to see that something is happening

    filecounter = filecounter + 1
    If 100 * (filecounter \ 100) = filecounter Then
        AppSpeed xlDown
        Application.StatusBar = folderpath & " " & CStr(filecounter)
        DoEvents
        AppSpeed xlUp
    End If

Next advice is to reduce the number of files in one folder. The documenations do not really help in this matter. My experience is that it is worth to keep the number of files under 1000 in one folder because access time increases enormously over that. You have a flexible structure so can quickly test it by dividing the files to multiple folders.

Source Link
AcsErno
  • 253
  • 3
  • 7

I use the following sub to speed up processing:

Public Sub AppSpeed(Optional iWhat As Long = xlDown)

    Dim bDir As Boolean

    bDir = True
    If iWhat = xlUp Then bDir = False

    On Error Resume Next

    With Application
         .Calculation = IIf(bDir, xlCalculationAutomatic, xlCalculationManual)
         .ScreenUpdating = bDir
         .DisplayStatusBar = bDir
         .EnableEvents = bDir
         .DisplayPageBreaks = bDir
         .PrintCommunication = bDir
   End With
End Sub

and I call it like

AppSpeed xlUp ' at the beginning of the app

and

AppSpeed xlDown  ' az the end of processing

The inconvenience of this solution is that you can't monitor what's happening so Dim a counter like filecounter and put the following snippet somewhere in the loop e.g. after wb.SaveAs to see that something is happening

    If 100 * (filecounter \ 100) = filecounter Then
        AppSpeed xlDown
        Application.StatusBar = folderpath & " " & CStr(filecounter)
        DoEvents
        AppSpeed xlUp
    End If

Next advice is to reduce the number of files in one folder. The documenations do not really help in this matter. My experience is that it is worth to keep the number of files under 1000 in one folder because access time increases enormously over that. You have a flexible structure so can quickly test it by dividing the files to multiple folders.