excel - 我的代码有效，但我想知道在哪里可以改进代码，您有什么建议？

Question

我的代码运行得很快。我决定使用 FSO，因为我将在执行此代码期间打开并读取几个大文件。我的代码已经运行得很快，但我想知道我能做些什么来改进它。

我已经关闭了屏幕更新，除了打开手动计算（不是本项目范围的一部分）我还能做什么？

Row = 2

On Error GoTo ErrHandler

'Paths
serverPath = "\\kingnet\public\reports\"

'Files
lcaseReport = "Slbs_a01.txt"
dcaseReport = "Dlbs_a01.txt"

Application.ScreenUpdating = False

With Sheet2

    Do While .Cells(Row, 3) <> ""

        If (Cells(Row, 7)) = Date Then
            filePath = serverPath
        ElseIf (Cells(Row, 7)) < Date Then
            filePath = serverPath & "archive\" & Format(.Cells(Row, 7), "mmddyyyy") & "\"
        ElseIf (Cells(Row, 7)) = "" Then
            GoTo Skip
        End If

        If .Cells(Row, 8) = "" Then
            Set fsoStream = FSO.OpenTextFile(filePath & lcaseReport, ForReading)

            Do While fsoStream.AtEndOfStream <> True
                strLine = fsoStream.ReadLine
                carton = Mid(strLine, 10, 16)

                'lcase First Label
                If carton = .Cells(Row, 3).Value = True Then
                    .Cells(Row, 8).Value = Mid(strLine, 31, 5)
                    .Cells(Row, 11).Value = Mid(strLine, 41, 5)
                    .Cells(Row, 13).Value = "lcase"
                End If

                'lcase Last Label
                If carton = .Cells(Row, 4).Value = True Then
                    .Cells(Row, 8).Value = Mid(strLine, 31, 5)
                    .Cells(Row, 12).Value = Mid(strLine, 41, 5)
                    .Cells(Row, 14).Value = .Cells(Row, 12).Value - .Cells(Row, 11).Value
                End If
            Loop

            fsoStream.Close
            Set FSO = Nothing
        End If

Skip:
        Row = Row + 1
    Loop

End With

'dcase
Row = 2

With Sheet2

    Do While .Cells(Row, 3) <> ""

        If (Cells(Row, 7)) = Date Then

            filePath = serverPath

        ElseIf (Cells(Row, 7)) < Date Then

            filePath = serverPath & "archive\" & Format(.Cells(Row, 7), "mmddyyyy") & "\"

        Else
            GoTo Skip2
        End If

        If .Cells(Row, 13) = "" Then
            Set fsoStream = FSO.OpenTextFile(filePath & dcaseReport, ForReading)
            Do While fsoStream.AtEndOfStream <> True
                strLine = fsoStream.ReadLine
                carton = Mid(strLine, 10, 16)

                'dcase First Label
                If carton = .Cells(Row, 3).Value = True Then
                    .Cells(Row, 8).Value = Mid(strLine, 31, 5)
                    .Cells(Row, 11).Value = Mid(strLine, 41, 5)
                    .Cells(Row, 13).Value = "dcase"
                End If

                'dcase Last Label
                If carton = .Cells(Row, 3).Value = True Then
                    .Cells(Row, 8).Value = Mid(strLine, 31, 5)
                    .Cells(Row, 12).Value = Mid(strLine, 41, 5)
                    .Cells(Row, 14).Value = .Cells(Row, 12).Value - .Cells(Row, 11).Value
                End If
            Loop

            fsoStream.Close
            Set FSO = Nothing
        End If

Skip2:
        Row = Row + 1
    Loop

End With

Application.ScreenUpdating = True

Exit Sub

ErrHandler:
Select Case Err.Number
    Case 76
    MsgBox "Archive folder for date " & (Cells(Row, 7)) & " on row number " & Row & " missing. Contact Server Ops. To skip this line click ignore.", vbAbortRetryIgnore + vbDefaultButton3

    Resume Skip

    Case 53

    MsgBox "File missing from " & filePath & ". Files may be archived, run again after midnight or contact Server Ops.", vbAbortRetryIgnore + vbDefaultButton3

    Resume Skip
End Select

标签： excelvba