Slow performance when utilizing Interop.DSOFile to search files by Custom Document Property

Question

I am new to the world of VB.NET and have been tasked to put together a little program to search a directory of about 2000 Excel spreadsheets and put together a list to display based on the value of a Custom Document Property within that spreadsheet file. Given that I am far from a computer programmer by education or trade, this has been an adventure.

I've gotten it to work, the results are fine. The problem is, it takes well over a minute to run. It is being run over a LAN connection. When I run it locally (using a 'test' directory of about 300 files) it executes in about 4 seconds.

I'm not sure even what to expect as a reasonable execution speed, so I thought I would ask here.

The code is below, if anyone thinks changes there might be of use in speeding things up.

Thank you in advance!

Private Sub listByPt()
    Dim di As New IO.DirectoryInfo(dir_loc)
    Dim aryFiles As IO.FileInfo() = di.GetFiles("*" & ext_to_check)
    Dim fi As IO.FileInfo
    Dim dso As DSOFile.OleDocumentProperties
    Dim sfilename As String
    Dim sheetInfo As Object
    Dim sfileCount As String
    Dim ifilesDone As Integer
    Dim errorList As New ArrayList()
    Dim ErrorFile As Object
    Dim ErrorMessage As String

    'Initialize progress bar values
    ifilesDone = 0
    sfileCount = di.GetFiles("*" & ext_to_check).Length
    Me.lblHighProgress.Text = sfileCount
    Me.lblLowProgress.Text = 0
    With Me.progressMain
        .Maximum = di.GetFiles("*" & ext_to_check).Length
        .Minimum = 0
        .Value = 0
    End With


    'Loop through all files in the search directory
    For Each fi In aryFiles
        dso = New DSOFile.OleDocumentProperties
        sfilename = fi.FullName
        Try
            dso.Open(sfilename, True)
            'grab the PT Initials off of the logsheet
        Catch excep As Runtime.InteropServices.COMException
            errorList.Add(sfilename)
        End Try
        Try
            sheetInfo = dso.CustomProperties("PTNameChecker").Value
        Catch ex As Runtime.InteropServices.COMException
            sheetInfo = "NONE"
        End Try
        'Check to see if the initials on the log sheet
        'match those we are searching for
        If sheetInfo = lstInitials.SelectedValue Then
            Dim logsheet As New LogSheet
            logsheet.PTInitials = sheetInfo
            logsheet.FileName = sfilename
            PTFiles.Add(logsheet)
        End If
        'update progress bar
        Me.progressMain.Increment(1)
        ifilesDone = ifilesDone + 1
        lblLowProgress.Text = ifilesDone
        dso.Close()
    Next
    lstResults.Items.Clear()
    'loop through results in the PTFiles list
    'add results to the listbox, removing the path info
    For Each showsheet As LogSheet In PTFiles
        lstResults.Items.Add(Path.GetFileNameWithoutExtension(showsheet.FileName))
    Next
    'build error message to display to user
    ErrorMessage = ""
    For Each ErrorFile In errorList
        ErrorMessage += ErrorFile & vbCrLf
    Next
    MsgBox("The following Log Sheets were unable to be checked" _
           & vbCrLf & ErrorMessage)

    PTFiles.Clear() 'empty PTFiles for next use
End Sub

Answer 1

Accessing files over the network will be much much slower than locally. This is a simple fact.

Your code appears to be fine as far as I can see - you are simply seeing the effects of network access.